Are the figures in Column F calculated?

One explanation that can often explain situations like this is that Excel is performing its calculations on the actual values in the cells, rather than the displayed values.
The displayed value may often be rounded, hiding extra decimal places.

You can go into Excel options..Advanced and turn on “Set Precision as Displayed” .

Looks like rounding error to me too. You could use ROUND to return the calculated values to 2 decimal places. This has the advantage of allowing you to follow tax office rules for calculating tax.

cheers, Paul

To get a handle on what is happening, set all the cells to display more precision (say 4 dec places). I am confident that you will find that Excel is not getting its arithmetic wrong.

The warning is because everything that is not shown is lost. For example if your actual data is 1.755, your display would be 1.76. Once you turn on the Precision as Displayed, the actual value becomes 1.76 – an increase of 0.005.

Make sure that setting only applies to one spreadsheet – the advantage of ROUND.

cheers, Paul

This is Excel 2002, SP3. I’ve been using this same spreadsheet for at least the past 10 years. Now, for the first time Excel is making subtraction errors. In the attached screenshot column H is the balance (H2 is selected to show you the formula). There are subtraction errors in rows 4, 6, and 8 (there are five more errors in a column of 49 rows, but you get the idea). What is going on?

The answer in H2 is perfectly correct as 243.86-5.39 = 238.47. As the formula asked for.

A couple of points.

The first is that you should refrain from using formulas such as c2=””. Instead start using the function ISBLANK, as in =if(isblank(c2), etc. Far more elegant and accurate.

Type all your functions in lower case. Always. Excel will convert them to upper case when it recognises the function. You will get an error message as in @NAME? if it does not recognise the function. The function you typed in will remain in lower case making it easy to identify the source of the problem. Usually a typo.

Finally, the calculations in your spreadsheet are correct. Remove the formatting from all your cells. You will notice that the numbers are calculated to 4 decimal points and are rounded to 2 as per your formatting. Hence the perceived error.

In general, Excel will calculate numbers up to 15 decimal points. Comparing numbers can therefore lead you to believe that there are errors where there is fact a discrepancy in rounding.

There is no solution to this that I know of. You have functions such as ROUNDUP and ROUNDDOWN that might help. I don’t think it is worth the trouble.

Good luck.

A few questions …..
1) Can you attach a copy of the worksheet?
2) What are the headings for cols F&G?
3) What is col H accumulating?
4) Will this formula work in col F
=IF(C2=””,0,ROUND(C2*0.07,2))

Its almost certainly rounding errors. When doing calculations on monetary values it is best to format all cells to a currency format. That will take care of all rounding issues using the standard rounding rules, ie those used in accounting practices.

The reason it just cropped up, is either the values you are putting in this time are just the right combo to trip you up, or it just got overlooked before and now there is more scrutiny, or a combination of both. In short a combination of good old human nature and software that is not quite intuitive enough yet.

So just format columns C, F, and H with a standard currency format and it will all work out for you.