Excel Function IF AND

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Not too good at maths, trying to produce a formula to calulate discounts.
if a>0.25 and b>10 discount =Z
if a>0.25 and b>49 discount =Y
if a>1 and b>10 discount =X
if a>1 and b>49 discount =W
if b>499 Discount =V
I Have "=IF(AND(C4>0.25,B5>10),I15,0)" for 1st line but can't seem to add
other lines to the formula is it possible to have 1 formula that carries out
all the calulations?
 
If you wanted to go the route of a nested IF statement, then this may work
for you.

=IF(C4>0.25,IF(B5>10,I15,IF(B5>49,"DiscountYCell",0)),IF(C4>1,IF(B5>10,"DiscountXCell",IF(B4>49,"DiscountWCell",0)),IF(C4>499,"DiscountVCell",0)))

However, you may want to consider creating a table and using VLOOKUP.

HTH,
Paul
 
=IF(AND(A4>0.25,B4>10),Z,IF(AND(A4>0.25,B4>49),Y,IF(AND(A4>1,B4>10),X,IF(AND(A4>1,B4>49),W,IF(B4>499,V,"Invalid")))))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks for getting back so quickly, I've filled the formula as below to test
and it's not working, any suggestion please.
=IF(AND(C4>0.25,B5>10),5,IF(AND(C4>0.25,B5>49),7.5,IF(AND(C4>1,B5>10),10,IF(AND(C4>1,B5>49),15,IF(B5>499,20,0)))))
 
Hi Filled in details to test formula as below and it's not working any
suggestion please
=IF(C4>0.25,IF(B5>10,5,IF(B5>49,7.5,0)),IF(C4>1,IF(B5>10,10,IF(B4>49,15,0)),IF(C4>499,20,0)))
not sure what a VLOOKUP is will search help
Phil
 
How is it not working? What is it doing or not doing?
Gives us an example of your data in C4 and B5 and what result you expect.

Regards,
Paul
 
This is what I'd expect & in () what's happening,
C4 = 0.25 & B5 = 10 answer = 0 (this works)
C4 = 0.26 & B5 = 11 answer = 5 (this works)
C4 = 0.26 & B5 = 50 answer = 7.5 (however I change data never goes above 5)
C4 = 1.1 & B5 = 11 answer = 10
C4 = 1.1 & B5 = 50 answer = 15
B5 = 500 answer = 20

Phil
 
That is not a good reply, how can anyone help you when all you say is that
it's not working.
Surely you can't expect people to be mind readers? What did not work, did
you get an error, was the result unexpected or did your computer blow up?
 
Try this:

=IF(B5>499,20,IF(C4>1,IF(B5>49,15,IF(B5>10,10,0)),IF(C4>0.25,IF(B5>49,7.5,IF(B5>10,5,0)),0)))

HTH,
Paul
 
Hi Peo
Sorry about the bad post, 1st time I've posted anything, should have thought
it through before sending.
Re: details see previous reply to PCLICVE, when I change my 2 variables, for
the 1st 2 quantities the correct answer is return after that increasing the
quantites does not increase the discount.
It seems that this part of the formula is being acted on
i.e."=IF(AND(C4>0.25,B5>10),I15,0)" then the calculation stops.
Thanks all for the time you've spent on this,
Phil
 
I Have "=IF(AND(C4>0.25,B5>10),I15,0)"

Hi. I believe the logic is a little off.
If values pass the first test, it is possible they pass all the other tests.
For example, if C4 =2, and B5=600, then this meets every test, especially
the first test, and not the last test as probably expected.
if a>0.25 and b>10 discount =Z
if a>0.25 and b>49 discount =Y
if a>1 and b>10 discount =X
if a>1 and b>49 discount =W
if b>499...
Click to expand...

Usually, one works from highest to lowest.

=IF(B1>499,20,IF(AND(A1>1,B1>49),15,IF(AND(A1>1,B1>10),10,IF(AND(A1>0.25,B1>49),7.5,IF(AND(A1>0.25,B1>10),5,0)))))
 
There are two solutions:

=IF(B5>499,20,IF(AND(C4>1,B5>49),25,IF(AND(C4>1,B5>10),10,IF(AND(C4>0.25,B5>49),7.5,IF(AND(C4>0.25,B5>10),5,0)))))

and

=IF(AND(C4>1,B5>49),25,IF(AND(C4>1,B5>10),10,IF(AND(C4>0.25,B5>49),7.5,IF(AND(C4>0.25,B5>10),5,IF(B5>499,20,0)))))
depending if B5>499 should be tested before or after then and conditions.

The reason the other solutions failed is because they did the comparisons in
the wrong sequence: if a1 is 10.: =IF(A1>2,5,IF(A1>5,6,0)) the condition
a>2 is true, so 5 is returned and the condition a1>6 is never tested. The
conditions have to be done in the reverse sequence =IF(A1>5,6,IF(A1>2,5,0))
 
There are two solutions:

=IF(B5>499,20,IF(AND(C4>1,B5>49),25,IF(AND(C4>1,B5>10),10,IF(AND(C4>0.25,B5>49),7.5,IF(AND(C4>0.25,B5>10),5,0)))))

and

=IF(AND(C4>1,B5>49),25,IF(AND(C4>1,B5>10),10,IF(AND(C4>0.25,B5>49),7.5,IF(AND(C4>0.25,B5>10),5,IF(B5>499,20,0)))))

depending if B5>499 should be tested before or after the "and" conditions.

The reason the other solutions failed is because they did the comparisons in
the wrong sequence: if a1 is 10.: =IF(A1>2,5,IF(A1>5,6,0)) the condition
a>2 is true, so 5 is returned and the condition a1>5 is never tested. The
conditions have to be done in the reverse sequence =IF(A1>5,6,IF(A1>2,5,0))
 
I think my formula in the abve post was shorter...but they seem to
accomplish the same result.
 
A question, why do you keep posting multiple answers to questions posted
here? Is there something wrong with your connection?. You posted the exact
same answer a couple of minutes earlier? Another day you posted the same
answer 4 times! I can imagine this happening once because you hit the send
button twice. You obviously know Excel so one would expect that you would
know how to use a newsreader as well.
 
Another interesting option one can sometimes use is the following:
Since both A1 & B1 can take on 3 states, we think of the combinations as a
base-3 number.
Excel's bug in Mod() prevents us from including B1>499, so we have to factor
that out.
I din't run another program to see if the numbers could be smaller.

=IF(B1>499,20,MOD(28793600,15*(3*((B1>10)+(B1>49))+(A1>0.25)+(A1>1))+10)/2)
 
Hi Paul
Yes your right, sorry there were so many post coming in I just missed yours.
I've tested it & it works & is shorter, thanks for all your help.
All the best
Phil
 
Back
Top