Formula for Sum not rounded

[lzweifel]

Hi,

This is very basic, but has me stumped.... I have a varying number of rows each with calculate hours * dollars. This results in a figure, dollars and cents. At the end of all the rows I have my total dollars. This formula is just a simple sum of all the rows.

What's happening is the SUBTOTAL =SUM(J26:J50) is not calculating properly, always out by a few cents. Here is a complete example of one row calculations as well.

Here is my row calculation: =IF(C26="","",(G26*I26))

It must be that the sum is rounding in some way and the rows are not, or the rows are rounding and the sum is not. The ROWS ARE CORRECT, I do not want to change them. If I calculate with a calculator with 2 decimal points it is correct.

Gosh, this was hard to explain, hope I made some sense of it.

L

 

[Eric W]

Try this:

=IF(C26="","",ROUND(G26*I26,2))

If you have your format set to 2 digits, the actual amount of each cell could be off by a fraction of a cent, and you'd never see it. This will round to exactly 2 decimal places, so the SUM at the end should add up right.

 

[lzweifel]

Yes, except.... The rows are correct, it is the sum that is off.

The reason I say this is that I take these same calculations and input them into another database and each row calculates identically, but the sum of the database is different than the sum of my excel sheet. I mean, I could try it, but I don' want the rows to calculate differently than they currently are.

Let me try

 

[Tetra201]

Try using =SUMPRODUCT(ROUND(0&J26:J50,2)) instead of =SUM(J26:J50).

 

[lzweifel]

Hi, and thank you for helping, I truly appreciate your expertise!

Both methods work perfectly for the SUBTOTAL.... but my tax amount is still not correct as currently I have it calculating 5% of the subtotal, is there a way to set a formula that calculates each line individually and then totals. It is not something I want to put on each line item, only as one figure.

 

[KRice]

If you want to multiply each item in J26:J50 by some tax rate and then sum those 25 individual tax amounts using a single formula, try:
=SUMPRODUCT(J26:J50*0.05)
where 0.05 is the tax rate.

 

[jasonb75]

Yes, except.... The rows are correct, it is the sum that is off.

The rows 'appear' correct but that doesn't necessarily mean that they are.

In a simple example if you have the following
1.234
1.234
1.234
In each row you will see 1.23, so the sum should be 3.69 but because excel calculates the actual value, not the visible value the result will be 3.702, which will be shown as 3.70 when formatted to 2 decimal places.

With a higher number of rows, the effect will be multiplied which will explain your variance. By using the formula that Eric w has suggested in your rows, you can 'fix' the hidden decimals without changing the result in the row, but ultimately it will correct the sum at the end.

 

[Sulprobil]

In accounting you need to take the rounded total.
If you have 100 cells, each showing 9 cent, you have a total of $9.00. 5% tax are 45 cent. But if you take 5% tax of each of the 100 single cells and round those to the cent you get 0 cent each time and therefore a total of $0.00.
Which means: 45 times you should round a cell to the "wrong" side.
There are VBA solutions to do this for you, minimizing the absolute error. Is it ok to post a link?