Rounding to nearest 9

  • Thread starter Thread starter GaryS
  • Start date Start date
G

GaryS

I have the following =(E5+G5) * 1.25 which results in $1446.25. I would
like it to be rounded to $1449.00. How could I do this? Thanks!
 
I read the OP's subject which is misleading but if he want to roundup
1446.25 to 1449
then I would assume it would be

=CEILING(((E5+G5)*1.25,10)-1

or rather this to take care of zeros


--


Regards,


Peo Sjoblom
 
Forgot the last formula

=MAX(CEILING((E5+G5)*1.25,10)-1,0)



--


Regards,


Peo Sjoblom
 
Hi Guys,

Thanks. This one comes the closest, but it rounds up OR down.
=MAX(ROUND(((E5+G5) * 1.25)/10,0)*10-1,0)

I only want it to round up to the nearest 9. In other words if the results
are $3243.00 I want it rounded to $3249.00.

Thanks!
 
Peo,

This one rounds $3250 down to $3249. I need it to round $3250 to $3259.

Thanks so much for your help.
 
You didn't say what you wanted to happen for values above the "9 value" but
less than the "0 value"; for example, what do you want $3249.50 to "round"
to?

Depending on your answer to my question, this formula **may** do what you
want...

=10*ROUNDUP((A1+(MOD(A1,10)=0))/10,0)-1

Rick
 
It was because you stated in your subject line that you wanted nearest which
is what I gave you, but after looking at your example it was obvious you
wanted to round up

=MAX(CEILING((E5+G5)*1.25,10)-1,0)

will round up


--


Regards,


Peo Sjoblom
 
You didn't say what you wanted to happen for values above the "9 value"
but less than the "0 value"; for example, what do you want $3249.50 to
"round" to?

Depending on your answer to my question, this formula **may** do what you
want...

=10*ROUNDUP((A1+(MOD(A1,10)=0))/10,0)-1
Click to expand...

Of course you will want to replace my A1 references with (E5+G5)*1.25.

Rick
 
Hi Peo,

For some reason this solution still does not round up. I apologise for
forgetting to mention rounding up. The solution Rick provided does work for
me. Thank you so much for your help.
 
This should work

=MAX(CEILING(A1+1,10)-1,0)


note that Rick's solution does not round up when the last integer is 9 and
there are decimals,
for instance 9.98, it rounds that to 9 or 1329.95 is 1329. Given your
preferences
shouldn't those be rounded to 19 and 1339 respectively?


--


Regards,


Peo Sjoblom
 
note that Rick's solution does not round up when the last integer is 9 and
there are decimals,
for instance 9.98, it rounds that to 9 or 1329.95 is 1329. Given your
preferences
shouldn't those be rounded to 19 and 1339 respectively?
Click to expand...

Yep, I specifically pointed out that "flaw" prior to showing the formula to
the OP as he did not mention how he wanted to handle that situation.

Rick
 
Sorry, didn't see that. Regardless, my formula takes care of that "flaw"


--


Regards,


Peo Sjoblom
 
Peo (and Rick),

You guys are right in that I want 329.50 to round to 339.00, and I didn't
point that out. Also I neglected originally to say that I want to always
round up not down.

Peo, when I use your solution substituing my situation for your A1 reference
I get an error that I'm missing an arugment. Here is what I put down:

=MAX(CEILING((E5+G5)*1.25)+1,10)-1,0)

I'm sure I'm just blind. What am I missing? Thanks!
 
The reason I put "flaw" in quotes is because I was not completely sure
whether the OP wanted the rounding my formula does or the one yours does. It
seems strange to me that 1329.01 through 1329.99 (**especially** the
beginning of that range) should be rounded up to $1339, hence the formula I
posted. It may well turn out, however, that your rounding method is the
actual one the OP wants... we will just have to wait for him to come back to
this thread to see.

Rick
 
=MAX(CEILING((E5+G5)*1.25+1,10)-1,0)

but it is moot anyway if you want 9.5 to round down to 9 and not to 19


--


Regards,


Peo Sjoblom
 
Peo - Your solution is EXACTLY what I DO want. I ALWAYS want to round UP to
the next $9.00. Thank you very much!
 
Back
Top