Hi - Dave here.
Happy Friday!
What's the best way to calculate income tax brackets in Excel?
Well, the math is quite simple, but the implementation can be tricky, and there are many approaches.
However, I like this simple and elegant formula based on Excel's new dynamic array engine:
=LET(
income,I4,
upper,C5:C11,
lower,DROP(VSTACK(0,upper),-1),
IF(income<=lower,0,
IF(income>upper,upper-lower,income-lower))
)
You can see this approach in the worksheet below, where this single formula splits the income in cell I4 into seven different brackets.
[
Download the workbook and read the full explanation]
Compared to traditional formulas, the modern approach above has several key advantages: just one formula, only two cell references, no need to lock cell references, only the upper limits in C5:C11 are needed, and variable names that make the formula readable and efficient.
This is a great example of how Excel's new engine can drastically simplify a problem, but it does require Excel 365. For older versions of Excel, I've included traditional formulas that will work in any version of Excel. Check out the article, then download the worksheet and try it out yourself.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at one useful Excel formula. You can sign up on our home page.