tag:blogger.com,1999:blog-1869924468172210809.post5252225520863656051..comments2024-05-09T12:49:12.870+01:00Comments on Not Just Numbers: A Christmas Excel ChallengeGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger30125tag:blogger.com,1999:blog-1869924468172210809.post-4179443984708907352013-12-26T13:03:57.994+00:002013-12-26T13:03:57.994+00:00A solution, to show a VBA Function call itself rec...A solution, to show a VBA Function call itself recursively:<br /><br />Option Explicit<br /><br />Sub tellnrofgifts()<br /> Const nrofdays As Integer = 12<br /> Dim nrofgifts As Integer<br /> nrofgifts = gifts(nrofdays)<br /> MsgBox ("Number of gifts after " & nrofdays & " days is: " & nrofgifts)<br />End Sub<br /><br />Function gifts(day As Integer) As Integer<br /> If day < 2 Then<br /> gifts = 1<br /> Exit Function<br /> Else<br /> gifts = gifts(day - 1) + (day * (day + 1) / 2)<br /> End If<br />End Function<br />Anonymoushttps://www.blogger.com/profile/10161169968930779097noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-48635215793885296572013-12-20T17:06:03.268+00:002013-12-20T17:06:03.268+00:00To All:
To add a twist to this fun, use this VBA ...To All:<br /><br />To add a twist to this fun, use this VBA code, add your button to your worksheet, assign the Macro name and save file as xlsm.<br /><br />Now highlight a range of "text" like the The Twelve Days of Christmas, click on the button, type in the word or words to count. Example: Five Golden Rings = 8<br /><br />Code:<br />Dim Count As Integer<br />Dim Target As String<br />Dim Cell As Object<br />Dim N As Integer<br /><br />Sub Target_Count()<br /> Count = 0<br /> Target = InputBox("character(s) to find?")<br /> If Target = "" Then GoTo Done<br /> For Each Cell In Selection<br /> N = InStr(1, cell.Value, target)<br /> While N <> 0<br /> Count = count + 1<br /> N = InStr(n + 1, cell.Value, target)<br /> Wend<br /> Next Cell<br /> MsgBox count & " Occurrences of " & target<br />Done:<br />End SubMike McCormickhttp://www.mccormickpcs.comnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-71835028193098627472013-12-19T19:21:33.300+00:002013-12-19T19:21:33.300+00:00Simple & Sily solution is count Paragraphs.......Simple & Sily solution is count Paragraphs.....e.g. Para 1 = Gift 1; Para 2 = 2+1; Para 3 = 3+2+1; Para 4 = 4+3+2+1.....Para 12 = 12+11+10....+1 = 364......No advance excel tricks requiredAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-84895153929074115082013-12-19T03:35:16.189+00:002013-12-19T03:35:16.189+00:00I made mine anticipating pedants and with the opti...I made mine anticipating pedants and with the option for expansion (never a wrong time for best practice!).<br /><br />So, six rows in my main table:<br />Gift - free text eg "Doves", "Geese"<br />Day - starting at 0 to help make copying down formulae easy and going down to 12<br />New - number of new gifts that day, calculated by adding the day number to the day number multiplied by the value given in a sub-gifts lookup table using the gift name as a reference. So for example, if we decide that the tree is a gift as well as the partridge, we enter 1 in the sub-gifts lookup table. If we decide that a drummer is one gift, but his drum and drumsticks are also gifts we enter 3 in the lookup table (assuming each drumstick is a separate gift). The lookup table ensures we can add gifts, reorder gifts on different days and change number of sub-gifts easily.<br />New items from previous days - this is the fresh batch of gifts which are duplicates of yesterdays, so simply the previous value in this column plus the previous value from the New. To keep the formula working need to hard code a 0 for this on day 0.<br />Carried forward from previous days - All the gifts we've had up to this point. So the previous day's values for New, New items from previous days and this column.<br />Total - The total number of gifts that day. So the sum of New, New items from previous days and carried forward from previous days for that day.<br /><br />My answer then?<br />Assuming that:<br />the a goose is one gift and the egg it is a-laying is another gift<br />each drummer has additional gifts of 1 x drum 2 x drumsticks<br />each piper has a pipe as an additional gift<br />each maid is delivered with a cow<br />each partridge comes with a tree<br /><br />we have 425 gifts<br /><br />If you replicate this, don't forget to sort your lookup table for vlookup.<br /><br />~Robskii the Red Nosed RerAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-34505887045294778932013-12-18T13:05:27.081+00:002013-12-18T13:05:27.081+00:00Sorry, I left out Drummers on Twelve.
Now that I ...Sorry, I left out Drummers on Twelve.<br /><br />Now that I think about it, don't use the numeral words, just use the gift name, duh<br /><br />'=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<br /><br />That's enough fun for one week. Thanks Glen.Mike McCormickhttp://www.mccormickpcs.comnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-11271369307892229182013-12-18T12:39:55.357+00:002013-12-18T12:39:55.357+00:00Glen:
Thanks for the nomination.
To clarify a fe...Glen:<br /><br />Thanks for the nomination.<br /><br />To clarify a few things when using the CONTIF formula. If your range includes the lyrics (example: On the fourth day of Christmas) excel will count fourth as Four. So either exclude in the range or use the formula below eliminating this extra count. The extra count occurs on; fourth, sixth, seventh, eighth, tenth & eleventh. <br /><br />'=COUNTIF($D$6:$D$118,"*Partridge*")*1+COUNTIF($D$6:$D$118,"*Two Turtle*")*2+COUNTIF($D$6:$D$118,"*Three French*")*3+COUNTIF($D$6:$D$118,"*Four Calling*")*4+COUNTIF($D$6:$D$118,"*Five Golden*")*5+COUNTIF($D$6:$D$118,"*Six Geese*")*6+COUNTIF($D$6:$D$118,"*Seven Swans*")*7+COUNTIF($D$6:$D$118,"*Eight Maids*")*8+COUNTIF($D$6:$D$118,"*Nine Ladies*")*9+COUNTIF($D$6:$D$118,"*Ten Lords*")*10+COUNTIF($D$6:$D$118,"*Eleven Pipers*")*11+COUNTIF($D$6:$D$118,"*Twelve*")*12 <br /><br />Happy HolidaysMike McCormickhttp://www.mccormickpcs.comnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-46140091069897971792013-12-18T11:59:00.692+00:002013-12-18T11:59:00.692+00:00He has indeed! very impressive. I'm now strugg...He has indeed! very impressive. I'm now struggling with Why the +1 Why the +2 and why 6???Anonymoushttps://www.blogger.com/profile/08431282255635366973noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-48358197231947966382013-12-18T09:15:12.967+00:002013-12-18T09:15:12.967+00:00Another way of loking at it is this. On the first ...Another way of loking at it is this. On the first day, he gets 1 present, on the second 1+2, on the third 1+2+3 and so on. The formula for this as has been pointed out is n*(n+1)/2 for each day, which then needs to be added. So we can start with<br /><br />=1+2*3/2+3*4/2+4*5/2+5*6/2+6*7/2+7*8/2+8*9/2+9*10/2+10*11/2+11*12/2+12*13/2<br /><br />This can be simplified to<br /><br />=1+(2*3+3*4+4*5+5*6+6*7+7*8+8*9+9*10+10*11+11*12+12*13)/2<br /><br />and then we can develop a nice array formula, which is <br /><br />{=1+SUM(ROW(2:12)*ROW(3:13)/2)}<br /><br />Again, don't forget to hit Ctrl-Enter when entering the formula.<br />Anonymoushttps://www.blogger.com/profile/17599442084962627592noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-60634608755038835572013-12-18T08:17:16.964+00:002013-12-18T08:17:16.964+00:00I thought it would be fun to see if I could do thi...I thought it would be fun to see if I could do this as a one cell array formula. Not the simplest method, I admit, but this works (don't forget to put Ctr;-Enter when you enter the formula):<br />{=2*(SUM(ROW(1:6)*(CHOOSE(ROW(1:6),12,11,10,9,8,7))))}<br />The maths is quite simple. We start off with 1*12, then add 1*12 plus 2*11, then add 1*12 plus 2*11 then 3*10, etc and end up with the whole series ending with 12*1. Therefore we have 12*1 once, but also 1*12 twelve times, 11*2 twice but 2*11 eleven times, which means that we have 2*((1*12)+(2*11)+(3*10)+(4*9)+(5*8)+(6*7))<br />This formula takes the values of rows 1-6 using the ROW function and multiplies them by the values 12 through 7 using the CHOOSE function, and multiplies the whole lot by 2 to get the answer of 364. Voila!Anonymoushttps://www.blogger.com/profile/17599442084962627592noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-730073395225984672013-12-18T05:00:06.515+00:002013-12-18T05:00:06.515+00:00Interesting challenge!!Interesting challenge!!Vikram Shankar Mathur FCAhttps://www.blogger.com/profile/00646590998741134560noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-87116571292048846732013-12-18T01:47:16.915+00:002013-12-18T01:47:16.915+00:00How about having a summary area to enter the varia...How about having a summary area to enter the variable day number and to return the result and then a separate table that populates based on the variable for the number of days that is entered...<br /><br />A1 = "day"<br />A2 = enter the day number - the variable chosen can be e.g.12<br />B1 = "number of presents"<br />B2 =SUM(presents) <br /><br />The formula in B2 should be entered once a table has been created and a range named as "presents"... see below.<br /><br />Build the table... this assumes the partridge and the pear tree comprise 2 separate gifts - if they only represent one gift, remove the "+1" from the formulas in column E<br /><br />D1 = "day number"<br />E1 = "# of presents"<br /><br />D2 = 1<br />E2 =IF(D2="","",SUM($D$2:D2)+1) <br />D3 =IFERROR(IF((D2+1)>$A$2,"",D2+1),"")<br />E3 =IF(D3="","",SUM($D$2:D3)+1)<br /><br />Copy D3 - E3 down to as many rows as desired - so if more verses are added, they will be accommodated.<br /><br />Select the cells filled in columns D and E and click on insert table to convert this content to a table format.<br /><br />Name column E "presents" and enter the formula in B2 = Sum(presents) using the "presents" named range in the formula.<br /><br />Experiment with the variable - the number of days in A2 and see the result for # of presents update, and the table re-populate as appropriate.<br /> Anonymoushttps://www.blogger.com/profile/06171582110411507479noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-30159493072701775152013-12-18T01:27:27.956+00:002013-12-18T01:27:27.956+00:00I got to the same point as you, trying to do it wi...I got to the same point as you, trying to do it with one formula and my maths struggled at getting further than that. If you look at Simon Thacker's (2nd) comment above, he's done it.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-2722257570816672562013-12-18T01:22:29.410+00:002013-12-18T01:22:29.410+00:00I like that. Certainly in the running for the quir...I like that. Certainly in the running for the quirkiest solution!Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-32454270707487439622013-12-18T01:21:15.892+00:002013-12-18T01:21:15.892+00:00ThIs little post does appear to have started somet...ThIs little post does appear to have started something!Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-796931202659209802013-12-18T00:34:41.018+00:002013-12-18T00:34:41.018+00:00err...so I did a spreadsheet and listed out how ma...err...so I did a spreadsheet and listed out how many presents each day. Total seems to be 364, which runs in a sequence over different presents 12,22,30,36,40,42,42,40,36,30,22,12 which is nicely symmetrical, but doesn't get you very far.<br />Then I realised that on each day where n is the number of the day my true love sends n+(n-1) presents. Then my arithmetic ran into the wall of maths and it's been so long. Can anyone tell me please if there is a way of applying a factor for the number of days in total to my little formula of n+(n-1) to give the correct solution for any number of days?Anonymoushttps://www.blogger.com/profile/08431282255635366973noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-38407321510512877952013-12-17T19:19:16.192+00:002013-12-17T19:19:16.192+00:00Though not as short as some others it does achieve...Though not as short as some others it does achieve the same answer (364 gifts) in one formula. Pasting the The Twelve Days of Christmas lyrics in excel and using the COUNTIF formula below:<br /><br />=COUNTIF(range,"*Partridge*")*1+COUNTIF(range,"*Two*")*2+COUNTIF(range,"*Three*")*3+COUNTIF(range,"*Four*")*4+COUNTIF(range,"*Five*")*5+COUNTIF(range,"*Six*")*6+COUNTIF(range,"*Seven*")*7+COUNTIF(range,"*Eight*")*8+COUNTIF(range,"*Nine*")*9+COUNTIF(range,"*Ten*")*10+COUNTIF(range,"*Eleven*")*11+COUNTIF(range,"*Twelve*")*12Mike McCormickhttp://www.mccormickpcs.comnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-37008793622148501052013-12-17T16:31:02.101+00:002013-12-17T16:31:02.101+00:00Ok people, what about the geese a laying ... are t...Ok people, what about the geese a laying ... are they laying down or are they laying eggs which would be more gifts with each passing verse. Glen, you've finally found the magic potion to getting more commentators!!Carl Mnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-86598039212580316442013-12-17T16:28:16.528+00:002013-12-17T16:28:16.528+00:00I like Ray’s simple solution. However to make it i...I like Ray’s simple solution. However to make it interactive with a variable number of days of Christmas I would put in A4 the number of days (you could even use a dropdown box to select 1 to 12) and link A2 to A4. In B2 I would put the following formula and copy it across =IF(A2>1,A2-1,0).<br /><br />By selecting the number of days you will then get the total gifts.<br />Anonymoushttps://www.blogger.com/profile/14269889089490253755noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-18977441581828136492013-12-17T16:14:45.968+00:002013-12-17T16:14:45.968+00:00While it is not as elegant as Simon's mathmati...While it is not as elegant as Simon's mathmatical solution or Ray's helper row solution, how about a sumproduct solution? <br />=SUMPRODUCT((ROW(OFFSET(A1,0,0,NumDays)))*(NumDays-(ROW(OFFSET(A1,0,0,NumDays))-1)))<br />It simply multiplies the a list of row numbers from 1 to the number of days by a descending list of those same row numbers.Darin Myersnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-14779602191371902012013-12-17T16:13:45.191+00:002013-12-17T16:13:45.191+00:00This comment has been removed by the author.Alwaysinginghttps://www.blogger.com/profile/09781056921641245601noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-28413909215462526092013-12-17T14:04:41.185+00:002013-12-17T14:04:41.185+00:00Correct, it's not a trick. :)Correct, it's not a trick. :)Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-18576490243677194762013-12-17T14:03:44.939+00:002013-12-17T14:03:44.939+00:00Ray, that's a nice simple solution. More of an...Ray, that's a nice simple solution. More of an Excel solution than Simon's impressive mathematical one above.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-17611225610914269412013-12-17T14:02:26.663+00:002013-12-17T14:02:26.663+00:00It is the cumulative sum that is required. I knew ...It is the cumulative sum that is required. I knew somebody would manage it in one formula - well done.Glen Feechanhttps://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-91289264561147599152013-12-17T13:18:37.863+00:002013-12-17T13:18:37.863+00:00The complexity I can see is that the partridge and...The complexity I can see is that the partridge and the pear tree are two gifts, but I believe it is correct to treat each drummer and drum as one gift and each piper and pipe as other single gifts.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-10471415395116738472013-12-17T12:24:13.693+00:002013-12-17T12:24:13.693+00:00I just like the simplicity of numbering 1 to12 in ...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 columnAnonymoushttps://www.blogger.com/profile/14447714496769351984noreply@blogger.com