Round Excel values up to nearest $5.00

U

USAOz

What formual would I enter to round an Excel value up to the next whole
dollar amount in $5.00 increments? For example, any amount between $30.01
and $34.99 would be rounded and displayed as $35.00.
 
D

Dave Peterson

One way:

=CEILING(A1,5)
or
=ROUNDUP(A1/5,0)*5

What formual would I enter to round an Excel value up to the next whole
dollar amount in $5.00 increments? For example, any amount between $30.01
and $34.99 would be rounded and displayed as $35.00.
Click to expand...
 
D

Dave Pettit

You can use the INTfunction to round up or down as follows:

=INT((A1+2.5)/5)*5

The 2.5 is half of the multiple to which you are rounding, and the 5s are
what you are rounding to.

--Dave Pettit
Flint MI
 
U

USAOz

Hi!

Thank you VERY much for your correct and speedy response! Also, thanks for
the tip on that useful website link - very interesting!
 
U

USAOz

Hi Dave!

Thanks for that response - it is closer to what I was seeking than the other
2 (also correct) responses. BTW, thanks for your explanation - that was
something I never understood before and had I known that, probably would not
have needed to post the question in the first place! Thanks again!
 
B

Bob Phillips

Can you explain two things to me.

First, how does this solution meet you specified requirement of '...round an
Excel value up to the next whole
dollar amount in $5.00 increments? For example, any amount between $30.01
and $34.99 would be rounded and displayed as $35.00'. By my calculations,
this formula will return 30 for a value of 30.01 not the 35 you stated.

Secondly, in what way is it any different to Frank's, let alone closer?
 
M

Myrna Larson

Explain not only those two things, what is meant by this response being
"closer" than the 2 other "correct" responses.

There should be only one answer. If the other two are correct, this one gives
either an identical result or something different. It it's different, and the
other two are "correct", then this one is wrong, not "closer".

OTOH, if this one is "closer", the other two aren't correct, are they?
 
Top