Numbers Rounding Too Much

Hi psychdoctor,

These numbers were typed in. Were your values derived from formulas?

Perhaps there has been some rounding. Set the data format to many more decimal places to see more precise displays.

One thing I suspect is that 1.833 is 1.833333333333333

Regards,

Ian.

HI pd,

Using Numbers '09, I get your expected resuts, except for the final one multiplying 17.499 by 12. I suspect a slip of the fingers on this one. 😉

All values in column B were entered directly from the keyboard.

All values in column C were calculated using the formulas below.

Cell C6 was formatted as Number, showing three decimal places. all others were left as Automatic.

Formulas used:

C6: =SUM(B2:B6)

C12: =SUM(B8:B12)

C14: =SUM(C6,C12)

C15: =12*C17

IF, as suspected by Ian, the numbers in the left column of your example are generated by formula, the easiest way to achieve the expected result may be to ensure that the displayed value for each cell is the actual value contained in that cell. That's easily done by rounding the result of the formula to three places (instead of just displaying it rounded to three places). Use whatever formula is in those cells now, but wrap that formula in the round function:

=ROUND(formula,3)

where formula is the formula currently in each cell.

Regards,

Barry

YB, the numbers are derived from formulas, simple addition. But it still does not make sense. If I take out my calculator and add what is displayed (3 decimal places), the 8.499 is the solution. If I round each of the numbers to two decimal places and add them with a calculator, it adds to 8.49, not 8.50. So if numbers is doing addition on the underlying numbers, out to 13 or 14 decimal places, it should not round until the end, and if it was it would display 8.499 or 8.49, not 8.500. It seems like Numbers us rounding it to one place, yet displaying 3 decimal places.

I've tried the formula with sum() and just adding and the same thing happens. I opened this on my IPad in Numbers and the same thing happens. When I add additional numbers, more rows, with random decimals it works correctly.

This may be a bug, and dependent on the numbers I am using. I enter the numbers in this spreadsheet every 2 weeks. The numbers represent time converted to decimals. I've tried creating a new spreadsheet, with no luck.

I won't be at my computer for a couple of hours, so I will just try typing in the numbers though I did that in Numbers on my iPad and the same error occurred. I will also try to duplicate what you and Barry wrote above. BTW, this occurs on two machines with two versions of Numbers (current, and Numbers '09).

Thanks

BTW, Ian and Barry, I'm psychdoctor, I signed in under the wrong Apple ID, I can't figure out how to delete the post.

The values in the cells are not rounded to three decimal places. They are displayed rounded to three decimal places because that is how you wanted to see them but the numbers in the cells may have more decimal places than three.

For example, 1.833 in the display could be 1.83255555 or 1.8334444 or some other number that, when rounded to 3 decimal places, would show as 1.833

So, using "1.833" as an example for a summation, you are thinking 1.833 + 1.833 should be 3.666. But what if the two numbers were not 1.833 but were really 1.8334? 1.8334 + 1.8334 = 3.6668 which, when displayed to 3 decimal places, would be show as 3.667.

If you want the numbers in the cells to be rounded to three decimal places for real, you need to use the ROUND function to do it.

Also, you cannot delete a post. If you get back to it soon enough to edit it, you can delete everything you wrote and replace it with a few dashes

Hi Jeff,

Somewhere in the FAQs on this topic is a suggestion to work with integers. I suggest whole minutes. Leave the calculation of minutes X hourly rate to the end.

All cell formats are Automatic to remove confusion with Date and Time, Duration or Currency formats.

To calculate total minutes for each session, D2 of the Week 1 table contains this formula (and copy down)

=B2×60+C2

D7 of the Week 1 table contains

=SUM(D)

Because it is a Footer Row, D7 is excluded from the formula (no self-referencing error).

Total table pulls the weeky total minutes from each weekly table, then sums them, in B8=Sum(B)

All integers so far. Now for the first floating point arithmetic in C8 of the Total table

=B8×12÷60

Total minutes times hourly rate divided by 60 (minutes to hours).

C8 is Automatic format with 2 decimal places (dollars and cents). Expanding C8 to 30 decimal places shows 201.200000000000000000000000000000

Even if there happens to be a floating point error in C8, you would still have to round to the nearest cent. 🙂

Regards,

Ian.

psychdoctor wrote:

Badunit, I do understand about how numbers are rounded in Numbers. My point is that the final answer in these eamples (the number of hours and the Cost) should be 17.49 or 17.490, but instead is 17.500. Even though some tables list the values as 17.50, I tried to display with up to 15 decimals and it comes out as 17.5000000000.

The answer is correct. Referring to the third screenshot in your latest post, take a look at the numbers you started with, you should be expecting the answer you are getting: 17.5 hours. Sum up the hours from the Hours column, you get 12 hours. Sum up the minutes in the Minutes column. You get 330 minutes which is 5.5 hours. Sum those and you get EXACTLY 17.5 hours.

