Excel nested (if-and) formula

  • 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 !

  • 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

    ETAF

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!