Multiple IF/Thens that impact a single cell.

JocelynF

I've tried 20 different formulas and none are doing what I need. I'm starting to wonder if this is even possible.

Okay, I am trying to make the Two Crucial Time Status for Alice ISD act as follows: If any of the Two Crucial Times monitoring items with stop signs are checked under Items Being Assessed, and the Alice ISD column is Off Track for that item, then the [Alice ISD]2 status would be Off Track. So if [Items Being Assessed]11 is checked and [Alice ISD]11 is Off Track, then the Two Crucial Times status for Alice ISD is Off Track. Same thing for High Risk. But if [Items Being Assessed]11 is checked, and [Alice ISD]11 is Off Track, AND Items Being Assessed]12 is checked, and [Alice ISD]12 is High Risk, the [Alice ISD]2 would be High Risk.

Then, for added fun, if none of the stop sign monitoring items in two crucial times are off track or high risk, I need the value of the Alice ISD Two Crucial Times Status to be On Track if Alice ISD Points Possible is greater than or equal to 80%, Off Track if greater than or equal 60%, otherwise High Risk.

Here is the best I've been able to come up with so far: =IF(OR(AND([Items Being Assessed]11 = true, [Alice ISD]11 = "Off Track"), AND([Items Being Assessed]11 = true, [Alice ISD]11 = "High Risk"), AND([Items Being Assessed]12 = true, [Alice ISD]12 = "Off Track"), AND([Items Being Assessed]12 = true, [Alice ISD]12 = "High Risk")), IF(OR([Alice ISD]11 = "Off Track", [Alice ISD]12 = "Off Track"), "Off Track", "High Risk"), IF([Alice ISD Points]@row > 0.8, "On Track", IF([Alice ISD Points]@row >= 0.6, "Off Track", "High Risk")))

But it doesn't work all the time. But if I try to do the same thing for rows 13-16, it fritzes. There is something missing.

If this madness makes sense to you and you have any ideas, I appreciate it!

Kelly Moore

Hey @JocelynF

Try this: (This formula will work for all of your High Level rows without any other editing, assuming they follow the same criteria as this one). I also put in the 'Not Assessed' selection. If you don't want that, delete the highlighted text from your formula.

=IF([5 to Drive Category]@row = "High Level", IF(OR(COUNTIFS([5 to Drive Category]:[5 to Drive Category], <>"High Level", [5 to Drive Category]:[5 to Drive Category], [Monitoring Item]@row, [Items Being Assessed]:[Items Being Assessed], 1, [Monitoring Item]:[Monitoring Item], CONTAINS(UNICHAR(128721), @cell), [Alice ISD]:[Alice ISD], "High Risk") > 0, [Alice ISD Points Earned]@row < 0.6), "High Risk", IF(OR(COUNTIFS([5 to Drive Category]:[5 to Drive Category], <>"High Level", [5 to Drive Category]:[5 to Drive Category], [Monitoring Item]@row, [Items Being Assessed]:[Items Being Assessed], 1, [Monitoring Item]:[Monitoring Item], CONTAINS(UNICHAR(128721), @cell), [Alice ISD]:[Alice ISD], "Off Track") > 0, [Alice ISD Points Earned]@row < 0.8), "Off Track", IF(OR(COUNTIFS([5 to Drive Category]:[5 to Drive Category], <>"High Level", [5 to Drive Category]:[5 to Drive Category], [Monitoring Item]@row, [Items Being Assessed]:[Items Being Assessed], 1, [Monitoring Item]:[Monitoring Item], CONTAINS(UNICHAR(128721), @cell), [Alice ISD]:[Alice ISD], "On Track") > 0, [Alice ISD Points Earned]@row >= 0.8), "On Track", IF(COUNTIFS([5 to Drive Category]:[5 to Drive Category], <>"High Level", [5 to Drive Category]:[5 to Drive Category], [Monitoring Item]@row, [Items Being Assessed]:[Items Being Assessed], 0, [Alice ISD]:[Alice ISD], "Not Assessed") > 0, "Not Assessed")))))

The UniChar reference is code speak for your stopsign. The formula is only counting the rows that have the respective [5 to Drive Category] reference, a stopsign in the [Monitoring Item], and a checkbox [Items Being Assessed].

Will this work for you? Please shout out if anything needs tweaking.

Kelly