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

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

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