Forum Discussion

Asmaa950's avatar
Asmaa950
Copper Contributor
Jun 06, 2020
Solved

Multiple conditional if

Hello All,

 

this equation does not work and i do not know where is the problem

 

=IF(V11<15000/12,0,IF(V11<30000/12,"(V10-(15000/12))*2.5%",IF(V11<45000/12,"((V10-(30000/12))*10%)+(375/12)",IF(V11<60000/12,((V11-(45000/12))*15%)+(1875/12),IF(V11<200000/12,((V11-(60000/12))*20%)+(4125/12),IF(V11<400000/12,((V11-(200000/12))*22.5%)+(32125/12),IF(V11<600000/12,"((V10-(400000/12))*25%)+(77125/12)",IF(V11<700000/12,"((V10-(400000/12))*25%)+(77500/12)",IF(V11<800000/12,((V11-(400000/12))*25%)+(79750/12),IF(V11<900000/12,((V11-(400000/12))*25%)+(82000/12),IF(V11<1000000/12,((V11-(400000/12))*25%)+(85000/12),IF(V11>1000000/12,((V11-(400000/12))*25%)+(90000/12),0))))))))))))

 

if it will not work, is there another formula can help with this multiple conditions?

 

Thank You.

 

 

Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 06, 2020

    Asmaa950 

    If that's the only error please remove apostrophes here

    =IF(V11<15000/12,0,
    IF(V11<30000/12, "(V10-(15000/12))*2.5%",
    IF(V11<45000/12, "((V10-(30000/12))*10%)+(375/12)",
    IF(V11<60000/12, ((V11-(45000/12))*15%)+(1875/12),
    IF(V11<200000/12, ((V11-(60000/12))*20%)+(4125/12),
    IF(V11<400000/12, ((V11-(200000/12))*22.5%)+(32125/12),
    IF(V11<600000/12, "((V10-(400000/12))*25%)+(77125/12)",
    IF(V11<700000/12, "((V10-(400000/12))*25%)+(77500/12)",
    IF(V11<800000/12, ((V11-(400000/12))*25%)+(79750/12),
    IF(V11<900000/12, ((V11-(400000/12))*25%)+(82000/12),
    IF(V11<1000000/12,((V11-(400000/12))*25%)+(85000/12),
    IF(V11>1000000/12,((V11-(400000/12))*25%)+(90000/12),0
    ))))))))))))

10 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 06, 2020

    Asmaa950 

    In general formula works. Most probably it returns not what you expected

    =IF(V11<15000/12,0,
 IF(V11<30000/12,  "(V10-(15000/12))*2.5%",
 IF(V11<45000/12,  "((V10-(30000/12))*10%)+(375/12)",
 IF(V11<60000/12,  ((V11-(45000/12))*15%)+(1875/12),
 IF(V11<200000/12, ((V11-(60000/12))*20%)+(4125/12),
 IF(V11<400000/12, ((V11-(200000/12))*22.5%)+(32125/12),
 IF(V11<600000/12, "((V10-(400000/12))*25%)+(77125/12)",
 IF(V11<700000/12, "((V10-(400000/12))*25%)+(77500/12)",
 IF(V11<800000/12, ((V11-(400000/12))*25%)+(79750/12),
 IF(V11<900000/12, ((V11-(400000/12))*25%)+(82000/12),
 IF(V11<1000000/12,((V11-(400000/12))*25%)+(85000/12),
 IF(V11>1000000/12,((V11-(400000/12))*25%)+(90000/12),0
 ))))))))))))

    For example, some conditions return text instead of calculation result. But that's only  you know what it shall return. In addition, most probably helper range will be better than constants hardcoding.

      • mathetes's avatar
        mathetes
        Silver Contributor
        Jun 06, 2020

        Asmaa950 

         

        What SergeiBaklan is telling you here, and what I tried to tell you on your other post with this same nested IF question is this:

         

        When you say a formula "doesn't work" you may be very clear in your own mind what it means. BUT it doesn't say what DOES happen? Doesn't work is saying what does not happen, not what does.

        • So is it an error message you get? If so, what's the error message?
        • Does the formula just not give an answer at all? Does it hang there without any result?
        • Does the formula give you an answer but one you don't expect? 

         

        WHAT DOES HAPPEN?

         

        SergeiBaklan  has been far nicer than I in that he parsed the formula to see if the syntax was acceptable, and it appeared to be. Excel probably wouldn't have let you finish writing it had it not been.

         

        Anyway, if you really want an answer, you're going to have to answer our questions for you.

Resources