Excel nested (if-and) formula

manz26 · Dec 20th 2013

Is this the right way to write the formula for the given expression?
If no, guide .
IF(ratio<20&loss>2000&balance<100)
print 25

else if(ratio>20 & ratio<40&loss<2000&loss>1000&balance<100
print 20

else if(ratio>20&loss<2000&loss>500&balance<100
print 20

else if(ratio>20&ratio<40&loss<2000&loss>500&balance<100
print 15

else if(ratio>20&loss<500&balance<100
print 15

=IF(AND(BA2<20,AU2<-2000,L2<100),"25",IF(AND(BA2>20,BA2<40,AU2>-2000,AU2<-1000,L2<100),"20",IF(AND(BA2>20,AU2>-2000,AU2<-500,L2<100),"20",IF(AND(BA2>20,BA2<40,AU2>-2000,AU2<-500,L2<100),"15",IF(AND(BA2>20,AU2>-500,L2<100),"15")))))

**Here the negative sign indicates the losses.
Thanks !

etaf · Dec 20th 2013

Re: Excel nested (if-and) formula

you have missed a false
=IF(AND(BA2<20,AU2<-2000,L2<100),"25",IF(AND(BA2>20,BA2<40,AU2>-2000,AU2<-1000,L2<100),"20",IF(AND(BA2>20,AU2>-2000,AU2<-500,L2<100),"20",IF(AND(BA2>20,BA2<40,AU2>-2000,AU2<-500,L2<100),"15",IF(AND(BA2>20,AU2>-500,L2<100),"15","does not meet criteria")))))

also
"25"
will put the value of 25 into the cell but as text
if you want the numeric vaolue of 25
remove the "" and just put
25
same for all the other values

otherwise a quite check looks ok

you could probably change this to one statement as an OR to combine the two rules
else if(ratio>20&ratio<40&loss<2000&loss>500&balance<100
print 15

else if(ratio>20&loss<500&balance<100
print 15

what happens - is it not working

maybe upload a dummy spreadsheet - with some examples in

Excel nested (if-and) formula

Participate now!

Share