Speed Up Your Work With These Excel Formulas

Excel has been the most trusted software of accountants ever since it was released in 1987. Despite competition from Google Sheets, it continues to enjoy the title of ‘world’s most popular productivity tool’. Even though it is widely used, there are a lot of its functions that are not fully explored. To use it more efficiently as a tool for budgeting, planning and forecasting operations, you can master some formulas that will save you time and effort. Let’s take a look at this using two functions: IF and VLOOKUP.

Conditional IF Formula

"IF" (=IF) function of MS Excel allows you to test whether a condition is true or not, and returns different results accordingly. The syntax of this handy little function is as follows:

=IF (condition to test, what to do if it’s true, what to do if it’s false)

For example, suppose you are an accountant trying to work the US income tax for 2018 for a series of (unmarried) clients. Finding out the tax each client would have to pay becomes quicker and easier with this tool. Here, let’s assume people get taxed only for an income above $9,525 at 12%.

You can use this to calculate each client’s income tax liability:
“=IF(B2>=9525,B2*12%,0)”.
For example, if the income in cell B2 is greater than or equal to $9,525, charge income tax at 12 percent of a person’s income; otherwise, charge $0.

But as any professional dealing with tax and accounting would know, things are rarely this straightforward. When there’s more than one possibility, nesting IF functions is quite handy.

Suppose now that the income tax regime is a bit more complicated: incomes above $9,525 attract tax at 12 percent, but properties above $38,700 attract tax at a higher rate of 22 percent. You could cope with this by nesting one IF function within another:

The formula in cell C2, which has been copied down to the other cells, is:

=IF(B2>=38700,22%,IF(B2>=9525,12%,0%)) * B2

What this means:

• If someone’s income is more than $38,700, take the income tax rate as 22 percent;
• Otherwise, if the house price is more than $9,525 (but less than $38,700, since we’ve already eliminated this), take the income tax rate as 12%;
• Otherwise, take the income tax rate as 0 percent.

For a nested IF function like this, you have to take the values in order: either start with the highest value and work your way down to the lowest, or work from the lowest value to the highest. As you can see, it will become more complicated as you add more values and conditions. Thus, it is recommended to go for VLOOKUP formula.

VLOOKUP Formula

Suppose now that the income tax regime is more complicated and is charged as follows  That is:

• Incomes up to $9,525 are exempt from income tax.
• Incomes between $9,525 and $38,700 are charged tax at 12 percent.

Nested IF Function can solve this but it would become complicated if the thresholds or tax rate values are changed. Instead, using the VLOOKUP function will make things smoother in this context. Let’s see how:

Using this formula, you could calculate the income tax payable on each of your possible purchases as follows:

This looks up the income of the client ($45,000, as held in cell B11) against the lookup table held in A2:B8, and returns the value of the second column in this table (in this instance, 22 percent), which it then multiplies by the client’s income.

There are a few important aspects to notice:

1. It’s vital that the income thresholds we’re looking up against are in the first column of the lookup table and are in ascending order.
2. You must make the reference to the lookup table absolute (that’s what the $ signs are for), to make sure that when you copy the formula down it always refers to the fixed table A2:B8.