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
Takes the schedule amount & results in a benefit x quantity x schedule fee

Files

• Re: Nested IF Statement Alternative

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.

• Re: Nested IF Statement Alternative

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
``=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)))``

Files

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

• Re: Nested IF Statement Alternative

I apologize. I have corrected my mistake.

• 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

• 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
``=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
``=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.forum.ozgrid.com/index.php?attachment/68983/

Files

• 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

Participate now!

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