Excel Function IF AND
 

Hi Not too good at maths, trying to produce a formula to calulate discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

If you wanted to go the route of a nested IF statement, then this may work
for you.

=IF(C40.25,IF(B510,I15,IF(B549,"DiscountYCell", 0)),IF(C41,IF(B510,"DiscountXCell",IF(B449,"Dis countWCell",0)),IF(C4499,"DiscountVCell",0)))

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

HTH,
Paul

"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

=IF(AND(A40.25,B410),Z,IF(AND(A40.25,B449),Y,I F(AND(A41,B410),X,IF(AND(A41,B449),W,IF(B4499 ,V,"Invalid")))))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

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(C40.25,B510),5,IF(AND(C40.25,B549),7.5 ,IF(AND(C41,B510),10,IF(AND(C41,B549),15,IF(B5 499,20,0)))))

"Bob Phillips" wrote:

=IF(AND(A40.25,B410),Z,IF(AND(A40.25,B449),Y,I F(AND(A41,B410),X,IF(AND(A41,B449),W,IF(B4499 ,V,"Invalid")))))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

Hi Filled in details to test formula as below and it's not working any
suggestion please?
=IF(C40.25,IF(B510,5,IF(B549,7.5,0)),IF(C41,IF (B510,10,IF(B449,15,0)),IF(C4499,20,0)))
not sure what a VLOOKUP is will search help
Phil

"PCLIVE" wrote:

If you wanted to go the route of a nested IF statement, then this may work
for you.

=IF(C40.25,IF(B510,I15,IF(B549,"DiscountYCell", 0)),IF(C41,IF(B510,"DiscountXCell",IF(B449,"Dis countWCell",0)),IF(C4499,"DiscountVCell",0)))

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

HTH,
Paul

"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

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

"Indigo" wrote in message
...
Hi Filled in details to test formula as below and it's not working any
suggestion please?
=IF(C40.25,IF(B510,5,IF(B549,7.5,0)),IF(C41,IF (B510,10,IF(B449,15,0)),IF(C4499,20,0)))
not sure what a VLOOKUP is will search help
Phil

"PCLIVE" wrote:

If you wanted to go the route of a nested IF statement, then this may
work
for you.

=IF(C40.25,IF(B510,I15,IF(B549,"DiscountYCell", 0)),IF(C41,IF(B510,"DiscountXCell",IF(B449,"Dis countWCell",0)),IF(C4499,"DiscountVCell",0)))

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

HTH,
Paul

"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

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


"PCLIVE" wrote:

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

"Indigo" wrote in message
...
Hi Filled in details to test formula as below and it's not working any
suggestion please?
=IF(C40.25,IF(B510,5,IF(B549,7.5,0)),IF(C41,IF (B510,10,IF(B449,15,0)),IF(C4499,20,0)))
not sure what a VLOOKUP is will search help
Phil

"PCLIVE" wrote:

If you wanted to go the route of a nested IF statement, then this may
work
for you.

=IF(C40.25,IF(B510,I15,IF(B549,"DiscountYCell", 0)),IF(C41,IF(B510,"DiscountXCell",IF(B449,"Dis countWCell",0)),IF(C4499,"DiscountVCell",0)))

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

HTH,
Paul

"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

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?


--
Regards,

Peo Sjoblom


"Indigo" wrote in message
...
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(C40.25,B510),5,IF(AND(C40.25,B549),7.5 ,IF(AND(C41,B510),10,IF(AND(C41,B549),15,IF(B5 499,20,0)))))

"Bob Phillips" wrote:

=IF(AND(A40.25,B410),Z,IF(AND(A40.25,B449),Y,I F(AND(A41,B410),X,IF(AND(A41,B449),W,IF(B4499 ,V,"Invalid")))))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

Try this:

=IF(B5499,20,IF(C41,IF(B549,15,IF(B510,10,0)), IF(C40.25,IF(B549,7.5,IF(B510,5,0)),0)))

HTH,
Paul

"Indigo" wrote in message
...
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


"PCLIVE" wrote:

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

"Indigo" wrote in message
...
Hi Filled in details to test formula as below and it's not working any
suggestion please?
=IF(C40.25,IF(B510,5,IF(B549,7.5,0)),IF(C41,IF (B510,10,IF(B449,15,0)),IF(C4499,20,0)))
not sure what a VLOOKUP is will search help
Phil

"PCLIVE" wrote:

If you wanted to go the route of a nested IF statement, then this may
work
for you.

=IF(C40.25,IF(B510,I15,IF(B549,"DiscountYCell", 0)),IF(C41,IF(B510,"DiscountXCell",IF(B449,"Dis countWCell",0)),IF(C4499,"DiscountVCell",0)))

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

HTH,
Paul

"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

Hi Peo
Sorry about the bad post, 1st time I've posted anything, should have thought
it through before sending.
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(C40.25,B510),I15,0)" then the calculation stops.
Thanks all for the time you've spent on this,
Phil


