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!

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

"duketter" wrote:

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!

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

"duketter" wrote:

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!

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.


"duketter" wrote:

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!

"duketter" wrote:
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 -----

"duketter" wrote:

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!

"duketter" wrote:
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 -----

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

"duketter" wrote:

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!