CEO needaspreadsheet.com
Columnist
Share this content

Partridges, Pear Trees and Merry Christmas!

24th Dec 2013
CEO needaspreadsheet.com
Columnist
Share this content

Christmas QuestionFrom the Not Just Numbers blog:

Last week’s post caused a bit of a stir – and distracted a lot of people from work in the run up to Christmas!

In case you missed it, here is the post:

A Christmas Excel Challenge

I asked readers to come up with ways of using Excel to calculate how many gifts are in the Christmas song, The Twelve Days of Christmas, and, boy, did you rise to the challenge!

You can read all of the comments on the post itself, but I have highlighted below some of my favourites.

Just for clarification, there was no trick, each days gift was considered as one gift, e.g. a Partridge in a Pear Tree is one gift (not one Partridge and one Pear Tree). However, each gift (apart from the last) is received on more than one day, the Partridge in a Pear Tree being received on each of the 12 days, the two Turtle Doves being received on the each of the last 11 days, etc.

For most people (myself included) the maths skills stopped at coming up with a formula for each day’s presents:

=(B1+1)/2*B1

where B1 contains the day number in question. Therefore on the 12th day 78 gifts are received.

However, Simon Thacker was the first to come up with one formula for the total number of gifts:

=B1*((B1+1)*(B1+2))/6

returning 364 total gifts over the 12 days.

This is a Tetrahedral number from Pascal’s Triangle, as described in this video from Mary Pat Campbell explaining the formula above.

No doubt that this is the most efficient solution, with just one formula to calculate the answer.

This was, however, an Excel problem rather than a maths problem, so I think it is also worth mentioning a more Excel-based solution from Ray Andrews:

“I just like the simplicity of numbering 1 to12 in row 1, 12 to1 in row 2, multiplying row 1 by row 2 in row 3 and summing the result of row three in the thirteenth column”

This uses Excel’s capabilities while keeping the maths really simple.

Finally I did say that I would mention quirky solutions too and my favourite is this from Mike McCormick:

=COUNTIF(range,”*Partridge*”)*1+COUNTIF(range,”*Turtle*”)*2+COUNTIF(range,”*French*”)*3+COUNTIF(range,”*Calling*”)*4+COUNTIF(range,”*Golden*”)*5+COUNTIF(range,”*Geese*”)*6+COUNTIF(range,”*Swans*”)*7+COUNTIF(range,”*Maids*”)*8+COUNTIF(range,”*Ladies*”)*9+COUNTIF(range,”*Lords*”)*10+COUNTIF(range,”*Pipers*”)*11+COUNTIF(range,”*Drummers*”)*12

Where range contains the lyrics. of the song!

Thanks for all of your contributions.

Have a wonderful Christmas and a fantastic New Year!

Glen

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.

Tags:

You might also be interested in

Replies (2)

Please login or register to join the discussion.

Routemaster image
By tom123
25th Dec 2013 11:44

Inspired

Thanks for these blogs - they have inspired me to learn a bit more about the tool we all probably use for a substantial proportion of our working day.

Thanks (0)
Replying to Paul Scholes:
Glen Feechan
By Glen Feechan
31st Dec 2013 10:13

Thanks  - always nice to be appreciated!

Thanks (0)