Excel - RoundUP ??? 2

[mxw]

Hello,

I've working on a formula equation, and need to roundup in incements of 50/100. How do I do that please.

example:
=C3*C4
the result is 2712 (this is under 50)
how do I get it to jump to 2750?
or
=C3*C4
this result is 3880 (this is past 50/next is 100)
how do I get this to jump to 3900?

thanks kindly,
mxw


~Eeegads~

 

[DaleWatson123321]

Hi mxw,

I've created and tested the following formula on your 4-digit values and it works. It also works on ANY whole number - regardless of size.

=(C5-MOD(C5/100,1)*100)+IF(AND(MOD(C5/100,1)>0,MOD(C5/100,1)<=0.5),50,100)

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[mxw]

Thank you!

I used C6 and targeted C5
I reset the calculation intergations to 10.

Zowie, works like a charm *beaming.
Fits like a 'glove'

I wouldn't have figured out that formula, thanks so much for your help.

mxw

~Eeegads~

 

[Geof]

Hi
How about
=int((number+49.99)/50)*50
It seems to work OK

 

[aexley]

Hi all,

Could I replace the 50 with a 20 and 49.99 with 19.99 to roundup to the nearest value divisible by 20?

Thanks in advance

Aexley

 

[aexley]

Yes would be the answer to that then.

aexley

 

[aexley]

Alternatively, how could the formula be altered to round to the nearest 20? This would be REALLY useful. Thanks

aexley

 

[xlbo]

My 2p - got intrigued and wanted to see if I could shorten Dale's formula:
=IF(C5/50=INT(C5/50),C5,50*(INT(C5/50)+1))

for increments of 20, just swap all the 50's for 20's

=IF(C5/20=INT(C5/20),C5,20*(INT(C5/20)+1))

Alternatively, if you want to incrememnt by different amounts and see how it changes things, just put a cell reference instead of the numbers and enter any number you like into there:
=IF(C5/C1=INT(C5/C1),C5,20*(INT(C5/C1)+1))

where formula result is in C5 and variable rounding integer is in C1

HTH Rgds
~Geoff~

 

[xlbo]

ooops, make that:
=IF(C5/C1=INT(C5/C1),C5,C1*(INT(C5/C1)+1))
Rgds
~Geoff~