You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

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

Rounding problem in Apple Numbers with multiplying by units in referenced cells

I've designed a self-populating invoice that references timesheets in other tabs (see screenshot below). The hours, calculated to 2 decimal total from each of 4 timesheet tabs, each are referenced to populate a cell in the Hours/Units column in the invoice tab. That gets multiplied by a unit price in the next column in the invoice.


The problem: This then calculates to an amount in the Cost column, which inexplicably rounds to the nearest dollar, but I want it to be accurate to the cent.


I've set the formatting to 2 decimal places but the result always rounds to the nearest dollar.


If I manually input the same Hours/Units amount the result will calculate to 2 decimal points rather than the nearest dollar, even though I haven't changed anything in the Cost column.


Weirdly, the next column, which calculates 13% tax, doesn't do this.


If I create a whole new column to try the same cost calculation it still rounds it even in the brand-new TEST Column.


How can I make this automatically calculate to the nearest cent?

MacBook Pro

Posted on Dec 3, 2022 6:38 PM

Reply
Question marked as Top-ranking reply

Posted on Dec 3, 2022 7:29 PM

I think what you will discover is the hours/units column has more digits than you are displaying.


1.283333... hours = 77 minutes (as best as a computer can do anyway)

1.283333*120 = 154.00


If you want the 1.28 to be exactly 1.28, you can use =ROUND(yourformula, 2) in that column. Or you could use minutes as the units for that column, which will be whole numbers unless your data includes seconds. Or you could increase the number of decimals displayed in the column and the results in the cost column will be closer to what you expect, though probably not always to the penny.

Similar questions

2 replies
Question marked as Top-ranking reply

Dec 3, 2022 7:29 PM in response to scottmoonman

I think what you will discover is the hours/units column has more digits than you are displaying.


1.283333... hours = 77 minutes (as best as a computer can do anyway)

1.283333*120 = 154.00


If you want the 1.28 to be exactly 1.28, you can use =ROUND(yourformula, 2) in that column. Or you could use minutes as the units for that column, which will be whole numbers unless your data includes seconds. Or you could increase the number of decimals displayed in the column and the results in the cost column will be closer to what you expect, though probably not always to the penny.

Rounding problem in Apple Numbers with multiplying by units in referenced cells

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.