For the third screenshot you said you entered each number to exactly four digits. I am sure you meant you entered the hours and minutes to exactly four digits. The "Decimal M" column I am positive is still determined by a formula (=Minutes/60) and is NOT rounded to four digits. If it was rounded, your answer would have been 17.4999000. That would not be the correct answer to your problem. Rounding in the middle of a procedure instead of at the end often introduces errors. But it does bother people when they look down a list of numbers and the total appears to be "off" by a penny or a fraction.

The values in the cells are NOT rounded unless you specifically used the ROUND function (or one of the other rounding functions). Values you type into cells will have the number of decimals you typed in, even if you display those cells with less decimal digits. Numbers determined by formulas will have however many decimal digits they have, which could be a lot, even if you display them with less digits. The "Auto" setting for precision does not mean it will automatically show all the digits. It is trying to determine what you want to see and it is assuming you want to see four digits. There are many more digits than that in the Total column.

Hi Jeff,

This was written yesterday, before your post this morning, but I was unble to post it at that time due to maintenance at the discussions site.

"The numbers represent time converted to decimals."

Here is an extended version of your example table that may help show what is going on. I've pasted the values, as the camera icon appears to be out for repairs.

Duration: this column shows the entered duration in minutes.

DUR2HOURS: This shows the result of =DUR2HOURS(A), which returns a decimal number, displayed with 15 place precision (but possibly calculated to a few more places), equivalent to the number of hours represented by the duration in column A. I have chosen to display this result with 15 places after the decimal (16 places in all) to ensure that the limit of display precision is reached, as evidenced by the final 0 in all the values.

NO MATTER HOW MANY DECIMAL PLACES THE USER CHOOSES TO DISPLAY, THE VALUES SHOWN ABOVE IN COLUMN B (or the even more precise values mentioned below) ARE WHAT WILL BE USED IN CALCULATIONS.

SUM(Bn:Bm): The two filled cells show the sums on the values in the five rows of column B ending on the same row as the result is shown. If the calculations were carried out to the same precision as the precision displayed (14), the expected result for the second sum in column C would be 8.499999999999990 (one digit before the decimal, 14 calculated digits following the decimal—the four and 13 nines—and a placeholder zero for the fifteenth decimal place demanded by the format choices in the Inspector.

The actual display (one digit before the decimal, a five and 14 zeroes following the decimal, leads me to believe that the actual values used are precise to at least 16 digits, and that the 16 digit result is automaticlly rounded to 15 digits for display at the maximum precision.

ROUND(B,3): This column shows the values calculated in column B rounded to three places after the decimal (and displayed with four places after the decimal to show the zero value ithe fourth place, and in all following places).

ROUND changes the actual value in the cell, not just the display of that value.

Although I did the duration to number of hours calculation and the rounding calculation separately, they can be combined into a single formula:

=ROUND(DUR2HOURS(A),3) will give the same results as shown in the table in column D.

SUM(Dn:Dm): This is the same formula as used in column C, but here the values being summed are the rounded ones in column D.

Shown below is the SAME table as above. The only differences are that all the numbers have been displayed with three places after the decimal, and the columns have been made narrower to fit the changed display. The actual values in the cells are the same as those in the table above.

Regards,

Barry

HI again, Jeff,

This is in response to your post this morning.

I plugged your numbers into a LibreOffice table with these results:

(Note: The change in A10, and resulting change in the total in F12 was part of a test described below)

The set at the top (rows 1 to 12) is shown as it appeared with no formatting set for the cells, and with the columns widened where necessary until the 'full' result was shown.

By default, LibreOffice appears to show 10 decimal places for numbers in this range. This gives some headroom, allowing LO to line up the decimal points for numbers up to 99999.xxxxxxxxxx without requiring the user to set a specific format for the cell containing those numbers. See cells A11 and A12, and the corresponding values in E11 and E12.

Note that although only 10 post decimal places (and only 11 places in all) are shown for most results, the actual results in all calculations will have been carried out to (at least) the maximum number of places that can be displayed (15), and probably to one or more places beyond that to provide correct rounding of the results (as in the examples F12 and F26).

"I then recreated the original table again, this time leaving out the Decimal Hour, which was worthless, and making certain all of the numbers were entered to 4 deimal places. Still "over-rounded" the final numbers. "

You can ensure that the "entered" numbers are entered to four decimal places.

You can ensure that the "calculated" numbers are displayed to four decimal places, but restricting the display to four decimal places makes NO CHANGE to the calculated value itself.

If you want to calculate the sum using the rounded values, then you must round each value before using it in the sum.

Here's one further example, with values chosen to make the expected result esier to determine.

Both examples in this set have the same times: Three sets of one hour and twenty minutes and one of one hour and no minutes.

Summed, the results should be four hours and sixty minutes, or five hours and no minutes.

Number formats in the upper group are set to general, with columns set wide enough to display LO's ten decimal place default.

The middle set of cells is formatted to display three decimal places, but use the same formulas as the upper set.

The bottom set uses ROUND(A41+B41/60,3) to round the calculated values in column E to three decimal places. Those rounded values are used in the sum in F39.

All three of these examples start with the same data. The first two tell us that the sum of three one hour and 20 minute sessions plus one one hour session is five hours. The third that the total time in those four sessions is 4.999 hours.

Your call on which is 'correct' for your case.

Regards,

Barry