Issue with formula giving inconsistent answers

Sherry Hamilton

I am trying to identify FAILS and PASSES the question "Within how many days is your earliest routine appt" (dropdown with selection of days to chose from). Many of the states have different requirements to determine pass and fail. Therefore, the formula is somewhat complex depending on state and the requirement.

Above is my formula. The answer under column [Within how many days is your earliest routine appt] shows 5 days. The formula states "If contract state is Utah, and is <=21, then Pass", but you can see that it is showing FAIL under the [Routine] column. What is weird is that many of the other answers under 21 state Pass. Below are all of the Passes, and Fails for Routine (all for Utah). There seems to be no rhyme or reason....and I am experiencing this for other states too. Any ideas as to why this might be happening?

Paul Newcome

There are a few things that we are going to want to change.

First... Since you are already specifying all of your "PASS" criteria, you do not have to keep using the IF/AND combos to specify the "FAIL" criteria. You can use the nested IFs to generate all of your "PASS" outputs but then use a single "FAIL" at the end of the formula.

=IF(AND(State@row = "WV", Number@row<= 21), "PASS", IF(AND(State@row = "VA", Number@row<= 18), "PASS", "FAIL"))

A second problem is that your formula uses quotes around the numbers. This means it is looking t=for that exact TEXT. Text cannot be greater than or less than other text, so you need to remove the quotes from the numbers ion the formula so it can evaluate actual numbers.

Next, Even after removing the quotes so that your formula is registering 23 as a number instead of a text string, your dropdown is outputting text values. So we need to account for that as well. If you do not want to change your dropdown options, then you will need to add to the formula to pull those first couple of characters and then convert them into numbers.

VALUE(RIGHT(Number@row, FIND(" ", Number@row) - 1)) <= 21

Finally... We can consolidate the formula a bit by wrapping all of the AND statements in one OR statement for the passes. Using my example formula above would look like this:

=IF(OR(AND(State@row = "WV", Number@row<= 21), AND(State@row = "VA", Number@row<= 18)), "PASS", "FAIL")

It may not seem like much in this example, but in your overall formula it will cut out approximately 45ish characters.

My personal suggestion if you do not want to change your dropdown options would be to put the VALUE formula in a separate column and reference that in your main formula.

Sherry Hamilton

Thank you Paul! Much appreciated!

Paul Newcome

Happy to help. 👍️

If you run into any issues with the consolidation of the original formula, feel free to copy/paste the formula directly from the sheet to here.