"Peo Sjoblom" wrote:

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?


--
Regards,

Peo Sjoblom


"Indigo" wrote in message
...
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(C40.25,B510),5,IF(AND(C40.25,B549),7.5 ,IF(AND(C41,B510),10,IF(AND(C41,B549),15,IF(B5 499,20,0)))))

"Bob Phillips" wrote:

=IF(AND(A40.25,B410),Z,IF(AND(A40.25,B449),Y,I F(AND(A41,B410),X,IF(AND(A41,B449),W,IF(B4499 ,V,"Invalid")))))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

I Have "=IF(AND(C40.25,B510),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 a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499...

Usually, one works from highest to lowest.

=IF(B1499,20,IF(AND(A11,B149),15,IF(AND(A11,B1 10),10,IF(AND(A10.25,B149),7.5,IF(AND(A10.25,B 110),5,0)))))

--
HTH :)
Dana DeLouis


"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

There are two solutions:

=IF(B5499,20,IF(AND(C41,B549),25,IF(AND(C41,B5 10),10,IF(AND(C40.25,B549),7.5,IF(AND(C40.25,B 510),5,0)))))

and

=IF(AND(C41,B549),25,IF(AND(C41,B510),10,IF(AN D(C40.25,B549),7.5,IF(AND(C40.25,B510),5,IF(B5 499,20,0)))))
depending if B5499 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(A12,5,IF(A15,6,0)) the condition
a2 is true, so 5 is returned and the condition a16 is never tested. The
conditions have to be done in the reverse sequence =IF(A15,6,IF(A12,5,0))


"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

No worries. Look at Dana's answer to see if it fits


--
Regards,

Peo Sjoblom



"Indigo" wrote in message
...
Hi Peo
Sorry about the bad post, 1st time I've posted anything, should have
thought
it through before sending.
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(C40.25,B510),I15,0)" then the calculation stops.
Thanks all for the time you've spent on this,
Phil


"Peo Sjoblom" wrote:

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?


--
Regards,

Peo Sjoblom


"Indigo" wrote in message
...
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(C40.25,B510),5,IF(AND(C40.25,B549),7.5 ,IF(AND(C41,B510),10,IF(AND(C41,B549),15,IF(B5 499,20,0)))))

"Bob Phillips" wrote:

=IF(AND(A40.25,B410),Z,IF(AND(A40.25,B449),Y,I F(AND(A41,B410),X,IF(AND(A41,B449),W,IF(B4499 ,V,"Invalid")))))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND Wrong sequence in tests
 

There are two solutions:

=IF(B5499,20,IF(AND(C41,B549),25,IF(AND(C41,B5 10),10,IF(AND(C40.25,B549),7.5,IF(AND(C40.25,B 510),5,0)))))

and

=IF(AND(C41,B549),25,IF(AND(C41,B510),10,IF(AN D(C40.25,B549),7.5,IF(AND(C40.25,B510),5,IF(B5 499,20,0)))))

depending if B5499 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(A12,5,IF(A15,6,0)) the condition
a2 is true, so 5 is returned and the condition a15 is never tested. The
conditions have to be done in the reverse sequence =IF(A15,6,IF(A12,5,0))


"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?


"Dave Thomas" wrote in message
et...
There are two solutions:

=IF(B5499,20,IF(AND(C41,B549),25,IF(AND(C41,B5 10),10,IF(AND(C40.25,B549),7.5,IF(AND(C40.25,B 510),5,0)))))

and

=IF(AND(C41,B549),25,IF(AND(C41,B510),10,IF(AN D(C40.25,B549),7.5,IF(AND(C40.25,B510),5,IF(B5 499,20,0)))))
depending if B5499 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(A12,5,IF(A15,6,0)) the
condition a2 is true, so 5 is returned and the condition a16 is never
tested. The conditions have to be done in the reverse sequence
=IF(A15,6,IF(A12,5,0))


"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

Thanks Dana
That works really well, easy when you know how.

all the best
Phil

"Dana DeLouis" wrote:

I Have "=IF(AND(C40.25,B510),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 a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499...

Usually, one works from highest to lowest.

=IF(B1499,20,IF(AND(A11,B149),15,IF(AND(A11,B1 10),10,IF(AND(A10.25,B149),7.5,IF(AND(A10.25,B 110),5,0)))))

--
HTH :)
Dana DeLouis


"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND Wrong sequence in tests
 

Hi Thank's Dave
I'll test both & see what answers I get.
All the best
Phil

"Dave Thomas" wrote:

There are two solutions:

=IF(B5499,20,IF(AND(C41,B549),25,IF(AND(C41,B5 10),10,IF(AND(C40.25,B549),7.5,IF(AND(C40.25,B 510),5,0)))))

and

=IF(AND(C41,B549),25,IF(AND(C41,B510),10,IF(AN D(C40.25,B549),7.5,IF(AND(C40.25,B510),5,IF(B5 499,20,0)))))

