IF with two ANDs and 3 answers

**jomili** · 10-26-2010, 11:34 AM

I'm stuck on syntax. Here's my problem:
Column Q contains Current Balance of Overtime Hours
Column R contains OT earned in a specified period
Column S contains Yes or No (whether to pay out or not)

In column T I want this:
IF(S="Yes") AND Q>R, answer would be R
IF(S="Yes") AND Q<R, Answer would be Q
IF(S="No"), Answer would be 0.

I'm getting my head all twisted around trying to create the formula. Can anyone help?

Thanks,
John

**contaminated** · 10-26-2010, 11:39 AM

Maybe these ones

=IF(S1="Yes",IF(Q1>R1,R1,IF(Q1<R1,Q1)),0)
or
=IF(AND(S1="Yes",Q1>R1),R1,IF(AND(S1="Yes",Q1<R1),Q1,IF(S1="No",0)))

**6StringJazzer** · 10-26-2010, 11:48 AM

Originally Posted by jomili

IF(S="Yes") AND Q>R, answer would be R
IF(S="Yes") AND Q<R, Answer would be Q
IF(S="No"), Answer would be 0.

There are two cases that may need to be considered:

S="Yes" AND Q=R
S <> "Yes" AND S <> "No"

What do you want in those cases?

**DonkeyOte** · 10-26-2010, 11:56 AM

Sounds as though you want:

Please Login or Register  to view this content.

**jomili** · 10-26-2010, 12:36 PM

Wow, a lot of help in such a short time! I just got out of a meeting, so just looked at the responses.

Contaminated,
your first formula worked admirably for my test data. I haven't yet tried the second one.

6StringJazzer, you bring up two very good points.
IF S="Yes" AND Q=R, then either R or Q would be appropriate
IF S <> "Yes" AND S <> "No" it would have to be blank, since values are restricted. So, we would want no result.

DonkeyOte, your formula strangley excites me, because I'm not familiar with the MIN function. I haven't tried it yet, but plan to look into it. Thanks for coming from another angle.

**jomili** · 10-26-2010, 12:48 PM

DonkeyOte,

You win the prize! I've taken a few minutes to look at your formula, and I understand it. It does what I asked for, and takes care of the exceptions 6StringJazzer brought up.

If I understand it right, your formula

Please Login or Register  to view this content.

means

1) IF S1 is NOT equal to "Yes" (takes care of it there's something other than Yes or NO) put a Zero.
2) IF S1 IS equal to "Yes", put the smaller of Q1 or R1.

Brilliant! Small, elegant, but oh so powerful! Thanks so much for showing me this!