Nested IF Statement Alternative

  • Hello,


    I have reached the 'nested if statement limit' & I agree maybe using "Vlookup" maybe a better option.


    Just not sure how to write the formula


    Thanks


    The eight scenarios are as follows:


    1) Takes the quote & results in a benefit at 60% of quote
    2) Takes the quote & results in a benefit at 80% of quote
    3) Takes the quote & results in a benefit of either 70% of the quote or the schedule amount (whichever is lower) x quantity
    4) Takes the quote & results in a benefit of either 75% of the quote or the schedule amount (whichever is lower) x quantity
    5) Takes the quote & results in a benefit of either 80% of the quote or the schedule amount (whichever is lower) x quantity
    6) Takes the quote & results in a benefit of either 75% of the quote or $98.00 per item (whichever is lower) x quantity
    7) Takes the quote & results in a benefit of either 75% of the quote or $350.00 per item (whichever is lower) x quantity
    8) Takes the schedule amount & results in a benefit x quantity x schedule fee

    Files

    • Test.xlsx

      (21.56 kB, downloaded 72 times, last: )
  • Re: Nested IF Statement Alternative


    Please explain how $660 becomes $733 in your attached example.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Nested IF Statement Alternative


    aussiformula,


    I hope you can work with this. I may have not put the formulas exactly as you want, but hopefully it can point you in the right direction. Here is the formula I used. I also attached the your sheet that I modified.

    Code
    1. =IF(C4="","",VLOOKUP(B4,$H$3:$J$8,3)*IF(VLOOKUP(B4,$H$3:$K$8,4)=1,C4,IF(VLOOKUP(B4,$H$3:$K$8,4)=2,D4,0)))


    ozgrid.com/forum/core/index.php?attachment/68956/

    Files

    • Test.xlsx

      (22.34 kB, downloaded 61 times, last: )
  • Re: Nested IF Statement Alternative


    Crush drinker - it's not my query.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Nested IF Statement Alternative


    Hello Crush Drinker,


    Thank you for getting back to me.


    The formula is great but I am unsure how to edit it for a few other scenarios


    Are you able to assist/update for me?


    Thanks

    Files

    • Test.xlsx

      (22.35 kB, downloaded 68 times, last: )
  • Re: Nested IF Statement Alternative


    I believe I got everything you asked for except

    Quote

    Another rule would be is there are amounts in J22-J26 then another rule needs to validate against person code G-K same as A-F (i.e A & G, B & H, C & I & D & J, E & K (as there are two seaprate limit(s) to be validated . If there is no amounts in J22-J26, then this rule would be ignored

    The reason I didn't get that is you never mentioned where or how to use it. If you can explain that I might be able to finish it for you. I also added a code into cell C3 to autofill that based on the contents of B3. that code is

    Code
    1. =IF(AND(B3>=11,B3<=17),$O$12,IF(AND(B3>=611,B3<=690),$O$13,IF(AND(B3>=800,B3<=850),$O$14,IF(AND(B3>=300,B3<=350),$O$15,""))))


    So far the equation I have for the total benefit is

    Code
    1. =IF(AND($I$19<>0,$J$19>0),"No family limit & no benefit paid",IF(G3="","",IF(F3<3,G3*IF(F3=1,0.6,0.8),IF(F3=7,H3*A3,IF(H3>G3,G3*A3*LEFT(VLOOKUP(F3,$L$5:N8,2),2)/100,H3*A3*LEFT(VLOOKUP(F3,$L$5:N8,2),2)/100)))))


    Attached is the worksheet that I have so far.ozgrid.com/forum/core/index.php?attachment/68983/

    Files

    • Test.xlsx

      (24.96 kB, downloaded 51 times, last: )
  • Re: Nested IF Statement Alternative


    Hello Crush Drinker,


    Thanks again for getting back to me.


    Okay I will explain J22-J26.


    Some of the benefits payable have three (3) possible totals to check. (that have to have a balance left to provide any benefit)


    For eg.


    If a family limit exists (if there is an amount in the family balance above 'zero'). then the individual limit has has to be verified against this family limit, no benefits can be payable unless the family limit is more or equal to the individual limit(s), based on the letter attached to the particular individual So, if there is no family balance then I need a rule that ignores this check/validation entirely


    If regards to the other limit(s) J22-J26 this needs to be based on a a range of item(s), 800-850 which comes under the category of "Orthodontics'. Orthodontics has an individual lifetime limit (per person), which (if applicable) has to be (again based on the individual limit (applicable letter G-K) & possibly the family limit.


    So the sum up an individual (if claiming for an orthodontic item in the item range), would have to have remaining lifetime limit, family limit (if family limit has a balance to check (via code "f" if entered manually during the data entry), & individual limit also.


    If the item range is not in the Orthodontics category, the lifetime limit & validation would be ignored as would not be applicable


    Only the family & individual limits would be checked/validated being letter F & letter A-E


    I hope this explains it


    Thank you


    Thanks

  • Re: Nested IF Statement Alternative


    Hello Crush Drinker,


    Just so you know I didn't know anything about excel I have created some formulas trying to do what I want them too, but I think you may have a better way to create the desired result


    Thanks again

    Files

    • Sample.xls

      (54.78 kB, downloaded 66 times, last: )