rounding

**Duckie** · 01-17-2010, 03:50 PM

i need help doing rounding
in A2 i have 4.4 i need it to be rounded to the nearest 0.5
in A4 i have 4.3 i need it to be rounded down to 4
in A7 i could have 4.8 which needs to go to 5
all the cells in column A will vary fron months to months
i have tried mround
roundup and rounddown doesn't work

**teylyn** · 01-17-2010, 03:57 PM

are you looking for one formula to do these three different roundings?
What should happen with 4.6? Round down to 4.5?

if so, try this:

=IF(AND(MOD(A2,1)>=0.4,MOD(A2,1)<=0.6),MROUND(A2,0.5),ROUND(A2,0))

**pike** · 01-17-2010, 04:09 PM

duckie
Round with 0.5 was no good
=ROUND(A1,0.5)

**pike** · 01-17-2010, 04:29 PM

or
you'd think donkeyote thought of this one
=IF(EXACT(MOD(G21,1),0.5),G21,ROUND(G21,0.5))

**teylyn** · 01-17-2010, 04:41 PM

pike, ROUND(a2,0.5) does not round to 4.5 on my machine. What are you getting?

**daddylonglegs** · 01-17-2010, 04:50 PM

Round expects an integer value for the second argument. If you use a non-integer value as that argument then I believe Excel just truncates it so if you use

=ROUND(A1,0.5)

or

=ROUND(A1,0.99)

you'll get the same results as

=ROUND(A1,0)

i.e. it'll round to the nearest integer