Complicated IF statement returning false

[mecerrato]

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))))))))))

 

[jasonb75]

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.

 

[mecerrato]

Thanks Jasonb75 but this gave me a #VALUE error

 

[jasonb75]

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]

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]

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]

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
here is the link:

[Solved] Complicated IF statement returning false

As I re-read what I wrote below, I see that it sounds a little harsh. I dont mean it to, but I dont feel like re-writing it all, so please accept my words in the friendly manner that I offer them - even if they dont sound that way. :-) I think y...

www.computing.net