# Allocate an amount in one cell amongst or between several cells based on the value of each of the several cells

Here is the link that relates to my question:

Cell C13 is a Retirement Annuity contribution of my choosing.

Column E is taxable income at various marginal tax rates.

I can reduce my taxable income by the value in Cell C13.

Currently I am manually allocating the value in Cell C13 into Column H.

In this example I have manually inserted \$88999 into H21 and then calculated H20.
I would like this somehow to be dynamic.

The allocated \$89999 to H21 because I need to max out the amount in E21 before allocating to the next cell.

Changing Gross Income in Cell C8 dynamically populates Column E so this makes my problem all the more challenging.

In a nutshell, the only cells that should be editable are yellow. I would like Column H NOT to be yellow.

• Hi Tedinoz, thanks for your guidance. I hope this is he correct place to post my follow up question:) Can you perhaps take a look at my revisions to show me where I'm going wrong. Here's the link: – docs.google.com/spreadsheets/d/… Commented Mar 14, 2022 at 6:33

You want a formula to calculate the tax payable on any given value of taxable income.

Try this formula In Cell C11 (and then copy to Cell C14):

`=VLOOKUP(C11,\$A\$19:\$D\$25,4,TRUE)+round((C11-(VLOOKUP(C11,\$A\$19:\$D\$25,1,1)-1))*(VLOOKUP(C11,\$A\$19:\$E\$25,5,TRUE)),0)`

The elements of the formula are:

• `VLOOKUP(C11,\$A\$19:\$D\$25,4,TRUE)` - base tax for the applicable threshold
• `(C11-(VLOOKUP(C11,\$A\$19:\$D\$25,1,1)-1))` - the taxable income that applies to the final threshold
• `VLOOKUP(C11,\$A\$19:\$E\$25,5,TRUE)` - the marginal tax rate that applies to this threshold.

Changes required

To do this, you need to structure the tax rates along the following lines.

Tax Rates

The key to this table is that the calculation of the "Base Tax" for the previous threshold, and the marginal tax rate for each threshold.

Tax Calculations