Decimals and Rounding

[Sue K]

I can not get my column totals to appear correctly due to the decimal

Example
7.5
7.1
6.25 (Actual Value is 6.245
6.25 (Actual Value is 6.245

27.09 Total reflected on workshee

How do I get this rounding thing corrected so my sum shows as 27.10
I've got my cell formatting set a 2 decimals, is there some other thing I'm missing
Thanks in Advance

 

[Peo Sjoblom]

No, since you are formatting to 2 decimals you only change the display, you
can use round

=ROUND(A1,2)

or you can change a setting under tools>options>calculation and select
precision as displayed

 

[Mark Graesser]

Sue
You can do this two ways

1) If your values are coming from formulas, then put your formula inside the ROUND function

=ROUND(yourformula,2

2) Go to Tools>Options>Calculations and check "Precision as displayed"

In either case, if you use the value in that cell in any other formula you will be using the rounded number

Good Luck
Mark Graesse
(e-mail address removed)
----- Sue K wrote: ----

I can not get my column totals to appear correctly due to the decimal

Example
7.5
7.1
6.25 (Actual Value is 6.245
6.25 (Actual Value is 6.245

27.09 Total reflected on workshee

How do I get this rounding thing corrected so my sum shows as 27.10
I've got my cell formatting set a 2 decimals, is there some other thing I'm missing
Thanks in Advance

 

[Norman Harker]

Hi Sue!

Another way that rounds the numbers for the purposes of your sum only
without doing a permanent precision as displayed:

=SUM(ROUND(A1:A3,2))
Entered as an array by pressing and holding down Ctrl + Shift and then
pressing Enter.

Appears in formula bar as:

{=SUM(ROUND(A1:A3,2))}

You'll need to vary the rounding factor according to the format that
you've applied.

But use this approach with caution. I don't tend to like Precision as
Displayed because it attacks the whole workbook. Rounding each figure
can be a chore and you may want to use an individual "true" figure
somewhere else. The array SUM needs changing if you change your
formatting.

A final approach is to accept the apparent error and to annotate the
result. Usually, that's my preferred approach.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Bernard Liengme]

Hi Sue
Just to add to the other correct answers. If you want to leave the formulas
in the cell alone but still round the values for adding try
a) =SUM(ROUND(A16:A19,2)) must be entered as array function, or
b) =SUMPRODUCT(ROUND(A16:A19,2)) entered a normal function
You may need to format the cell to show 2 decimals
Best wishes