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

Question:

Question:Q: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 ?

Posted on

Question marked as Solved

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.

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

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.

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

You need to increase the number of decimal place, with a 2 here:

SG

Mar 5, 2018 2:02 PM

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

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

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.

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

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

Question marked as Solved

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.

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

Nice, I think that this should do it since after rounding the "fudge" won't affect any other value and we are now getting the expected value from the formula.

Mar 6, 2018 6:28 AM

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

I somehow did not see the earlier posts. Mine was just a repeat of what others already said so I deleted it. I am on High Sierra 10.13.3 and Numbers 4.3.1.

Mar 6, 2018 1:23 PM

User profile for user: cmalabar

Question: Rounding of 0.005