Excel if statement with multiple conditions or Macros

[AdKu]

I'm trying to improve a prospect check spreadsheet by adding DB/DC Gateway test check. But my if statement multiple conditions is not working for me. please help.

If the plan has only HCEs or NHCEs - "NO REQUIRED GT" as in the first if clause

If the plan is only DC then GT can be satisfied if all eligible ee receive at least 5% as in the 2nd if clause

Otherwise, it will perform the GT check for DB/DC combo plan (given in the below table) as in the last five if clauses.

HCE Gateway limit 1/3 to 5% 15-25% 6% 25-30% 7% 30-35% 7.50% >35%

=IF(OR(A1=0,B1=0,C1=0),"NO REQUIRED GT",

IF(AND('Table'!A2=2,U22>=0.05),"GT PASSES",

IF(AND(U22>=0.075,'Table'!A2=1),"GT PASSES",

IF(AND(T21>30,T21<=35,U22>=0.07, 'Table'!A2=1),"GT PASSES",

IF(AND(T21>25,T21<=30,U22>=0.06, 'Table'!A2=1),"GT PASSES",

IF(AND(T21>15,T21<=25,U22>=0.05, 'Table'!A2=1),"GT PASSES",

IF(AND(T21<15,U22>=T21/3),"GT PASSES",

"GT FAILED")))))))

[ETA Consulting LLC]

Sometimes, you can run out of space for conditionals (e.g. "If-then") in the single command. You may want to create a separate space of each item "GT PASSES". Then, you can use a single "OR" command to say if either of these are true, then produce "GT PASSES".

Does that make sense. It's merely breaking the command apart to analyze different items instead of loading the "IF-THEN" conditionals into a single command.

Good Luck!

[Mike Preston]

A few things stand out:

1:All of your U22 tests are compared against percentages except the last one. To be consistent the last one should be U22>T21/3/100.

2:For clarity of operation order I would change the above to U22>=((T21/3)/100)

3:For ease in debugging I would change the 6 "GT PASSES" to unique strings. Something like "GT PASSES-1", "GT PASSES-2", etc. so you can see at a glance which test is controlling the output

4:In testing T21 you don't test for T21 exactly equal to 15 when A2=1. I think the last should be IF(AND(T21<=15,U22>=((T21/3)/100))

[My 2 cents]

I don't recall the actual limits, but I think that Excel can only handle 6 or 7 levels of nesting, and there may be a 255 character limit on a single cell's coding as well.

I prefer not to contemplate what my own limits may be concerning the complexity of a formula that I can follow, but I do know that Excel has limits.

[david rigby]

Well, since we are discussing limits...

An infinite number of mathematicians walk into a bar. The first orders a beer, the next orders half a beer, the next orders a quarter of a beer, and so on. … After the seventh order, the bartender pours two beers and says, “You fellas ought to know your limits.”

[Tom Poje]

ah, not quite the same as what I heard

A Englishman, a Scotsman, and an Irishman walk into a pub. They proceed to each buy a pint of Guinness.Just as they were about to enjoy their creamy beverage three flies landed in each of their pints, and were stuck in the thick head.

The Englishman pushed his beer away from him in disgust.

The Scotsman fished the offending fly out of his beer and continued drinking it as if nothing had happened.

The Irishman too, picked the fly out of his drink, held it out over the beer and then started yelling, "SPIT IT OUT, SPIT IT OUT YOU #$%&*@!!!"

[My 2 cents]

Reminds me of a cartoon (Rhymes with Orange) I saw two or three years ago in a newspaper.

An expert posits that an infinite number of monkeys on an infinite number of typewriters with an infinite amount of time could create the complete works of Shakespeare. A monkey points out that an infinite number of monkeys over an infinite amount of time created Shakespeare himself via evolution, saying "So, duh, we've done it already." [The mathematician in me points out that, to be more accurate, it would have been a finite number of monkeys over a finite amount of time.]

This discussion thread seems to have drifted somewhat off subject, hasn't it?

[Mike Preston]

Not by me.

[david rigby]

An expert posits that an infinite number of monkeys on an infinite number of typewriters with an infinite amount of time could create the complete works of Shakespeare.

Thanks to the internet, we know this is not true.

[GMK]

In keeping with the walked-into-a-bar discussion and the Siberian cold wave we have up here reminds me that:

A pair of jumper cables walked into a bar.

The bartender said, "OK, I'll serve you, but don't start anything."

[My 2 cents]

An expert posits that an infinite number of monkeys on an infinite number of typewriters with an infinite amount of time could create the complete works of Shakespeare.

