google-sheetsgoogle-sheets-formulaspreadsheet

IFS formula for greater than equal to but less than


Trying to make an IFS statement produce the following results but can't work it out. The following rules need to apply

I have tried this unsuccessful

=ifs(and(C4>=10,C4<32,"PASS"),C4<10,"",C4>=32,"EXPERT)")

Solution

  • I'd use a regular nested IF formula:

    =IF(C4>32=,"Expert",IF(C4>=10,"Pass","")
    

    Results


    EDIT:

    Your brackets seems to be off, I get the same result with the IFS statement: IFS results

    =IFS(AND(C4>=10,C4<32),"PASS",C4<10,"",C4>=32,"EXPERT")
    

    However, I think you can make it a bit shorter even:

    =IFS(C4>=32;,"EXPERT",C4>=10,"PASS",C4<10,"")
    

    OR

    =IFERROR(IFS(C4>=32;,"EXPERT",C4>=10,"PASS"),"")
    

    This way you check first if C4 is >=32, so the 10 doesn't matter (yet), if it's not, you'll check if it's >=10, if not, the first one will check if it's below 10 (which is the only option left) and pass "", the second formula here will generate an error in the IFS part, and so giving you "".