Things are looking up
Taxing formulas: bracket-rate calculations in any spreadsheet
Using Excel to find the busiest day of the week
For this month's column, I selected two letters that involve two of the most useful spreadsheet functions - vlookup and choose. Vlookup lets you create a formula that selects an item based on a matching value in a table's left-most column. Choose picks a value from a list that's contained inside the Choose formula. Here are some ideas for handy ways to put these helpers to work in your applications.
Q Suppose that I wanted my worksheet to begin with a figure such as $166,000, and then multiply the first $140,000 by 0.44, the next $5,000 by 0.45, the next by 0.46, and so on until the $166,000 is used up. Is there a way that this can be done automatically?
- Kerry Owens
A Of course. The calculation that you describe is very common wherever schedules of rates are involved, such as in tax tables. Since that's an application with which everyone is (depressingly) familiar, I'll show you how to set up a tax table, and then you can substitute your own data.
To set up the model in Quattro Pro or 1-2-3, follow these steps:
1. Enter the values and labels shown in ranges A1..B7, C1..C2, and E2..E4 of Figure 1.
2. Enter the formula shown in cell C3 of the figure, +C2+(A3-A2)*B2, and copy it to range C4..C7.
3. Assign the range name Income to cell F2, and then assign the name Table to range A2..C7.
4. In cell F4, enter the following:
(income-@vlookup(income,table,0))*@vlookup(income,table,1)+@vlookup(income,table,2)
Excel users should recall that the lookup functions begin numbering the columns of the table with 1, not 0, so the formula in F4 should look like this:
=(incomevlookup(income,table,1))*vlookup(income,table,2)+vlookup(income,table,3)
In the worksheet, the brackets and their rates are laid out in columns A and B. The formulas in column C calculate the base amount for each bracket. For example, +C2+(A3-A2)*B2 in cell C3 takes the base amount for the first bracket (C2), plus the rate for the second bracket (B2) times the dollars in the second bracket (A3-A2). Now you know why your head throbs so excruciatingly every year when the Tax Pack arrives.
The formula in cell F4 calculates the tax on the income in cell F2. In this formula, the first lookup function, @vlookup(income,table,0), returns the threshold value for the second bracket, $533, from cell A3. Subtracting the threshold value from the income value yields the amount of income in the second bracket.
Multiply by the tax rate for the second bracket, @vlookup (income,table,1), to get the tax due for the second bracket. Finally, to complete the calculation, add the base amount for the previous brackets, @vlookup(income,table,2).
For your problem, Kerry, just extend the table, and substitute your values for the tax data, following the pattern shown right.
Q I work for a meat packing plant, where we log the date of stock shipments into an Excel 5.0 worksheet. Now I need to prepare a report that shows the number of shipments by day of the week. In particular, I need to know which day of the week had the most shipments. What's the best way for me to handle this?
- Elmer Pyron
A To find out which day of the week had the most shipments, try this formula, courtesy of Shane Devenshire.
If your dates are in range A2:A100, the array formula {=choose(mode(weekday(A2:A100)),"Sun","Mon","Tue","Wed","Thu", "Fri","Sat")} shows the name of the day that had the most shipments (be sure to press <Ctrl>+<Shift>+<Enter> to enter the formula as an array formula). In this formula - based on an old spreadsheet trick - the weekday function returns a code that corresponds to a day of the week; then the choose function uses that number to look up the day that occurs most frequently in A2:A100.
If you want the formula to accommodate gracefully as you add more shipments, substitute the term offset(A2,0,0, count(A:A),1) for the address A2:A100. This expression returns the range that contains date values in column A.
On the other hand, if you need a report that shows the number of shipments by day, enter =choose(weekday(A2), "Sun","Mon","Tue","Wed","Thu","Fri","Sat") in a new column, headed dow, in your list. Then create a copy for each shipment and use a pivot table to count the number of shipments by day of week.