stop rounding up in a colum that contains decimals in excel

B

bina

the answers in 6 cells need to be *0.387 these round up or down (this is ok)
but when i need to add these up 6 cells up its still taking the 3rd decimal
place in to account eg 15*0.387= £5.81, 10*0.387=£3.87. 30*0.387=£11.61 these
3 are duplicated once more, when i add them up the answer is coming out as
£42.57 and shoud be £42.58 plase can you help me
 
N

Niek Otten

The value displayed is not the actual value; if the "rounding" occurs
through formatting, the unrounded value is used in computations.
Use the ROUND() function to really round, or use Tools>Options>Calculation
and check "Precision as displayed", but read HELP to find out what happens.
 
B

Barb Reinhardt

I had to read between a lot of line for this one. If I take
15*.387 + 10*.387 + 30* .387, I got 21.285. This number, multiplied by 2,
is 42.57

I'm not sure where 42.58 is coming from.
 
B

Bob Phillips

Your initial formula is not rounding but just showing to 2 decimal places I
would guess, so the result of

15*.0387 is showing as 5.81 but is actually 5.805.

When you sum the results, it adds the actual values, not the displayed
values.

Either round initially

=ROUND(15*0.387,2)

or change the sum to round

=SUM(ROUND((B1:B6)*(0.387),2))

where B1:B16 are the multipliers. This is an array formula, it should be
committed with Ctrl-Shift-Enter, not just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Top