# Complicated IF statement returning false

#### mecerrato

##### New Member
I am using this long IF statement to calculate the commission rate for sales people. The value if true portion works but the value if false returns FALSE in the cell instead of executing the false instruction which in this case is (IF(R11<1,(H11*(\$C\$7/10000)),H11*R11). Maybe I cannot have another IF in the False section of the formula?

If the main IF statement is FALSE (meaning Q11 is blank) I want the false portion of the formula to then
test if R11 is blank and if it is blank then (H11*(\$C\$7/10000)) if R11 is not blank then H11*R11

Can someone help me get this right?
=IF(Q11>1,(IF(ISNUMBER(SEARCH("COOP",Q11)),(H11*1%),IF(ISNUMBER(SEARCH("CERBASI",Q11)),(H11*0.5%),IF(ISNUMBER(SEARCH("Direct",Q11)),(H11*0.3%),IF(ISNUMBER(SEARCH("Specialist",Q11)),(H11*0.5%),IF(ISNUMBER(SEARCH("UNITED",Q11)),(H11*1%),IF(ISNUMBER(SEARCH("Subordinate",F11)),(H11*0%),(IF(R11<1,(H11*(\$C\$7/10000)),H11*R11))))))))))

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### jasonb75

##### Well-known Member
Maybe I cannot have another IF in the False section of the formula?
You can have them anywhere you like as long as they return something valid.

As far as I can see the first IF has no false result, so you will see FALSE if Q11<=1

I think that this will correct what you want

=IF(Q11>1,(IF(ISNUMBER(SEARCH("COOP",Q11)),(H11*1%),IF(ISNUMBER(SEARCH("CERBASI",Q11)),(H11*0.5%),IF(ISNUMBER(SEARCH("Direct",Q11)),(H11*0.3%),IF(ISNUMBER(SEARCH("Specialist",Q11)),(H11*0.5%),IF(ISNUMBER(SEARCH("UNITED",Q11)),(H11*1%),IF(ISNUMBER(SEARCH("Subordinate",F11)),(H11*0%))))))))),(IF(R11<1,(H11*(\$C\$7/10000)),H11*R11))

There might be another one missing, but if there is, I haven't found it yet.

edit:- there is another one missing, the part that I moved created a hole after the TRUE result for the Subordinate search. As long as the formula contains all possible matches for the text in Q11 then it shouldn't be a problem, otherwise you will see false if none of the search terms are found.

Last edited:

#### mecerrato

##### New Member
Thanks Jasonb75 but this gave me a #VALUE error

#### jasonb75

##### Well-known Member
Oops, there was a typo in there, thought I had corrected it when I added the edit comment. Try this one instead

=IF(Q11>1,(IF(ISNUMBER(SEARCH("COOP",Q11)),(H11*1%),IF(ISNUMBER(SEARCH("CERBASI",Q11)),(H11*0.5%),IF(ISNUMBER(SEARCH("Direct",Q11)),(H11*0.3%),IF(ISNUMBER(SEARCH("Specialist",Q11)),(H11*0.5%),IF(ISNUMBER(SEARCH("UNITED",Q11)),(H11*1%),IF(ISNUMBER(SEARCH("Subordinate",F11)),(H11*0%)))))))),(IF(R11<1,(H11*(\$C\$7/10000)),H11*R11)))

• mecerrato

#### mecerrato

##### New Member

Thank you this worked. I did receive a similar formula that also worked, thank you very much Here is the other formula, I am posting for others just in case as an alternative.
=IF(ISNUMBER(SEARCH("COOP",Q11)),H11*1%,
IF(ISNUMBER(SEARCH("CERBASI",Q11)),H11*0.5%,
IF(ISNUMBER(SEARCH("Direct",Q11)),H11*0.3%,
IF(ISNUMBER(SEARCH("Specialist",Q11)),H11*0.5%,
IF(ISNUMBER(SEARCH("UNITED",Q11)),H11*1%,
IF(ISNUMBER(SEARCH("Subordinate",F11)),H11*0%,
IF(R11<1,(\$C\$7/10000)*H11,H11*(R11/10000))))))))

#### jasonb75

##### Well-known Member
I did receive a similar formula that also worked
Where did you receive that formula, did you ask the same question on another forum as well? There is nothing wrong with doing that, but if you do, we ask that you tell us about it and provide a link to your thread in the other forum. Most excel help forums have this as a rule.

#### mecerrato

##### New Member
Where did you receive that formula, did you ask the same question on another forum as well? There is nothing wrong with doing that, but if you do, we ask that you tell us about it and provide a link to your thread in the other forum. Most excel help forums have this as a rule.
yes it was from computing.net

Replies
3
Views
193
Replies
8
Views
101
Replies
3
Views
51
Replies
6
Views
128
Replies
4
Views
106