depending if B5499 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(A12,5,IF(A15,6,0)) the condition
a2 is true, so 5 is returned and the condition a15 is never tested. The
conditions have to be done in the reverse sequence =IF(A15,6,IF(A12,5,0))


"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?


"Dave Thomas" wrote in message
et...
There are two solutions:

=IF(B5499,20,IF(AND(C41,B549),25,IF(AND(C41,B5 10),10,IF(AND(C40.25,B549),7.5,IF(AND(C40.25,B 510),5,0)))))

and

=IF(AND(C41,B549),25,IF(AND(C41,B510),10,IF(AN D(C40.25,B549),7.5,IF(AND(C40.25,B510),5,IF(B5 499,20,0)))))
depending if B5499 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(A12,5,IF(A15,6,0)) the
condition a2 is true, so 5 is returned and the condition a16 is never
tested. The conditions have to be done in the reverse sequence
=IF(A15,6,IF(A12,5,0))


"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

I think my formula in the abve post was shorter...but they seem to
accomplish the same result.


"Indigo" wrote in message
...
Thanks Dana
That works really well, easy when you know how.

all the best
Phil

"Dana DeLouis" wrote:

I Have "=IF(AND(C40.25,B510),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 a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499...

Usually, one works from highest to lowest.

=IF(B1499,20,IF(AND(A11,B149),15,IF(AND(A11,B1 10),10,IF(AND(A10.25,B149),7.5,IF(AND(A10.25,B 110),5,0)))))

--
HTH :)
Dana DeLouis


"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND Wrong sequence in tests
 

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.



--
Regards,

Peo Sjoblom



"Dave Thomas" wrote in message
...
There are two solutions:

=IF(B5499,20,IF(AND(C41,B549),25,IF(AND(C41,B5 10),10,IF(AND(C40.25,B549),7.5,IF(AND(C40.25,B 510),5,0)))))

and

=IF(AND(C41,B549),25,IF(AND(C41,B510),10,IF(AN D(C40.25,B549),7.5,IF(AND(C40.25,B510),5,IF(B5 499,20,0)))))

depending if B5499 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(A12,5,IF(A15,6,0)) the condition
a2 is true, so 5 is returned and the condition a15 is never tested. The
conditions have to be done in the reverse sequence
=IF(A15,6,IF(A12,5,0))


"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?


"Dave Thomas" wrote in message
et...
There are two solutions:

=IF(B5499,20,IF(AND(C41,B549),25,IF(AND(C41,B5 10),10,IF(AND(C40.25,B549),7.5,IF(AND(C40.25,B 510),5,0)))))

and

=IF(AND(C41,B549),25,IF(AND(C41,B510),10,IF(AN D(C40.25,B549),7.5,IF(AND(C40.25,B510),5,IF(B5 499,20,0)))))
depending if B5499 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(A12,5,IF(A15,6,0)) the
condition a2 is true, so 5 is returned and the condition a16 is never
tested. The conditions have to be done in the reverse sequence
=IF(A15,6,IF(A12,5,0))


"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

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 B1499, so we have to factor
that out.
I din't run another program to see if the numbers could be smaller.

=IF(B1499,20,MOD(28793600,15*(3*((B110)+(B149)) +(A10.25)+(A11))+10)/2)

--
Dana DeLouis


"PCLIVE" wrote in message
...
I think my formula in the abve post was shorter...but they seem to
accomplish the same result.


"Indigo" wrote in message
...
Thanks Dana
That works really well, easy when you know how.

all the best
Phil

"Dana DeLouis" wrote:

I Have "=IF(AND(C40.25,B510),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 a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499...

Usually, one works from highest to lowest.

=IF(B1499,20,IF(AND(A11,B149),15,IF(AND(A11,B1 10),10,IF(AND(A10.25,B149),7.5,IF(AND(A10.25,B 110),5,0)))))

--
HTH :)
Dana DeLouis


"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?

Excel Function IF AND
 

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

"PCLIVE" wrote:

I think my formula in the abve post was shorter...but they seem to
accomplish the same result.


"Indigo" wrote in message
...
Thanks Dana
That works really well, easy when you know how.

all the best
Phil

"Dana DeLouis" wrote:

I Have "=IF(AND(C40.25,B510),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 a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499...

Usually, one works from highest to lowest.

=IF(B1499,20,IF(AND(A11,B149),15,IF(AND(A11,B1 10),10,IF(AND(A10.25,B149),7.5,IF(AND(A10.25,B 110),5,0)))))

--
HTH :)
Dana DeLouis


"Indigo" wrote in message
...
Hi Not too good at maths, trying to produce a formula to calulate
discounts.
if a0.25 and b10 discount =Z
if a0.25 and b49 discount =Y
if a1 and b10 discount =X
if a1 and b49 discount =W
if b499 Discount =V
I Have "=IF(AND(C40.25,B510),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?