Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

Question: Rounding of 0.005

Using Numbers 3.6.2


Rounding of =ROUND(0.005,2) returning 0.01 where

=ROUND(1.005,2) returns 1 and not the expected 1.01


Why ?


User uploaded file

User uploaded file

Posted on

Reply
Question marked as Solved
Answer:
Answer:

Hi c'


I don't know that it would be considered a bug or if it is a situation covered in the ISO standards for floating point binary numbers.


If your base data is precise (and accurate) to three decimal places, you can force all instances of n.xx5 to round up by adding a tiny 'fudge factor' to the raw data, as I've done below. This is the same table as the one above, with a small change in the formula in columns B, D, F and H. The new formula, as it appears in B2, is shown below the table.

User uploaded file


B2: ROUND(A2+0.0000001,2)


Note that the negative values (in A2 - A8) are also rounded up. This may or may not be significant in your data set.


Regards,

Barry

Posted on

Question marked as Helpful

Mar 6, 2018 6:25 AM in response to cmalabar In response to cmalabar

Hi c'


On first seeing this question, I thought it likely that this was an extension, or an artifact of "Banker's rounding," which alternates the role of '5' between 'round up indicator' and 'round down'.


I was able to replicate the behaviour, using Numbers 3.6.2 in OS 10.11.6, but also to find other values that exhibited similar 'round down' behaviour.

User uploaded file

Doesn't look systematic enough to be intended.

Might be related to the oddities of results entered in decimal (base ten), converted to binary (base 2) for calculation, then back to decimal for display.


Regards,

Barry


PS: the only entered values in this table are the 0.005 and 0.015 in A6 and A7. All the rest in that column were created by selecting those two (then in A2 and A3) and filling the series down to the end of column A, and later by selecting the same pair and filling the series up to added rows above what became A6 and A7. All other values on the table were created using two formulas:

ROUND(A2,2) and the equivalent in B2, D2, F2 and H2

and

A2 + 1 and the equivalent in C2, E2 and G2

then Filling each formula down its respective column.

B

There’s more to the conversation

Read all replies

Mar 5, 2018 2:05 PM in response to cmalabar In response to cmalabar

I tried your example and cannot seem to replicate the issue:

User uploaded file

maybe you have some other formatting issue at play.



This seems to be suggesting the same thing SG is proposing. Make sure the cell formatter is not limiting the number of digits.


select the cells then open the cell formatter, then set the number of places to 2 (or more)

Mar 5, 2018 2:05 PM

Reply Helpful
Question marked as Helpful

Mar 6, 2018 6:25 AM in response to cmalabar In response to cmalabar

Hi c'


On first seeing this question, I thought it likely that this was an extension, or an artifact of "Banker's rounding," which alternates the role of '5' between 'round up indicator' and 'round down'.


I was able to replicate the behaviour, using Numbers 3.6.2 in OS 10.11.6, but also to find other values that exhibited similar 'round down' behaviour.

User uploaded file

Doesn't look systematic enough to be intended.

Might be related to the oddities of results entered in decimal (base ten), converted to binary (base 2) for calculation, then back to decimal for display.


Regards,

Barry


PS: the only entered values in this table are the 0.005 and 0.015 in A6 and A7. All the rest in that column were created by selecting those two (then in A2 and A3) and filling the series down to the end of column A, and later by selecting the same pair and filling the series up to added rows above what became A6 and A7. All other values on the table were created using two formulas:

ROUND(A2,2) and the equivalent in B2, D2, F2 and H2

and

A2 + 1 and the equivalent in C2, E2 and G2

then Filling each formula down its respective column.

B

Mar 6, 2018 6:25 AM

Reply Helpful (1)

Mar 5, 2018 7:18 PM in response to Barry In response to Barry

You nailed it, that is exactly the behavior that my installation show. I'm not competent enough to know why it does what it does but only that those value are problematic for my intended use and not what we expected at all.


I did a test and get the the same results on numbers 2.3 (554).


Am I right that this should be filed as a bug ?

I don't have more recent version to test if this is still problematic.

Mar 5, 2018 7:18 PM

Reply Helpful
Question marked as Solved

Mar 6, 2018 6:25 AM in response to cmalabar In response to cmalabar

Hi c'


I don't know that it would be considered a bug or if it is a situation covered in the ISO standards for floating point binary numbers.


If your base data is precise (and accurate) to three decimal places, you can force all instances of n.xx5 to round up by adding a tiny 'fudge factor' to the raw data, as I've done below. This is the same table as the one above, with a small change in the formula in columns B, D, F and H. The new formula, as it appears in B2, is shown below the table.

User uploaded file


B2: ROUND(A2+0.0000001,2)


Note that the negative values (in A2 - A8) are also rounded up. This may or may not be significant in your data set.


Regards,

Barry

Mar 6, 2018 6:25 AM

Reply Helpful (1)

Mar 6, 2018 1:21 PM in response to Badunit In response to Badunit

Hi Badunit,


I'm sure everything is fine with the cell and with the function... Barry above is getting the exact same result as me. That said, what version of numbers are you using, maybe that has changed in the latest iteration which I haven't tried yet.


I'm on El Capitan if that matter, calculation might be coming from the OS and not the app itself.

Mar 6, 2018 1:21 PM

Reply Helpful
User profile for user: cmalabar

Question: Rounding of 0.005