Nested if statements

J

JICDB

I am writing a request form for our printing department and I need some help
with a nested if formula. I have two main fields in this formula - size
requested and size used. If the request is for 8.5 x 11 paper, lets say 100
pcs, and we use 8.5 x 11 paper to fill the request the quantity of sheets of
paper used is 100 (Quantity *1). If we use 11x17 paper to fill the request,
we can get two 8.5 x 11 pieces on that paper so I need to divide the
requested amount by 2 to see how many sheets of paper were used. Because
there are varying size combinations I don't know the best way to figure out
sheets used.

In English I want to say (R=size requested, U=size Used, Q = quantity
requested)

If R = U than Q *1
If R = 8.5x11 and U = 11x17 than Q/2
If R = 8.5x11 and U = 17x22 than Q/4
If R = 11x17 and U = 17 x 22 than Q/2
If R = 8x15 and U = 11x17 than Q*1
If R = 8x14 and U = 11x22 than Q/2
 
D

Don Guillett

basic idea
=q/if(r=8.5x11,if(u=11x17,2,4)),nextcondition))
or
work using /2 with the OR statement
=q/if(or(cond1,cond2,cond3),2,next
 
J

JICDB

Thanks! I used your basic idea to come up with this formula that works
perfect! Thanks again! (I10 = requested size, C15 = size used , and D8 =
quantity requested)

IF(I10=C15,D8*1,IF(AND(I10="8.5 x 11",C15="17 x 22"),D8/4,IF(AND(I10="8.5 x
14",C15="11 x 17"),D8*1,IF(OR(AND(I10="8.5 x 11",C15="11 x 17"),AND(I10="11 x
17",C15="17 x 22"),AND(I10="8.5 x 14",C15="17 x 22")),D8/2,""))))
 

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