Calculating formatted values

Posted by Matthew Schmitt on April 16, 2001 8:25 AM

Can excel calculate a range of formatted values? I have a column where the actual number goes out to 7 or 8 decimals. When I format the column to only show two decimals, my autosum still calculates the actual value of the cell. How do I get it to total the formatted values?

Posted by Dax on April 16, 2001 8:43 AM

I would suggest using this formula:-

=SUM(ROUND(D4:D5,2))

HOWEVER, you must enter this formula using Control+Shift+Enter rather than just enter.

Regards,
Dax.

Posted by Stephane Parent on April 16, 2001 8:49 AM

Hi Matthew,

If your data are in column A, you can use something like =round(a1,2) in b1 to round your values to the second decimal. Then you can sum or average those rounded values. Else, you can use arrays formula in your sum ( something like this: =sum(round(a1:a10),2) be sure to enter that formula and press Shift+CTRL while you press enter. If you did right, you should see {=sum(round(a1:a10),2)}

I hope it helps.

Stephane Parent

Posted by lenze on April 16, 2001 9:09 AM

You can force Excel to only use displayed values in calculations on the the calculation tab of the options dialog panel (Tools Menu)