|So if you know about the "displayed vs. actual value" issue, then to say "my excel isn't calculating correctly. i know it sounds odd but its true" isn't correct, is it?|
Excel is calculating 100% correctly, you just don't want it to.
There are 3 Excel functions you can use to force Excel to calculate based on the number of decimal places you want it to use.
This will use the standard rounding rules: 4 or below is ignored, 5 or above is raised to the next highest number.
=ROUND(13.325, 2) = 13.33
=ROUND(13.324, 2) = 13.32
This will round the number up regardless of the value of the digit that you are checking.
=ROUNDUP(13.325, 2) = 13.33
=ROUNDUP(13.324, 2) = 13.33
This will round the number down regardless of the value of the digit that you are checking.
=ROUNDDOWN(13.325, 2) = 13.32
=ROUNDDOWN(13.324, 2) = 13.32
If you SUM the results of these functions, you will get a value based on the results, not based on the original numbers.
=SUM(13.324, 13.324) = 26.648 which when displayed with 2 digits will show 26.65.
=SUM(ROUND(13.324, 2),ROUND(13.324, 2)) = 26.64 since the .4's were stripped off before the values were summed.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.