Solved Complicated IF statement returning false

May 22, 2020 at 07:43:59
Specs: Windows 7
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))))))))))


See More: Complicated IF statement returning false

Reply ↓  Report •

✔ Best Answer
May 22, 2020 at 18:34:38
As I re-read what I wrote below, I see that it sounds a little harsh. I don't mean it to, but I don't feel like re-writing it all, so please accept my words in the friendly manner that I offer them - even if they don't sound that way. :-)

I think you may be confused as to how a Nested IF works.

I say that because of your use of the phrase "value if true portion works but the value if false returns FALSE"

In a Nested IF, there is no singular value_if_true portion and no singular value_if_false. They aren't single entities like you are making it sound. There needs to be a value_if_true and value_if_false argument for every logical_test.

The fact that you have a open parenthesis before your second if also leads me to the conclusion that you think the True and False "portions" (as you describe them) are isolated entities. It seems as if you feel that all of your Q11 tests is a single value_if_true argument for the logical_test IF(Q11>1. That is not the case. The only value_if_true argument for your opening IF is the COOP search. After that, all value_if_true and value_if_false arguments belong only to their own preceding IF.

The basic syntax for a Nested If look like this:

IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))

That final value_if_false2 is only returned if everything else before it is FALSE.

So, in your case, it doesn't appear that you need to check if Q11 is empty or not, you can simply allow all of the SEARCHes to "fail". If none of the SEARCHes return a value, then Q11 must be empty (or at least not match any of your search criteria.)

Therefore I think that this formula will work for you, although I am still a bit confused as to whether the "Subordinate" IF for F11 is in the proper location. That may cause some issues. Shouldn't that be the very first thing that you check?

=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*H11/10000,H11*R11)))))))

Let me know if that works.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
May 22, 2020 at 13:39:35
Question: Before I dig into this, I want to make sure that the clause that refers to F11 is correct.

IF(ISNUMBER(SEARCH("Subordinate",F11)

The sudden switch from checking Q11 numerous times to checking F11 jumped out at me and I want to make sure that that is correct.

IOW , that says: "If all the Q11 searches fail, then search F11 for Subordinate"

Just making sure that that is not a typo.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
May 22, 2020 at 13:55:59
It is correct, it’s a separate field that tells me it’s a subordinate lien and if so the commission is zero.
On a separate note, I was thinking that maybe I should be using IFS as it handles multiple criteria cleaner but I couldn’t make it work either.

message edited by mecerrato


Reply ↓  Report •

#3
May 22, 2020 at 14:25:31
I should be using IFS as it handles multiple criteria cleaner

I would suggest a VLOOKUP() table.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Related Solutions

#4
May 22, 2020 at 15:17:02
re: "It is correct, it’s a separate field that tells me it’s a subordinate lien and if so the commission is zero."

I'm a bit confused by that. If a Subordinate loan pays 0, why wouldn't you be checking that first, instead of waiting to see if all of the Q11 searches fail? Could F11 contain Subordinate yet the loan not be subordinate because of what is in Q11?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#5
May 22, 2020 at 18:34:38
✔ Best Answer
As I re-read what I wrote below, I see that it sounds a little harsh. I don't mean it to, but I don't feel like re-writing it all, so please accept my words in the friendly manner that I offer them - even if they don't sound that way. :-)

I think you may be confused as to how a Nested IF works.

I say that because of your use of the phrase "value if true portion works but the value if false returns FALSE"

In a Nested IF, there is no singular value_if_true portion and no singular value_if_false. They aren't single entities like you are making it sound. There needs to be a value_if_true and value_if_false argument for every logical_test.

The fact that you have a open parenthesis before your second if also leads me to the conclusion that you think the True and False "portions" (as you describe them) are isolated entities. It seems as if you feel that all of your Q11 tests is a single value_if_true argument for the logical_test IF(Q11>1. That is not the case. The only value_if_true argument for your opening IF is the COOP search. After that, all value_if_true and value_if_false arguments belong only to their own preceding IF.

The basic syntax for a Nested If look like this:

IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))

That final value_if_false2 is only returned if everything else before it is FALSE.

So, in your case, it doesn't appear that you need to check if Q11 is empty or not, you can simply allow all of the SEARCHes to "fail". If none of the SEARCHes return a value, then Q11 must be empty (or at least not match any of your search criteria.)

Therefore I think that this formula will work for you, although I am still a bit confused as to whether the "Subordinate" IF for F11 is in the proper location. That may cause some issues. Shouldn't that be the very first thing that you check?

=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*H11/10000,H11*R11)))))))

Let me know if that works.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#6
May 23, 2020 at 09:09:00
Thank you for the mini lesson, I never take your comments as harsh, you have taught me a lot throughout the years and I always have appreciated you :-)

Yes this works perfectly in every scenario, thank you so much.


Reply ↓  Report •

Ask Question