Search Not Just Numbers

Loading...

Tuesday, 24 December 2013

Partridges, Pear Trees and Merry Christmas!

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".

2 comments:

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

    ReplyDelete
  2. Solely UKLPG Okayed Autogas Installers be given public Federal fake prada bags. In a mere several years the volume of Fruit juice Petroleum Propane cars and trucks possesses rocketed by 3, 500 to help in excess of one hundred and fifty, 000. This UKLPG advertised: "We are currently witnessing fantastic levels of desire intended for LP Propane Conversions. inch Some sort of cars driving a car 10, 000 mile after mile 12 many weeks will probably regain the purchase price interior primary season. LPG Conversions. LPG symbolizes Liquefied petroleum Propane and is particularly some sort of by-product on the petroleum small business currently being fake 2.55 chanel handbags from the refining connected with fat, or maybe obtained from fat or maybe propane revenues since they get away from the earth. And so though LPG may crank out all around 20% fewer emissions in comparison with typical diesel-engined in addition to petroleum centric machines, as well as rather undoubtedly some sort of fossil energy resource. LPG Conversions. World wide increased temperatures is usually an issue that everyone appreciates presently, and from now on we can easily many complete your little that can help. There are many unique chanel outlet that can help spend less the environment, far too a lot of to help number in this article, although let’s give attention to LPG. Renovating the car to help LPG is usually an very nice move forwards, both equally with environmental in addition to personal words. This significant variety of Laser, nitrous oxides in addition to particulates machines dispose of is usually awful. 120 LPG function motor vehicles will probably make identical variety of particulates united identical diesel-engined cheap fake watches. LPG discharges less this NOx connected with petroleum and much a lot less than just one 20 th this NOx connected with diesel-engined. LPG discharges 17% to help 20 % fewer Co2 fractional laser. LPG Conversions. past a long time, this know-how connected with car-electronics in addition to connected with petroleum centric machines include modified on excessive swiftness. Modern-day petroleum machines include some sort of sequential multipoint petrol-injection process of which is effective rolex replica by using a CAN-bus process when using the technology on the car or truck. The most effective connected with Romano to help these technological improvements is going to be Romano RISN process. This can be a high-tech multipoint sequential in-line LPG hypodermic injection process.

    ReplyDelete