Forum Discussion
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?
=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!")))
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 )
2 Replies
- mtarlerSilver Contributor
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 )