Numbers calculating incorrect?!

Question

Numbers calculating incorrect?!

Hello all.

I am attempting to use Numbers to fill out and file sales taxes on behalf of our business.

I have used Numbers before as basically just a template to hold numbers that I done the calculations by hand, but decided I would give a try at using formulas within numbers to do the calculations for me. The riff? The calculations are coming back incorrectly and I am scratching my head! We’re talking about literal pennies here.. but we all know Uncle Sam doesn’t play about pennies.

If I can’t get these formulas to work, I’ll just end up going back to doing the calculations by hand and plugging in the final figures into Numbers as a reference, but I’m just trying to simplify my life. I’ve attached two screenshots of incorrect calculations, can someone PLEASE point me in the right direction?

I am NOT using any rounding formulas, I am only using Sum(cell+cell); Sum(cell x cell); so on, so forth.

As you can see in the first image, Numbers is trying to tell me that 54.81 - 0.82 = 53.98; but that is incorrect. It should be 53.99.

Again, as seen here:

2,824.65 - (.944% Comp = 26.66) = 2,797.98

This is again false, it should be 2,797.99

This would possibly be acceptable for entities that want you to round your figures, but unacceptable for entities that want the exact number, which is most of them. Please help?

iPad Air

Posted on Jan 2, 2024 2:19 PM

Reply

Answer 1

Top-ranking reply

muguy

Level 10

92,054 points

Jan 2, 2024 2:33 PM in response to aboutoneminuteremaining

You're encountering rounding errors. You need to round your numbers.

5% of 1096.12 is not 54.81 but rather 54.806

1.5% of that result is .82209, not .82

So the net result is 53.98391, which rounded, is not 53.99

Either add Round(5%*number,2) at each point or round the result even if it doesn't align with what you think it should be.

Reply

Answer 2

Ralf-F

Level 4

2,741 points

Jan 2, 2024 2:38 PM in response to aboutoneminuteremaining

Sorry but Numbers is calculating correctly.

Unfortunately the human brain will make mistakes when we talk about money.

5% of 1,096.12 is 54.806 and not 54.81 as shown.

The normal display setting for currency is two decimals, but all calculations are done with all available decimals (not only the decimals that are displayed).

Therefore your results look incorrect.

To avoid this you would have to use the ROUND function.

Here one example.

3 people will split a bill of 100.00 = 100.00 / 3

Numbers will display 33.33 for each, but 33.33 * 3 is not 100.00

Is 33.33 or 33.333333333 the right number?

Ralf

Reply

Answer 3

Ralf-F

Level 4

2,741 points

Jan 2, 2024 3:24 PM in response to aboutoneminuteremaining

You don't need to use SUM

You can do your calculation within the ROUND function.

ROUND - Apple Support

I changed the display to 4 decimals, just to show that the result is rounded to 2 decimals.

You need the ROUND function is all cells that should be rounded!

Ralf

Reply

Answer 4

Ralf-F

Level 4

2,741 points

Jan 2, 2024 3:33 PM in response to Ralf-F

Please have a look at these different option

ROUND - Apple Support will round based on the value

ROUNDUP - Apple Support will always round up, if the result is 10.011 it would round up to 10.02

ROUNDDOWN - Apple Support will always round down, if the result is 10.018 it would round down to 10.01

MROUND - Apple Support can be used to round to the nearest quarter (or whatever you pick), if the result is 10.14 it would round to 10.25

Reply

Answer 5

Jan 2, 2024 3:11 PM in response to aboutoneminuteremaining

“Sorry but numbers is calculating correctly”

I don’t why but that made me laugh. I know, quite the ego on my part to think I’m outsmarting a spreadsheet program. Haha.

Appreciate the speedy response fellas, and what you all are saying makes perfect sense: Numbers is calculating with phantom decimals not being shown. But that leads me to the next conundrum:

How to add the ROUND feature to the formula to coexist with the SUM formula that I am using? Because when I try adding round to the formula, Numbers is trying to make it ADD a rounded number to the sum of B4 x 5%, making the calculation go from off by a penny or so to completely wrong.

I’m sure I’m doing something simple wrong. Perhaps I need to add the round feature to each cell? Still need to make it coexist with the existing formula.

Reply

Answer 6

Jan 2, 2024 9:50 PM in response to Ralf-F

You are the best!!!!!!

This method works flawlessly, thus far. Using this formula with another entity, you can see that the numbers are spot on...

Screen cap from official filing calculator:

It’s spot on. I only needed to put the formula on “tax due”(AKA B4) for everything to fall into place.

For more complex filings (ie: filings where single entities have multiple jurisdictions with different tax rates and/or are compounded), I am going to put an adjusted ROUND formula on all cells and hopefully it produces the same (desired) end result.

I extend my sincerest gratitude for a fast, efficient, and simple solution to my problem.

Reply

Answer 7

Ralf-F

Level 4

2,741 points

Jan 3, 2024 12:31 AM in response to aboutoneminuteremaining

Glad that I could help.

Reply

Numbers calculating incorrect?!

Similar questions