The Twelve Days of Excel

When you carol along with The Twelve Days of Christmas do you think of spreadsheets? I do, and that certainly says all you need to know about my musical ability. The folks at PNC Wealth Management do also. Annually they issue their Christmas Price Index, with the 2011 version here. Spreading the data out, The Twelve Days of Christmas looks like this:

A B C D E F G H I J K L M N O P
1   1st
Day
2nd
Day
3rd
Day
4th
Day
5th
Day
6th
Day
7th
Day
8th
Day
9th
Day
10th
Day
11th
Day
12th
Day
Total
Quantity
Unit Cost Bundled Cost
2 Drummers Drumming                       12 12 $ 219.16 $ 2,629.90
3 Pipers Piping                     11 11 22 $ 220.69 $ 2,427.60
4 Lords-a-leaping                   10 10 10 30 $ 476.67 $ 4,766.70
5 Ladies Dancing                 9 9 9 9 36 $ 699.34 $ 6,294.03
6 Maids-a-milking               8 8 8 8 8 40 $ 7.25 $ 58.00
7 Swans-a-swimming             7 7 7 7 7 7 42 $ 900.00 $ 6,300.00
8 Geese-a-laying           6 6 6 6 6 6 6 42 $ 27.00 $ 162.00
9 Golden Rings         5 5 5 5 5 5 5 5 40 $ 129.00 $ 645.00
10 Calling Birds       4 4 4 4 4 4 4 4 4 36 $ 129.99 $ 519.96
11 French Hens     3 3 3 3 3 3 3 3 3 3 30 $ 50.00 $ 150.00
12 Turtle Doves   2 2 2 2 2 2 2 2 2 2 2 22 $ 62.50 $ 125.00
13 Partridge 1 1 1 1 1 1 1 1 1 1 1 1 12 $ 15.00 $ 15.00
14 Pear Tree 1 1 1 1 1 1 1 1 1 1 1 1 12 $ 169.99 $ 169.99

PNC provides the Bundled Cost. We have to work backwards to find the Unit Cost. In other words, eight total maids-a-milking cost $58. The data sources are at the Wiki link. The poor dairy lasses are working minimum wage.

For DDoE, PNC’s table is a good way to review the concept of named ranges. Define Christmas as =Sheet1!$B$2:$M$14, _12th_Day as =Sheet1!$M$2:$M$14, Bundled_Cost as =Sheet1!$P$2:$P$14, Maids as =Sheet1!$B$6:$P$6, Quantity as =Sheet1!$N$2:$N$14, and Unit_Cost as =Sheet1!$O$2:$O$14.

The total number of gifts can be =SUM(Christmas) or =SUM(Quantity), being 376 (more on this later). The cost of the 12th Day of Christmas can be =SUM(Bundled_Cost) or = SUMPRODUCT(_12th_Day,Unit_Cost), being $24,263.18. The total expenditure of your True Love is =SUMPRODUCT(Quantity,Unit_Cost), or $101,119.84. A very generous person, your True Love.

We can use the Intersection Operator, a space, as =Maids Quantity, to find that we need 40 work hours from them. The value of named ranges is in the legibility it brings to your spreadsheet. You can expand this until you cover all days and all gifts. The work is in the defining of the names, the benefit is in the presentation.

PNC says the total gifts number 364. Surely a partridge and a pear tree count as two gifts in your household, no? Happy Holidays!

…mrt
©¿©¬

6 thoughts on “The Twelve Days of Excel

  1. Hi Rob –

    Well, it is, and it isn’t. And it’s both “gold rings” and “golden rings”. Here’s what Wikipedia says:

    “The fourth day’s gift is often stated as four “calling” birds but originally was four “colly” birds, being another word for a blackbird.”

    “A minor variant includes the singing of “golden” rather than “gold” rings, to avoid having to stretch “gold” into two syllables (“go-old”)”

    “The price of a canary at Petco is used for the calling [sic] bird, though the price of a blackbird (colly bird) would be more in tune with the song.”

    …mrt

Leave a Reply

Your email address will not be published. Required fields are marked *