Help with Rounding up a formula to a whole number

wberg82

New Member
Joined
Feb 19, 2016
Messages
38
=IF(MOD((J29*1.1),12)>=9,CEILING((J29*1.1),12),(J29*1.1))

This current formula I am using is not working the way I want it to. Here is what I am trying to do:

I want the user to enter a number in cell J29. Cell B29 then takes that number and multiplies it by 10%. The result of that number rounds up to the next interval of 12. However, here is where it gets complicated (at least in my eyes).

It only rounds up to the next interval of 12 after the result equals or greater than a certain result of the multiplication, otherwise the output will be that (whole) number. For example:

If user enters "3" in cell J29, the result of the above equation is 3.3. This number needs to round up to 4. However, if the result is greater than or equal to 9, I want the output to round up to 12.

If result is >=9 output is 12
If result is >=20 output is 24
If result is >=30 output is 36
If result is >=42 output is 48

Any help on this would be greatly appreciated.

Thanks!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
test this:
Code:
=IF(MOD((J29*1.1),12)>=9,MROUND((J29*1.1),12),ROUNDUP(J29*1.1,0))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,575
Messages
5,523,681
Members
409,531
Latest member
wo1f

This Week's Hot Topics

Top