IF formula to round up values depending upon their outcome

J

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)))
 
S

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
 
J

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).
 
S

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
 
D

daddylonglegs

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

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

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
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top