Spreadsheet to calculate Scottish income tax

[DavidGee]

Hi, I'm trying to create a spreadsheet to calculate income tax payable for a given level of income. I know the tax bands (here), but I just can't work out what the formula should be to calculate how much of the total income would fall into each band, for any given level of income. Can anyone help with this, please? Thank you.

 

[Dave Patton]

The Rate Diff uses the same numbers in each of the examples. How you secure the numbers and which format of the formula is
up to you.

See post #2 create the Rate Diff column and use the result for your formula or
See post #7 c3 highlight F3:F7-F2:F6 copy the result and use in your formula or name it aR in Name Manager or
See post #9 aR={0.19;0.01;0.01;0.2;0.05}
Look at the numbers 19% 20% etc. and mentally calculate the required numbers .19 .01 etc. {0.19;0.01;0.01;0.2;0.05}

Name error My example uses a number (the largest possible for Excel); the number is named BigNum. =9.99999999999999E+307