Forum Discussion

Dhas160's avatar
Dhas160
Copper Contributor
Jul 27, 2022
Solved

I need Help with an Excel Formula

I am weak when it comes to writing an excel formula.  The formula is below and I get a #VALUE! error.   What additional information do you need or what do you see based off the formula below?    ...
  • mtarler's avatar
    Jul 27, 2022

    Dhas160  you have 2 formulas in that cell.  Here I space it out to show:

    =IF(AND(I3="X",N3+O3+P3+Q3>0,R3+S3<1),
        "SELL OFF FLOOR",
         ""),
     IF(R3+S3<3,
        "",
        IF(AND(R3+S3>3,R3+S3<6),
           "LOW STOCK",
           IF(R3+S3>5,"FAST DELIVERY!")
           )
         )

    notice how the first IF statement ends on line 3 but then there is a comma and another IF statement starts on line 4.  I THINK you mean for that next IF statement to be the FALSE case  of the previous (i.e. cascading IF statements) like this:

    =IF(AND(I3="X",N3+O3+P3+Q3>0,R3+S3<1),
        "SELL OFF FLOOR",
        IF(R3+S3<3,
           "",
           IF(AND(R3+S3>3,R3+S3<6),
              "LOW STOCK",
              IF(R3+S3>5,"FAST DELIVERY!")
              )
           )
        )

    alternatively you could use IFS() statement instead:

    =IFS(AND(I3="X",N3+O3+P3+Q3>0,R3+S3<1), "SELL OFF FLOOR",
         R3+S3<3,"",
         AND(R3+S3>3,R3+S3<6), "LOW STOCK",
         R3+S3>5,"FAST DELIVERY!",
         TRUE, "none of the above"
         )

    the last thing I will point out is that I would add () around some of those conditionals because it is confusing if:

    N3+O3+P3+Q3>0

    means:

    (N3+O3+P3+Q3) > 0

    or 

    N3+O3+P3+( Q3 > 0 )

     

Resources