Complicated IF statement returning false

mecerrato

New Member
Joined
Oct 5, 2015
Messages
29
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))))))))))
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,056
Office Version
  1. 2019
Platform
  1. Windows
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:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,056
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Oct 5, 2015
Messages
29

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
11,056
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Oct 5, 2015
Messages
29
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,114,659
Messages
5,549,268
Members
410,905
Latest member
Extjel
Top