How to create complex conditional formulas in online spreadsheet tools?

Other Questions Airtable
1

I’m working with an online spreadsheet tool that has its own formula system. I’m trying to set up a tricky conditional formula but I’m having trouble getting it right.

My spreadsheet has columns for Grade, Zipcode, and School. Each of these can have values of UE, PE, or EE. I want to create a new column called Eligibility that follows these rules:

  • If Grade is UE, Eligibility should be UE
  • If Grade is PE, check Zipcode and School:
    • If either is EE or PE, Eligibility should be PE
    • Otherwise, Eligibility should be UE
  • If Grade is EE, check Zipcode and School:
    • If either is EE, Eligibility should be EE
    • Otherwise, Eligibility should be UE

Here’s what I’ve tried so far, but it’s not working:

IF(GradeCol = "UE", "UE",
  IF(GradeCol = "PE",
    IF(OR(ZipCol = "EE", ZipCol = "PE", SchoolCol = "EE", SchoolCol = "PE"),
      "PE", "UE"),
    IF(GradeCol = "EE",
      IF(OR(ZipCol = "EE", SchoolCol = "EE"),
        "EE", "UE"),
      "")))

Can someone help me figure out what I’m doing wrong or suggest a better way to structure this formula?

2

Your approach is on the right track, but I’d suggest simplifying it a bit. Here’s a more concise formula that should achieve what you’re looking for:

IF(Grade=“UE”, “UE”,
IF(OR(Zip=“EE”, School=“EE”),
IF(Grade=“EE”, “EE”, “PE”),
“UE”))

This formula first checks if Grade is UE. If not, it then checks if either Zip or School is EE. If so, it assigns EE or PE based on the Grade. If neither Zip nor School is EE, it defaults to UE. This should cover all your conditions more efficiently.

Remember to adjust column references to match your specific spreadsheet layout.

3

have u considered using nested IF statements? might be easier to read. something like:

IF(Grade=‘UE’, ‘UE’,
IF(Grade=‘PE’,
IF(OR(Zip=‘EE’, Zip=‘PE’, School=‘EE’, School=‘PE’), ‘PE’, ‘UE’),
IF(Grade=‘EE’,
IF(OR(Zip=‘EE’, School=‘EE’), ‘EE’, ‘UE’),
‘UE’)))

this should work for ur conditions. lmk if u need more help!

4

I’ve been wrestling with similar complex conditional formulas in online spreadsheets, and I found that breaking it down into smaller steps can make it more manageable and easier to troubleshoot. Here’s an approach that might work for you:

First, create separate columns for each condition:

GradeCheck: IF(Grade=“UE”, “UE”, Grade)
ZipCheck: IF(OR(Zip=“EE”, Zip=“PE”), “Pass”, “Fail”)
SchoolCheck: IF(OR(School=“EE”, School=“PE”), “Pass”, “Fail”)

Then, combine these in your final Eligibility column:

IF(GradeCheck=“UE”, “UE”,
IF(AND(GradeCheck=“PE”, OR(ZipCheck=“Pass”, SchoolCheck=“Pass”)), “PE”,
IF(AND(GradeCheck=“EE”, OR(Zip=“EE”, School=“EE”)), “EE”, “UE”)))

This approach makes it easier to verify each step and adjust as needed. It’s been a lifesaver for me when dealing with complex logic in spreadsheets.