Stop SUM Rounding???

Hello Everyone,

I have a sheet that has formulas that have been carried over from numerous other sheets. The end result is 20 cells in a column that contain (for example) $146.26 in each cell. The problem is the SUM=(these 20 cells) but instead of the total being $2,778.94 it is giving me $2,779.00. It is rounding the total for some reason?

How do I stop this rounding? If I put $146.26 in 20 cells next to the original cells and SUM() the new cells I get the correct total so why are my other cells being rounded???? The cell formatting is set correctly and nothing is set to round (FLOOR) anywhere??

Thank you in advance for any help

Can you attach a sample workbook?

Hi,
Unfortunately I am working on a ship and the work computers are so restricted that uploads are not possible! I tried to upload but it's blocked!
Reading your responses it appears that perhaps I need to take a closer look at the formats and maybe even the formulas. I have looked in the cell formatting and it's uniform but what can it be with the formulas? What should I be looking for that will cause a rounding?

Below is some of the formulas that lead to the final figures. The other ones are standard =SUM() formulas. I agree that this is something I have done as Excel is not wrong. I have tripped myself up numerous times over things like this!

=IF(B11=$B$298,"",AK150)
=IF(B11=$B$298,"",AI150)
=IF(AH150>AI150,AH150,AI150)
=IF($C$3=TODAY(),"$0.00",AG150*30)
=IF(SUM(D11:INDEX(D11:Y11,AF150))>AE150,AE150,SUM(D11:INDEX(D11:Y11,AF150)))

Thank you very much for your help I'm totally stuck!

Margate

Change the formatting of the 20 cells and the one with the formula in it to increase the number of decimals seen - this will probably explain what is happening - instead of $146.26 in a cell, you may have $146.263333, and all those extra bits when added up will contribute to a higher value than you expect. You will need to apply ROUND( ... ,2) to all your formulae to get a consistent result.

Hope this helps.

Pete

hello Pete_UK,

Thank you for your help. Although it did not ultimately solve my problem it did put me on the right track. In the end I used

Please Login or Register  to view this content.

You were right in that the decimal was something like 22.234234342534. I thought I had fixed this!
As the others said in their posts I did not!

Lesson learned!

Thank you very everyone much for your help.

Margate