Thanks to the internet, we know this is not true.

Not sure I fully grasp your point. I think the point of the expert's comment is not that the only thing that all those monkeys would produce is Shakespeare. They would produce, eventually, every episode of Two Broke Girls and I Love Lucy, the entirety of Lord of the Rings AND Harry Potter, every single comment posted by a troll this year (both the unexpurgated version and the one edited to replace some of the more egregious words or phrases with typographical symbols), every term paper you or I ever wrote, etc. And let us not forget the United States Tax Code (both the 1954 version and the 1986 version).

An infinite number of monkeys, with an infinite number of typewrites and an infinite amount of time, is an awful lot getting typed. The tragedy, of course, is that when they do type Hamlet, it will be utterly without meaning to the monkeys (which makes me think, at least a little bit, about one or two of the term papers I wrote).

[My 2 cents]

Two things:

1. I accidentally said "typewrites" instead of "typewriters" in the second paragraph. Sorry. I imagine that eventually one of the monkeys will type that paragraph without the typo.

2. Those monkeys will also undoubtedly eventually reproduce the entirety of the original poster's Excel formula. If it can be done, they will also type a version that Excel will be able to handle. For that matter, they will eventually type the full source code for Excel.

[david rigby]

Not sure I fully grasp your point.

Not so subtle point that the internet already has an infinite number of monkeys.

[Mike Preston]

If you are not going to name the GT PASSES separately so that you know which gateway test was passed, you can lump all of them into a single OR which avoids bumping up against the nested IF limitation of 7 (note that your original expression had only 7 nested IF's so it shouldn't have been a problem):

=IF(OR(A1=0,B1=0,C1=0),"NO REQUIRED GT",IF(OR(AND(Table!A2=2,U22>=0.05),AND(U22>=0.075,Table!A2=1),AND(T21>30,T21<=35,U22>=0.07, Table!A2=1),AND(T21>25,T21<=30,U22>=0.06, Table!A2=1),AND(T21>15,T21<=25,U22>=0.05, Table!A2=1),AND(T21<15,U22>=((T21/3)/100))),"GT PASSES","GT FAILED"))

[Michael Devault]

I'm not sure which version of Excel is being used, but the Office 365 version contains the IFS function that checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition. The syntax is:

IFS(logical_test1,value_if_true1,[logical_test2,value_if_true2],[logical_test3,value_if_true3],...)

Logical_test1 is required, as is value_if_true1. You can nest up to 127 different conditions.

[MoJo]

An expert posits that an infinite number of monkeys on an infinite number of typewriters with an infinite amount of time could create the complete works of Shakespeare.

Thanks to the internet, we know this is not true.

Not sure I fully grasp your point. I think the point of the expert's comment is not that the only thing that all those monkeys would produce is Shakespeare. They would produce, eventually, every episode of Two Broke Girls and I Love Lucy, the entirety of Lord of the Rings AND Harry Potter, every single comment posted by a troll this year (both the unexpurgated version and the one edited to replace some of the more egregious words or phrases with typographical symbols), every term paper you or I ever wrote, etc. And let us not forget the United States Tax Code (both the 1954 version and the 1986 version).

Uh.... It'd only take one monkey with one typewriter to produce my term papers. In fact, it did....

[AdKu]

Thank you everyone.

Unfortunately, the test result shows GT PASSES regardless of the values in cell  T21  and the value in the corresponding cell U22.

    FYI: I used all your valuable recommendations, in particular Mike's generous suggestion.

 

Any additional help is much appreciated!

[Mike Preston]

Post what the statement looks like now.

[BG5150]

Two guys walk into a bar.  The third one ducks.

[AdKu]

Mike, I used the statement that you provided above (see below) 

=IF(OR(A1=0,B1=0,C1=0),"NO REQUIRED GT",

       IF(OR(AND(Table!A2=2,U22>=0.05),

                  AND(Table!A2=1,U22>=0.075),

                  AND(Table!A2=1, T21>30,T21<=35,U22>=0.07),

                   AND(Table!A2=1,T21>25,T21<=30,U22>=0.06),

                  AND(Table!A2=1,T21>15,T21<=25,U22>=0.05),

                  AND(Table!A2=1,T21<15,U22>=((T21/3)/100))),

                            "GT PASSES","GT FAILED"))

[Mike Preston]

Well, it works on my machine (although I didn't test every combination).  You'll have to break it out to multiple statements to debug it, I'm afraid.  Good luck.