Hello. First post here. I trying to make a formula that will multiply a cell by 1.43 and then round the total up to the nearest 45 or 95 with no decimal points. Any help would be greatly appreciated.
Round Up To Nearest X`



Re: Round Up To Nearest 45 Or 95
Try something like this to round up the value in cell B1:
=ROUNDUP((B1*1.43)/45,0)*45
Jim

Re: Round Up To Nearest 45 Or 95
That doesn't seem to be working. It just rounds it to the nearest multiple of ten. I think i'm a little further along now though. This will round to 95 everytime...
=ROUNDUP((C2*1.25),2)5
Now I just need to find out how to add the round to 45 in there. 
Re: Round Up To Nearest 45 Or 95
Datzneat,
Please do not add code tags to formulas. You can if you like add formula tags like this (new feature of website:
[noparse][f]=roundup (c2, 2) 5[/f][/noparse]
gives
[f]=roundup (c2, 2) 5[/f]

Re: Round Up To Nearest 45 Or 95
I attached a sample of the formula I posted and it seems to work. Unless, I don't understand what you want...
Jim


Re: Round Up To Nearest 45 Or 95
[COLOR="Blue"][noparse]= Min( Roundup( A1 * 1.43 / 45, 0) * 45, Roundup( A1 * 1.43 / 95, 0) * 95)[/noparse][/COLOR]

Re: Round Up To Nearest 45 Or 95
Yea i'm still seeing the wrong numbers with these. It should only be coming up with numbers that end in 45 or 95.
ie.
11 would be 45
32 would be 4546 would be 95
1865 would be 1895and so forth.

Re: Round Up To Nearest 45 Or 95
See if this gets us any closer.

Re: Round Up To Nearest 45 Or 95
Quote from datzneat11 would be 45
32 would be 45
46 would be 95
1865 would be 1895That's incorrect.
11 * 1.43 = 15.73, rounded up to multiple of 45 is 45, to 95 is 95, smaller is 45
32 * 1.43 = 45.76, rounded up to multiple of 45 is 90, to 95 is 95, smaller is 90
46 * 1.43 = 65.78, rounded up to multiple of 45 is 90, to 95 is 95, smaller is 90
1896 * 1.43 = 2711.28, rounded up to multiple of 45 is 2745, to 95 is 2755, smaller is 2745 
Re: Round Up To Nearest 45 Or 95
I don't get what you're saying shg... those were just examples of what i need the numbers to round to, all multiplying aside.
The problem with that code Jim is that it isn't just one code. It is multiple codes, and i'm not sure if rounddown is the best way to do it...
Is there a way to work an OR statement into : =ROUNDUP((C2*1.25),2)5


Re: Round Up To Nearest 45 Or 95
Hi shg,
I think OP is still struggling with how to say what they want. I don't think they meant a "multiple", just something else.
Jim

Re: Round Up To Nearest 45 Or 95
Datzneat, is what you mean that the number * 1.43 should be rounded up to the nearest number ending in 45 or 95?

Re: Round Up To Nearest 45 Or 95
That is correct sir. Sorry if i sound like a bumbling idiot on here lol.

Re: Round Up To Nearest 45 Or 95
Hi,
=CEILING(A1*1.43+5,50)5
HTH

Re: Round Up To Nearest 45 Or 95
One more...


Re: Round Up To Nearest 45 Or 95
See if this gives the results you expect:
[COLOR="Blue"]= 1.43 * A1 MOD( 1.43 * A1, 100) + IF( MOD( 1.43 * A1, 100) > 45, 95, 45 )[/COLOR]

Re: Round Up To Nearest 45 Or 95
Looks damn good to me. It's sad how close I was to getting that same formula. Thanks everyone.
EDIT: Sorry. Krish's worked. I'll still check out the one you gave me shg.

Re: Round Up To Nearest 45 Or 95
Same results shg. Thanks again for all of your help!

Re: Round Up To Nearest 45 Or 95
Great. Kris's formula is much cleaner  wish I'd seen that solution.

Re: Round Up To Nearest 45 Or 95
15000*1.43=21450
but result is 21495 with both fomula.
regards, 

Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!