ERROR IFS expects all arguments after position 0

Question

I'm trying to reference a cell depending on a value, but when I use the following code, I get an #N/A error with the note IFS expects all arguments after position 0 to be in pairs.

=IFS(OR(MONTH(TODAY())=-1,MONTH(TODAY())=4),IFS(I10=true, "???? Completed", A10="NOT STARTED","⛔ Not Started", L10=3/9,"???? On Track",L10=2/9,"???? On Track",J19=1/9,"???? On Track",L10<2/9,"???? At Risk",),MONTH(TODAY())=5,IFS(I10=true, "???? Completed",A10="NOT STARTED","⛔ Not Started",L11=3/9,"???? On Track",L11=2/9,"???? On Track",L11<2/9,"???? At Risk"),MONTH(TODAY())=6,IFS(I10=true, "???? Completed",A10="NOT STARTED","⛔ Not Started",L12=3/9,"???? On Track",L12=2/9,"???? On Track",L12<2/9,"???? At Risk"))

doubleunary doubleunary · Accepted Answer · 2021-04-26T18:29:29

Your formula probably has an extra trailing comma in L10<2/9,"🟡 At Risk", or perhaps it is missing the default values from each ifs(). Try this:

=ifs(
  or(month(today()) = -1, month(today()) = 4),
    ifs( 
      I10 = true, "🟢 Completed", 
      A10 = "NOT STARTED", "⛔ Not Started", 
      L10 = 3/9, "🔵 On Track", 
      L10 = 2/9, "🔵 On Track", 
      J19 = 1/9, "🔵 On Track", 
      L10 < 2/9, "🟡 At Risk", 
      true, na() 
    ), 
  month(today()) = 5, 
    ifs( 
      I10 = true, "🟢 Completed", 
      A10 = "NOT STARTED", "⛔ Not Started", 
      L11 = 3/9, "🔵 On Track", 
      L11 = 2/9, "🔵 On Track", 
      L11 < 2/9, "🟡 At Risk", 
      true, na() 
    ), 
  month(today()) = 6, 
    ifs( 
      I10 = true, "🟢 Completed", 
      A10 = "NOT STARTED", "⛔ Not Started", 
      L12 = 3/9, "🔵 On Track", 
      L12 = 2/9, "🔵 On Track", 
      L12 < 2/9, "🟡 At Risk", 
      true, na() 
    ) 
)

I could not quite decipher why the first or() has a test for month(today()) = -1. I do not think that will ever evaluate to true.

ERROR IFS expects all arguments after position 0

1 Answers