Round Function

D

duketter

I am using Excel 2007 and need some helping on a rounding formula. I would
like my cell to either round to the nearest whole num or ".5" of the number
but it needs to round down. For example:

5.1 would round to 5.0
5.4 would round to 5.0
5.5 would round to 5.5
5.7 would round to 5.5
5.9 would round to 5.5
6.0 would round to 6.0

I am using the Mround function but that rounds up so 5.8 and 5.9 rounds to
6.0 but I need them both to round to 5.5.

Any thoughts? Thanks!
 
A

Allen

This may not be the prettiest way but it works.
=IF(A1-INT(A1)>=0.5,INT(A1)+0.5,INT(A1))
 
M

Mike H

Hi.

Try this

=FLOOR(A1,0.5)

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
J

Joe User

duketter said:
I would like my cell to either round to the nearest
whole num or ".5" of the number but it needs to
round down.

Ostensibly, you want to round down to the nearest 0.5. Try:

=ROUNDDOWN(A1*2,0)/2

Caveat: "what you see is NOT what you get" always. For example, if A1 is
formatted as Number with 1 decimal place, and the value is 5.95, you will see
6.0, but the ROUNDDOWN expression above will result in 5.5. If that is
unacceptable, then perhaps you want:

=ROUNDDOWN(ROUND(A1,1)*2,0)/2


----- original message -----
 
J

Joe User

duketter said:
I think I may have got it:
=ROUNDDOWN(A1/0.5,0)*0.5

Yes, that will work. But it is generally better to work with integers
whenever possible, e.g. ROUNDDOWN(A1*2,0)/2 as I wrote. (Our postings
crossed on the wire.)

In this case, it does not matter. But generally, decimal fractions are not
represented exactly internally. For example, suppose you wanted to round
down to the nearest 1/3. ROUNDDOWN(A1*3,0)/3 would be more reliable than
ROUNDDOWN(A1/0.3333,0)*0.3333.

Of course, for that example, you could minimize the error if you were
willing to write 0.333333333333333. That is only about 3.33067E-16 off ;-).


----- original message -----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top