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
©¿©¬
Thank you! I needed some cheer this day!
I can’t wait for the New Year heat map
It’s ‘colley bird’. Not sure what the price of one of those would be, though.
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
Perfect time of year for this one. Very very cool.
Have a great holiday!
Ha, nice work! I’m thinking a patridge in a pear tree is kind of like a gift set so perhaps it would only count as one.