IF formula to round up values depending upon their outcome

[JM_Mc]

I am using an IF formula to calculate between two cells, one is J (width) the
other is K (length). Currently these formulas give an answer that then has
to be rounded up based on the decimal place. I need the formula to also
round up the amount to quarter increments. For example if the answer is 1.17
then the formula needs to make it 1.25, if it is 1.33 then the formula needs
to make it 1.5, and finally if it is 1.63 then the formula needs to make it
1.75. So how do I add or make the formula round up to quarter increments?
The formula that I am using is:
=IF(J41<=3,K41/4,IF(J41<=4,K41/3,IF(J41>4,K41/2)))

 

[Sandy Mann]

Tty:

=IF(J41<=3,CEILING(K41/4,0.25),IF(J41<=4,CEILING(K41/3,0.25),IF(J41>4,CEILING(K41/2,0.25))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[JM_Mc]

Thank you so much this has solved a question that has been around for quite
sometime. Could you answer another formula for me. I have two cells that I
need to sum up, one cell has a set value (E), and the second cell is a value
that is only entered when needed (F). How do I sum up the two only when F
has a value in it? (If there is no value in (F) I want the formula to not
place (E)'s value as the sum).

 

[Sandy Mann]

Try:

=IF(F41="","",SUM(E41:F41))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[daddylonglegs]

Here's another option for your first question.....

=CEILING(K41/INT(7-J41),0.25)

 

[daddylonglegs]

Sorry, that would need to be....

=CEILING(K41/MIN(4,MAX(2,INT(7-J41))),0.25)

 

[Shane Devenshire]

Hi Sandy,

You might want to simplify the formula below to read:

=CEILING(K1/IF(J1<=3,4,IF(J1<=4,3,IF(J1>4,2))),0.25)

Cheers,
Shane Devenshire
Microsoft Excel MVP

 

[Shane Devenshire]

Hi again,

I see you got a shorter formula already, that one can also be shortened to:

=CEILING(K1/MIN(4,MAX(2,7-J1)),0.25)

Cheers,
Shane Devenshire
Microsoft Excel MVP