Out of curiosity, within 15 seconds after publishing the expiring iTunes post to my web site, I googled for the phrase, “This copy of iTunes has expired” and it appeared as search result number five! That is very impressive.  I tried the same search on both Bing and Yahoo and neither search returned a hit for the post, even after more than 20 minutes had passed.

I was pleasantly surprised to see that Google Docs permitted Control-Enter on a cell to enter that formula as an Array Formula. It can also be done by wrapping the entire formula in a call to ArrayFormula (as that is how it is noted in the Spreadsheet program).

If you are unfamiliar with Array Formulas, they can be used to encompass a range of cells using a singleton formula. I find them very powerful, although it may take  while to completely understand its use.

In this example I am using an Array Formula to calculate the Federal Income Tax using a specific rate table; this can be expanded to allow more flexibility when choosing the rate tables. I am using one for the tax year 2010 as married, filing jointly.

To calculate the tax, for each bracket that is less than the total income, the amount range for that bracket is multiplied by the tax for that bracket; the remainder of the income is subtracted from the start of the last bracket and multiplied by its tax rate. MoneyChimp.com has a nice overview of how this works, with examples.

This is easily translated into a formula that determines the net value for each bracket and multiplies that by the rate and sums that result with each bracket whose high end is less than or equal to the income. Where the array formula becomes useful is the summing of each of the brackets.

Tax Bracket Lookup

     $16,750           10%
     $68,000           15%
     $137,300          25%
     $209,250          28%
     $373,650          33%
     $1,000,000,000    35%

For example, to determine the net value for a bracket, assuming a list similar to the one above, you would subtract the cell above from the value of the current bracket, if the high end was less than or equal to the income. I’ve added a high number to the last bracket to (hopefully) ensure the monthly income figure would be less than it. I have also intentionally left a blank cell at the top of the table to equate to zero so the first bracket he a zero for the min value.

Assuming this table is in F3:G8, the following formula accomplishes the goal for a single tax bracket (first one):

=IF(C$7>$F$3, ($F$3-$F$2)*$G$3, IF(C$7>$F$2, (C$7-$F$2)*$G$3, 0)

Note this formula could be used on any row in that table from F3 through F8. To begin to use it as an array formula, we need to take the singleton formula (IF) and apply it across the range of cells. This is done by expanding on the references that iterate through F3:G8, as in:

=IF(C$7>$F$3:$F$8, ($F$3:$F$8-$F$2:$F$7)*$G$3:$G$8, IF(C$7>$F$2:$F$7, (C$7-$F$2:$F$7)*$G$3:$G$8, 0)

It is much easier to do it for a single row than expand the formula to the range of values.

Now that we have a range of values, we sum them up with the SUM formula, that takes an array of numbers:

=SUM(IF(C$7>$F$3:$F$8, ($F$3:$F$8-$F$2:$F$7)*$G$3:$G$8, IF(C$7>$F$2:$F$7, (C$7-$F$2:$F$7)*$G$3:$G$8, 0))

When entering this formula in the formula editor, press Control-Enter to accept the formula; this will convert it into an Array Formula. Similarly, you can wrap the entire formula in the ArrayFormula() call to create the same meaning.

The next post will expand on this calculation to Approximate the Monthly Net Income for an Independent Contractor.

The final spreadsheet is shared on my Google Docs for review.