John Walkenbach created Excel Charts for Excel 2007 and Excel 2003.
Bill Jelen created Pivot Table Data Crunching for Excel 2013, Excel 2010.
Apr 01
John Walkenbach created Excel Charts for Excel 2007 and Excel 2003.
Bill Jelen created Pivot Table Data Crunching for Excel 2013, Excel 2010.
Aug 31
By David H. Ringstrom, CPA
(If you didn't already do so in Part 1, click here to download the accompanying Income Tax Calculator spreadsheet.)
Last month I explained how to use the VLOOKUP function to cross-reference tax rates from a tax rate table. I then extended the functionality by creating 4 different tables — Single, Married-Joint, Married-Single, and Head of Household, as shown in Figure 1. After assigning a range name to each table, I used Microsoft Office Excel’s Data Validation feature to create an in-cell drop-down list comprised of these range names. Finally, I modified my VLOOKUP functions to use Excel’s INDIRECT function. INDIRECT replaced the static look-up range originally specified in the VLOOKUP formula. If I choose Married-Joint from the list, the VLOOKUP function will calculate the tax due based on that table. I can choose Married-Single from the list to determine the additional tax due under that filing status. At this point I have an efficient calculator for determining the amount due based on a taxable income input, but I’d rather not have 4 separate tables. This month I’ll dig deeper, and show you how to create a single table of tax rates, as shown in Figure 2. The final result will involve two rather complex formulas, but we’ll build them a step at a time.
Figure 1: The VLOOKUP version of the tax calculator relies on four separate rate tables.
Figure 2: The new tax calculator will rely on a single rate table.
Understand MATCH
The MATCH function is akin to VLOOKUP — you specify what to look for, where to look, and the type of match that you’d like. When the MATCH function finds the criteria you specify, it returns the position number within the list —otherwise it returns #N/A. The position number can then be used within an INDEX function to return a specific value, much like VLOOKUP or HLOOKUP. Although VLOOKUP is a useful look-up function, your look-up criteria must always be the first column of your data table. Instead, the INDEX/MATCH combination enables you to create a look-up based on any column within the table.
The MATCH function has three arguments:
Since MATCH only returns the position number within the list, we’ll then use the INDEX function to return actual amounts from the table.
Comprehend INDEX
I only have space in this article to describe the reference capability of the INDEX function, but Excel’s Help feature discusses the array and multiple area capabilities of INDEX. The reference capability of INDEX has three arguments:
Appreciate OFFSET
Not many Excel users know about the OFFSET function. In essence, it’s a means for shifting a range a certain number of columns and/or rows away from a starting point. In the case of our tax calculator, we’ll use OFFSET to have the MATCH function refer to the proper columns within our tax rate table. The OFFSET function has five arguments:
Create the Tax Calculator
Now that we have the function basics out of the way, let’s build a tax table that refers to a single table instead of four different tables. Enter these values in cells A2 through A8 of a blank worksheet:
|
Enter these values in cells B2 through C8 of the worksheet:
B2: |
Single |
C2: |
Base Tax |
B3: |
0.00 |
C3: |
0.00 |
B4: |
8,026.00 |
C4: |
802.50 |
B5: |
32,551.00 |
C5: |
4,481.25 |
B6: |
78,851.00 |
C6: |
16,056.25 |
B7: |
164,551.00 |
C7: |
40,052.25 |
B8: |
357,701.00 |
C8: |
103,791.75 |
Enter these values in cells D2 through E8 of the worksheet:
D2: |
Married-Joint |
E2: |
Base Tax |
D3: |
0.00 |
E3: |
0.00 |
D4: |
16,051.00 |
E4: |
802.50 |
D5: |
65,101.00 |
E5: |
4,481.25 |
D6: |
131,451.00 |
E6: |
16,056.25 |
D7: |
200,301.00 |
E7: |
40,052.25 |
D8: |
357,701.00 |
E8: |
103,791.75 |
Enter these values in cells F2 through G8 of the worksheet:
F2: |
Married-Single |
G2: |
Base Tax |
F3: |
0.00 |
G3: |
0.00 |
F4: |
8,026.00 |
G4: |
802.50 |
F5: |
32,551.00 |
G5: |
4,481.25 |
F6: |
65,726.00 |
G6: |
12,775.00 |
F7: |
100,151.00 |
G7: |
22,414.00 |
F8: |
178,851.00 |
G8: |
48,385.00 |
Enter these values in cells H2 through I8 of the worksheet:
H2: |
Head of Household |
I2: |
Base Tax |
H3: |
0.00 |
I3: |
0.00 |
H4: |
11,451.00 |
I4: |
1,145.00 |
H5: |
43,651.00 |
I5: |
5,975.00 |
H6: |
112,651.00 |
I6: |
23,225.00 |
H7: |
182,401.00 |
I7: |
42,755.00 |
H8: |
357,701.00 |
I8: |
100,605.00 |
Add these headings to the spreadsheet:
B10: |
Taxable Income |
C10: |
Tax Rate |
D10: |
Tax Due |
E10: |
Filing Status |
Enter 125,000 in cell B11, and then use Data Validation to create an in-cell drop-down list in cell E10:
Single,Married-Joint,Married-Single,Head Of Household
Figure 3: Enter the filing statuses in the Source field of the Data Validation window.
Caution: Be sure that the contents of the Source field exactly match the values that you entered in cells B2, D2, F2, and H2.
Figure 4: Data validation can provide an in-cell drop-down list.
We’ll now enter the formula to determine the tax rate. Enter this formula in cell C11:
=MATCH(B11,B3:B8,1)
Based on an input of 125,000 in cell B11, the formula should return the number 4. We’ve instructed MATCH to look at the taxable income for the Single filing status, and asked it to find the closest income bracket for $125,000. Next, we’ll add the INDEX function, so that we can get the actual tax rate. Modify the formula in cell C11 to be as follows:
=INDEX(A3:A8,MATCH(B11,B3:B8,1))
At this point the formula should return 28%. However, we’re referencing a static range of B3:B8 for our income brackets, and instead we want the formula to shift automatically based on our choice in cell E11. To do so, we’ll employ the OFFSET function. As shown in Figure 5, modify the formula in cell C11 to match this:
=INDEX(A3:A8,MATCH(B11,OFFSET(A3:A8,0,MATCH(E11,A2:I2,0)-1),1))
Figure 5: This formula determines the tax rate based on the taxable income amount and filing status.
Although this may look intimidating, we basically replaced the B3:B8 portion of the formula with this component:
OFFSET(A3:A8,0,MATCH(E11,A2:I2,0)-1)
Our OFFSET function contains these arguments:
At this point the formula should return 28%. This number should change to 25% if you choose Married-Joint in cell E11, 33% for Married-Joint, or remain at 28% if you choose Head of Household.
We’re now ready to create the final formula in our table, which will perform the actual tax calculation. Enter this formula in cell D11:
=INDEX(A2:I8,MATCH(C11,A3:A8,0),MATCH(E11,A2:I2,0)+1)
In this case, we’re specifying the entire table range for the reference argument of the INDEX function, and then using two MATCH functions to return the row and column positions. This MATCH function determines the row for our tax rate:
MATCH(C11,A3:A8,0)
Notice the zero in the match type position, because we want to ensure an exact match on the tax rate. The second MATCH function determines which column has the base-tax amount:
MATCH(E11,A2:I2,0)+1
As before, we’re determining which column our filing status appears in within row 2, but then adding 1 to that amount, since the base tax amount is in the next column over.
We now need to calculate the marginal tax amount beyond the base tax. To do so, add this to the end of the formula in cell D11:
(B11-INDEX(A2:I8,MATCH(C11,A2:A8,0),MATCH(E11,A2:I2,0))+1)*C11
The INDEX function returns the tax tier associated with the tax rate, and this amount is subtracted from the taxable income. $1 is added to this amount to determine the precise marginal amount to be taxed, and then the amount in parenthesis is multiplied by the tax rate in cell C11. The complete formula is shown in Figure 6.
Figure 6: The final piece of the calculator determines the tax due based on income level and filing status.
The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.
This article first appeared Microsoft Professional Accountant's Network newsletter.
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
Aug 31
By David H. Ringstrom, CPA
Look-up formulas are one of Excel’s most powerful features. Instead of manually linking to a worksheet cell, such as =A2, a look-up formula allows you to provide a criteria, such as taxable income, and have the formula automatically return the proper tax rate. This enables you to quickly run through various scenarios with your clients by simply changing the taxable income value. However, income tax calculations in particular can become tricky, as your formula also needs to refer to one of four different tables. In part 1 of this two-part series I’ll explain how to use the VLOOKUP formula to cross-reference tax rates from a single table. I’ll then show you how to use Excel’s INDIRECT function make your VLOOKUP formula refer to the proper table based on your choice of filing status in an adjacent worksheet cell. Next month, in part 2 of this series, I’ll show you how to condense all four tables into one, and use the MATCH and INDEX functions instead of VLOOKUP.
Click here to download the accompanying Income Tax Calculator spreadsheet.
Understand VLOOKUP
As you might infer, VLOOKUP performs vertical look-ups, which means it looks at columnar ranges. A similar function, called HLOOKUP, looks across rows, but for this article I’ll only focus on VLOOKUP. This is an ideal function to use when you need a formula to return a marginal tax rate, as shown in Figure 1. First, let’s discuss the VLOOKUP function, which has 4 arguments:
Caution: VLOOKUP will return a #VALUE! error if you enter a number less than 1 for the Col_Index_Num. Further, VLOOKUP will return a #REF! error if you specify a number greater than the number of columns in the Table_Array.
Trap: Approximate matches require the first column of the Table_Array be sorted in ascending order —otherwise VLOOKUP may return an incorrect value.
Caution: VLOOKUP matches on the first instance of the Lookup_Value. Thus, if your value appears more than once in column A, VLOOKUP will only return the first instance.
Figure 1: We’ll use VLOOKUP to create a simple income tax calculator.
Marginal Tax Rate Look-Up
Now that you understand the inputs for VLOOKUP, let’s walk through an example:
2008 Taxable Income |
Marginal Rate |
Base Tax |
0.00 |
10% |
0.00 |
8,026.00 |
15% |
802.50 |
32,551.00 |
25% |
4,481.25 |
78,851.00 |
28% |
16,056.25 |
164,551.00 |
33% |
40,052.25 |
=VLOOKUP(A11,A3:C8,2,TRUE)
The arguments for the function are as follows:
The formula in cell B11 should return 28% if you entered 125,000 in cell A11.
Shortcut: Since we’re not seeking an exact match, we can omit the Range_Lookup argument and shorten the formula to this:
=VLOOKUP(A11,A3:C8,2)
Conversely, our formula would look like this if we did want an exact match:
=VLOOKUP(A11,A3:C8,2,FALSE)
Tax Calculation
Our formula in cell B11 now determines the marginal tax rate, so now we’ll calculate the tax liability. We can see that income of $125,000 places our taxpayer in the 28% tax bracket. However, 28% doesn’t apply to every dollar of their income — only to income greater than $78,850. We’ll add the base tax of $16,056.25 to this calculated amount, for a total tax of $28,978.25. The formula to perform this calculation is somewhat complex, so we’ll build it in stages:
=VLOOKUP(A11,A3:C8,1)
We specify 1 for the Col_Index_Num, so that we can return the starting point of our tax bracket. Based on income of $125,000, the formula should return $78,851.
=A10-(VLOOKUP(A11,A3:C8,1)-1)
Your formula should now return $46,150. In this case we’re taking our taxable income of $125,000 from cell A11, subtracting the tax bracket of $78,851, and then subtracting $1 from that amount. This is because our client must pay tax of 28% of all income greater than $78,850.
=(A11-(VLOOKUP(A11,A3:C8,1)-1))*B11
Your formula should now return $12,992, which is $46,150 multiplied by 28%.
=(A11-(VLOOKUP(A11,A3:C8,1)-1))*B11+VLOOKUP(A11,A3:C8,3)
The formula should now return $28,978.25, as shown in Figure 2.
Figure 2: The spreadsheet now calculates the tax liability.
Expand the Calculator
Since our formula works with a single table, we’ll now add the three additional tables to the spreadsheet. After that we’ll then assign range names to each table, add a filing status input, and then incorporate the INDIRECT function into our VLOOKUP formulas. Here’s how:
Married-Filing Jointly |
||
2008 Taxable Income |
Marginal Rate |
Base Tax |
0.00 |
10% |
0.00 |
16,051.00 |
15% |
1,605.00 |
65,101.00 |
25% |
8,962.50 |
131,451.00 |
28% |
25,550.00 |
200,301.00 |
33% |
44,828.00 |
357,701.00 |
35% |
96,770.00 |
Married-Filing Single |
||
2008 Taxable Income |
Marginal Rate |
Base Tax |
0.00 |
10% |
0.00 |
8,026.00 |
15% |
802.50 |
32,551.00 |
25% |
4,481.25 |
65,726.00 |
28% |
12,775.00 |
100,151.00 |
33% |
22,414.00 |
178,851.00 |
35% |
48,385.00 |
Head of Household |
||
2008 Taxable Income |
Marginal Rate |
Base Tax |
0.00 |
10% |
0.00 |
11,451.00 |
15% |
1,145.00 |
43,651.00 |
25% |
5,975.00 |
112,651.00 |
28% |
23,225.00 |
182,401.00 |
33% |
42,755.00 |
357,701.00 |
35% |
100,605.00 |
Figure 3: Add the additional tables to your spreadsheet.
Figure 4: You can use the Name Box to assign a range name to a cell or block of cells.
Name limitations: You cannot use spaces or dashes within range names, nor can you start a range name with a number. Many users use the underscore character in place of spaces, such as Head_of_Household.
Excel 2003 or earlier: Choose Tools, and then Data Validation.
Single,MarriedJoint,MarriedSingle,HOH
Caution: Be sure that the contents of the Source field exactly match the range names that you assigned to each of the tables.
Figure 5: Enter these settings in the Data Validation window.
Figure 6: Data Validation provides an in-cell dropdown list.
=VLOOKUP(A11,INDIRECT(D11),2)
Tip: You’re simply replacing A3:C8 with INDIRECT(D11).
=(A11-(VLOOKUP(A11,INDIRECT(D11),1)-1))*B11+VLOOKUP(A11,INDIRECT(D11),3)
INDIRECT: The INDIRECT function enables you to convert text into an Excel address. In this case, we can use INDIRECT to change between one of four tables without having to modify the formulas in cells B11 and C11.
You now have a functional tax calculator that we’ll streamline next month in Part 2 of this series.
The views and opinions expressed in this column are those of the author and do not necessarily reflect theopinions of Microsoft.
This article first appeared Microsoft Professional Accountant's Network newsletter.
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
Aug 15
About the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
Feb 06
Microsoft has posted a list of new features in Excel 2013. Most center around pivot tables, but this list has some gaps, such as the new =FORMULATEXT function that can display a formula from another cell.
Jan 14
The following is programming code we frequently use to populate a drop-down list or listbox on an Excel UserForm with a unique list of items from a spreadsheet.
Private Sub UserForm1_Intialize() 'Creates a new collection Dim myList As New Collection 'Determines number of rows to loop through numRows = Range("A1").CurrentRegion.Rows.Count 'Optional - erases existing dropdown list from control Me.lstDropdown.Clear 'Loops through each row and adds to collection For i = 2 To numRows 'An item can only be added to a collection once, hence the on error On Error Resume Next myList.Add Cells(i, "A"), Cells(i, "A") On Error GoTo 0 Next 'Populates control with items from the collecton For i = 1 To myList.Count Me.lstDropdown.AddItem myList.Item(i) Next 'Optional - erases an existing value Me.lstDropdown.Value = "" End Sub