(SOLVED using VLOOKUP) Tiered/Nested IF clauses within one cell

Question

Imagine a tiered revenue sharing scheme like this:

Revenue up to 10000 get 100% of it.
Revenue up to 12000 get the above plus 80% of the amount above 10000.
Revenue up to 14000 get the above plus 60% of the amount above 12000.
Revenue up to 16000 get the above plus 40% of the amount above 14000.
Revenue over 16000 get the above plus 20% of the amount above 16000.

E.g. A revenue of 13000 will get you a share of 10000+0.82000+0.61000 = 12200.

I tried making a table (each threshold a column) and calculate the individual fractions using IF clauses and then add them all up. It is very cumbersome. I would like to use only two cells with the entire calculation done in one cell, hard-coded.

If at all possible, extra bonus points if I can have the threshold values (10000, 12000, etc) and fractions (100%, 80%, etc.) in separate cells as parameters for the calculation, maybe something like an array-function?

Thank you very much in advance!

Answer 1

Start your lookup table with a value of 0 and a rate of 100%. In this case, VLOOKUP() with the last parameter equal to 1 will correctly find the required row.

In order not to recalculate all the above rows for each of the values, calculate them in advance and place them in the table as an additional column.

For the first line it will be 0, and calculate all subsequent values using a formula like =C2+(A3-A2)*B2

For such a table, a not very complicated formula will return the correct result:

=(<revenue>-VLOOKUP(<revenue>;<lookup_table>;1;1))*VLOOKUP(<revenue>;<lookup_table>;2;1)+VLOOKUP(<revenue>;<lookup_table>;3;1)

The third parameter in the VLOOKUP() functions increases from left to right: 1 - the base amount, 2 - the interest rate, 3 - the calculated markup for reaching the previous levels.

For the data shown in the figure, the formula is used

=(E2-VLOOKUP(E2;$A$2:$C$7;1;1))*VLOOKUP(E2;$A$2:$C$7;2;1)+VLOOKUP(E2;$A$2:$C7;3;1)