QUESTION

Rowka asked “how do I make this formula smaller?”

=IF(AND(E11>=K7,E11<=N7,F11>=K7,F11<=N7,G11>=K7,G11<=N7),”PASS”,”FAIL”)

Translation of the Formula: If tests 1 thru 3 are within acceptable ranges, then say “PASS” otherwise, say “FAIL”
Keep reading. If you don’t understand the formula, it’s explained later

I answered and then Evan jumped into the mix and got everything all complicated.

Yes, Wonder Woman. He did.

So, this question will have 2 answers.

• Answer 1: Leave it alone. It works. It’s easy to troubleshoot. Making it smaller would be complicated.
• Answer 2: A more solid version that doesn’t make the formula smaller but adds critical elements when accuracy is a big deal, and a cluttered spreadsheet is annoying.

I appreciate that Evan jumped in because he pointed to an area of development that we always have to balance: our resources and the consequences if something goes wrong. Before we get into it. Let’s dissect the formula

We have an IF statement with an AND nested inside.

Looking purely at the IF portion, we’re asking IF 3 conditions are met (as designated by the blue orange and green) then the testing PASSES otherwise, it FAILS.

In the AND section, there’s a pattern and we see that K7 and N7 are in each section. E11, F11 and G11 are the unique pieces and separate the AND statement into 3 conditions. I’m going skip the details and …

The statement says: testing PASSES if E11, F11 and G11 are all between the numbers represented by K7 and N7

Example:

K7 = 200 and N7 = 260

E11 = 203 , F11 = 241 , G11  = 206 PASS

E11 = 251 , F11 = 209 , G11  = 198 FAIL

ANSWER 1

I suggested that Rowka leave the formula alone because it works. There are ways to make the formula smaller but, as it stands, it’s easy for someone else to look at and troubleshoot. One way of making the formula smaller would use the MEDIAN function and would require rearrangement of the data. More trouble than it’s worth and rearrangement might not even be an option.

Also, the formula is pretty small already.

The points to get from this:

• In writing formulas, think about the ease of someone else being able to decipher. Before we write VBA code or advanced tricks, just think about collaborators who may not have the skill.
• Sometimes, a clunky solution is good enough.

ANSWER 2

Enter Mr. Evan!

Evan pointed out weaknesses in the formula. In certain settings, Answer 1 would not fly because the formula will generate a PASS or FAIL based on impartial data.

Notice that Entry #1 is empty and the result is FAIL. This is absurd but the formula is right because “nothing” is less than 200. Entry #2 is also absurd because the result is PASS and there’s only one reading.

Let’s re-write the formula and add a trigger.

• Let’s use MAX and MIN instead of writing the individual “=>” jazz.
• Our trigger will be: If there are fewer than 3 readings, then don’t calculate anything.
  • COUNTA is the function that counts non-empty cells.

• Orange: If there aren’t 3 readings, don’t grade the entry.
• Blue: Look at the range of the 3 readings, find the maximum and see if it’s above the maximum acceptable.
  • If true: FAIL
• Green: Look at the range of the 3 readings, find the minimum and see if it’s below the minimum acceptable.
  • If true: FAIL

Entries 1, 2 and 5 are accurate. Entries 3 and 4 aren’t graded at all because there are less than 3 readings.

SUMMARY

Building formulas can go beyond the math. We have to think hard about the results, the format of the result you want, errors, and absurd results. Looking back to compare Answer 1 and Answer 2; Answer 1 is fine for a quick & dirty calculation and for someone who asks “can I make this smaller.” Answer 2 is more like I’d write for a client who is calculating something like chemical levels, credit hours, volunteer hours, sales commissions … areas where absurd results would be harmful.

Rowka, thanks for the question, sorry that it turned into all of this. LOL! Evan, thanks for jumping in.

Happy New Year! Build triggers into your formulas & keep your data clean in 2013!