Two IF Statements in One Cell Returning Incorrect Results

**Shiks** · 03-31-2011, 07:14 PM

Hello,

I am trying to compare two columns, each with it's own condition and rank then for sorting later. Below is my statement:

=IF(B4<$B$2,IF(C4<$C$2,0,1))

The issue am having is that in some of the destination cells it gives me a "FALSE" result while in others, it outputs either "1" or "0", which is what I would like. I have tried modifying the statement after reading similar posts but I have been unsuccessful. I changed the reference cells to numbers, that didn't help (

).

Any help is greatly appreciated!

P.S. I have attached a file

**Cutter** · 03-31-2011, 07:24 PM

Your IF() statement is lacking a condition.

As you have it your statement is saying:

IF B4<$B$2 do this IF(C4<$C$2,0,1)

otherwise - this is what is lacking. So Excel simply says FALSE.

Do you want some value if B4<$B$2 is TRUE but the IF(C4<$C$2,0,1) when it's FALSE.

If so:

=IF(B4<$B$2,"XXX",IF(C4<$C$2,0,1)) replace the "XXX" with the result you want when B4<$B$2 is TRUE

**Shiks** · 03-31-2011, 08:32 PM

Hi Cutter,

Thank you for responding so quickly!

I would like the statement to say that if both conditions are met (i.e. IF B4<$B$2 and IF C4<$C$2, then 0, else 1) then the result should be 0.

I would like to sort based on the numbers 0 and 1. The strange thing is that the statement works (i.e. gives either 0 or 1) in some cells but then it doesn't in others (i.e. gives output 'FALSE).

Thanks again for your help.

**Cutter** · 03-31-2011, 08:40 PM

OK, I thought that MAY have been what you wanted.

Try:

=IF(AND(B4<$B$2,C4<$C$2),0,1)

**Shiks** · 03-31-2011, 10:07 PM

It worked!!!!!

Thanks Cutter!!! You're awesome, keep up the great work of helping sometimes frustrated Excel users like me

**AsifShabbir** · 04-05-2011, 06:45 AM

Hi,

I have same problem. I want in column G either of the following two conditions otherwise "N/A". Conditions are
=IF(D3<=TODAY() + 42, "1") Column D is containing due date of a task so when It is 6 weeks before the due date I want "1" in Column G and
=IF(H3=Date, "0") Wich means that when Column H has some date I want in Column G "0"
If none of the conditions is met then there should be "N/A" in Column G

Hoping favor from your side

Thanks

**Cutter** · 04-05-2011, 09:27 AM

Please read the Forum Rules (click on the link at the top of the page).

Pay particular attention to Rule #2.

Once you have started your own thread you'll get the help you want.