Struggling with Complex Conditional Formulas in Spreadsheets

Other Questions Google Sheets
1

Hey everyone! I’m having a hard time with a tricky formula in my spreadsheet. I want it to say “YES” when:

  1. E2 isn’t empty and C2 has either “Ted”, “Nancy”, or “Jane”
  2. Or if C2 has “Steve” and E2 isn’t empty

For everything else, it should say “NO”.

I tried this:

=ifs(and(C2="Steve",E2<>""), (or(and(D2<>""), C2="Ted", C2="Nancy", C2="Jane"),"YES","NO"))

But I’m getting a Formula Parse error. Can anyone help me figure out what’s wrong? I’m not sure if I’m using IFS, AND, and OR correctly. Thanks!

2

Your formula structure was close, but needed some tweaking. Here’s a simplified version that should work:

=IF(OR(AND(NOT(ISBLANK(E2)),OR(C2=“Ted”,C2=“Nancy”,C2=“Jane”)),AND(C2=“Steve”,NOT(ISBLANK(E2)))),“YES”,“NO”)

This checks both conditions you specified and returns ‘YES’ if either is true, ‘NO’ otherwise. The key is using OR to combine your main conditions, then AND for the sub-conditions within each.

A tip for future complex formulas: build them step by step, testing each part as you go. It makes troubleshooting much easier. Also, consider using named ranges for clarity in larger spreadsheets.

3

hey dave, i think i got a solution for ya. try this formula:

=IF(OR(AND(E2<>“”,OR(C2={“Ted”,“Nancy”,“Jane”})),AND(C2=“Steve”,E2<>“”)),“YES”,“NO”)

it checks for both conditions u mentioned. the OR function combines em, and the {} lets u list names without repeating C2= each time. lemme know if it works!

4

I’ve run into similar issues with complex conditional formulas before. The problem with your current formula is the structure and nesting of the logical functions. Here’s a corrected version that should work for your requirements:

=IF(OR(AND(NOT(ISBLANK(E2)),OR(C2=“Ted”,C2=“Nancy”,C2=“Jane”)),AND(C2=“Steve”,NOT(ISBLANK(E2)))),“YES”,“NO”)

This formula first checks for your two main conditions using OR, then nests the sub-conditions within each AND statement. The NOT(ISBLANK(E2)) is used to check if E2 isn’t empty.

Remember, when working with complex conditionals, it often helps to break down the logic into smaller parts and build it up gradually. This way, you can test each component separately before combining them into the final formula. Hope this helps solve your spreadsheet puzzle!