CHANGE CALCULATED VALUE TO ACTUAL VALUE 1

[Evil8]

One of my users has a very large Commissions worksheet in Excel 2003. The problem is that the values stored in cells, calculated in rows, are stored with more than 2 decimal places and then totaled by column. Physically looking at the values in the cells the values are automatically rounded to the nearest cent ($ formating) so when manually totalling the rows in columns some are a cent or two different than the calculated total.

I don't think it's that big of deal but I was asked if anyone knows of a way to change the row formulas so the cell holds the same rounded value as what the user sees when they look at it.

Any help would be great. Thank you.

 

[zelgar]

use the Round function in the formula
For example, change cell from ="formula" to =Round("formula",2)

 

[Evil8]

That was too easy...lol. Thanks very much zelgar!

 

[xlhelp]

It happens because of rounding. You can try this ---> type 10.006 in two cells; set formatting to two decimal places for both; add the two together, you will get 20.01 instead of 20.02.

You can have the the manual total the same as autocalc. Under Options you need to set calculation as displayed. [red]CAUTION: You may do this to check. Should not do it otherwise. All your calculations will be wrong.[/red]

Canadian eh! Check out the new social forum Tek-Tips in Canada.
Finally....Done with 9 to 5