2 conditions in IF/AND formula

[zadar24]

Hi all,

I need help with my formula. I have two different columns which are needed to give me my answer in the 3rd column.

Column 1 is $ AMOUNT
Column 2 is a Yes/No Choice

So, between these 2 fields, I want 5 different options:
1. <$100 and YES - return a "1"
2. <$100 and NO - return a "-1"
3. >=$100 and YES - return a "1"
4. >=$100 and NO - return a "-1"
5. Blank cell for $ and either YES/NO - return a "0"

Here is my formula so far:
=IF((AND(A2<100,B2="YES")),1,IF((AND(A2>=100,B2="YES")),1,-1))

I'm close but I can't complete my formula. All that is missing is Option 5: if I don't have a $ amount, put a "0" regardless of the second (Yes/No) condition.

Thanks in advance!

[NBVC]

tRY:

=IF(A2="",0,IF(OR(AND(A2<100,B2="YES"),AND(A2>=100,B2="YES")),1,-1))

[zadar24]

it worked..thanks so much

[NBVC]

After looking at this again, it seems that really you want a 1 if B2="Yes" and and a -1 if B2 is otherwise....It doesn't really seem to matter what the number in A2 is, as long as it is not blank.

so wouldn't this just do it?

=IF(A2="",0,IF(B2="YES",1,-1))

[zadar24]

that works as well and it's cleaner.
I'll use this.

Thanks again