Today I am asking you a tricky formula question. This is asked by Ionel on the Introduction to VLOOKUP, OFFSET & MATCH Formulas post.
The question is,
I have data in three columns: A,B,C and I want to get the average of the closest two values out of three in each row. Could you help me with a formula for this?
Now, how would you go about it?
What does closest of two mean?
What does closest of two mean?We can assume that close-ness is nothing but distance between 2 numbers on numeric scale. So 3 is closer to 2 and 4 compared to 1 or 5.
Your challenge:
Assuming your data is in A2:C10, what formula will you write in D2:D10 to solve this?
Go ahead and get some coffee and get thinking.
Want to cop-out?
I have posted one solution in the next comment. You can see how I went about solving it.
14 Responses to “How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]”
in C3=2016
in C4=3
in C5=1 (the first next year with three Friday the 13ths)
=SMALL(IF(MMULT(--(MOD(DATE(C3+ROW(1:1000),COLUMN(A:L),13),7)=6),ROW(1:12)^0)=C4,C3+ROW(1:1000)),C5)
formula check in the next 1000 years
This will generate a table of counts of Friday the 13th's by year. If I didn't screw it up the next year with three is 2026.
I created a simple parameter table with a start date and end date that I wanted to evaluate. That calculates the number of days and generates a list of those days. Then filter and group. The generation of the list in power query (i.e. without populating a date table in excel) is pretty cool, otherwise this isn't really doing anything than creating a big date and filtering/counting.
let
Source = List.Dates(StartDateAsDate, Days2, #duration(1,0,0,0)),
ConvertDateListToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddDayOfMonthColumn = Table.AddColumn(ConvertDateListToTable, "DayOfMonth", each Date.Day([Column1])),
AddYearColumn = Table.AddColumn(AddDayOfMonthColumn, "Year", each Date.Year([Column1])),
AddDayOfWeekColumn = Table.AddColumn(AddYearColumn, "Day of Week", each Date.DayOfWeek([Column1])),
FilterFriday13 = Table.SelectRows(AddDayOfWeekColumn, each ([DayOfMonth] = 13) and ([Day of Week] = 5)),
Friday13thsByYear = Table.Group(FilterFriday13, {"Year"}, {{"Number of Friday the 13ths!", each Table.RowCount(_), type number}})
in
Friday13thsByYear
With the parameters replaced by values should you want to play along at home. This runs for 20 years starting on 1/1/2016.
let
Source = List.Dates(#date(2016,1,1), 7300, #duration(1,0,0,0)),
ConvertDateListToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
AddDayOfMonthColumn = Table.AddColumn(ConvertDateListToTable, "DayOfMonth", each Date.Day([Column1])),
AddYearColumn = Table.AddColumn(AddDayOfMonthColumn, "Year", each Date.Year([Column1])),
AddDayOfWeekColumn = Table.AddColumn(AddYearColumn, "Day of Week", each Date.DayOfWeek([Column1])),
FilterFriday13 = Table.SelectRows(AddDayOfWeekColumn, each ([DayOfMonth] = 13) and ([Day of Week] = 5)),
Friday13thsByYear = Table.Group(FilterFriday13, {"Year"}, {{"Number of Friday the 13ths!", each Table.RowCount(_), type number}})
in
Friday13thsByYear
=MATCH(3,MMULT(N(WEEKDAY(DATE(C3+ROW(1:100)-1,COLUMN(A:L),13))=6),1^ROW(1:12)),)+C3-1
It should be pointed out that Alex's solution, unlike some others, has the additional advantage of being non-array. My solution was nearly identical but with -- and SIGN instead of N and 1^.
=C3-1+MATCH(3,MMULT(--(WEEKDAY(DATE(C3-1+ROW(1:25),COLUMN(A:L),13))=6),SIGN(ROW(1:12))),0)
Sub Friday13()
Dim StartDate As Date
Dim EndDate As Date
Dim x As Long
Dim r As Long
Range("C7:C12").ClearContents
StartDate = CDate("01/01/" & Range("C3"))
EndDate = CDate("31/12/" & Range("C3"))
r = 7
For x = StartDate To EndDate
If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then
Cells(r, 3) = Month(x)
r = r + 1
End If
Next
End Sub
Calculate next year with 3 Friday 13th. Good for 100 years different from year entered in cell C3
Sub ThreeFriday13()
Dim StartDate As Date
Dim EndDate As Date
Dim x As Long
Dim WhatYear As Integer
Dim Counter As Integer
Range("E7").ClearContents
StartDate = CDate("01/01/" & Range("C3") + 1)
EndDate = CDate("31/12/" & Range("C3") + 100)
Counter = 0
For x = StartDate To EndDate
If WhatYear Year(x) Then
WhatYear = Year(x)
'Different year so reset counter
Counter = 0
End If
If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then
Counter = Counter + 1
If Counter = 3 Then
WhatYear = Year(x)
Exit For
End If
End If
Next
Range("E7") = WhatYear
End Sub
*RE-POST as not equal did not show earliuer
Calculate next year with 3 Friday 13th. Good for 100 years different from year entered in cell C3
Sub ThreeFriday13()
Dim StartDate As Date
Dim EndDate As Date
Dim x As Long
Dim WhatYear As Integer
Dim Counter As Integer
Range("E7").ClearContents
StartDate = CDate("01/01/" & Range("C3") + 1)
EndDate = CDate("31/12/" & Range("C3") + 100)
Counter = 0
For x = StartDate To EndDate
If WhatYear NE Year(x) Then
WhatYear = Year(x)
'Different year so reset counter
Counter = 0
End If
If Day(x) = 13 And Weekday(x, vbMonday) = 5 Then
Counter = Counter + 1
If Counter = 3 Then
WhatYear = Year(x)
Exit For
End If
End If
Next
Range("E7") = WhatYear
End Sub
earlier*
I've a doubt with using array formula here.
In sample workbook, I tried to replicate the formula again.
=IFERROR(SMALL(IF(WEEKDAY(DATE($C$3,ROW($A$1:$A$12),13))=6,ROW($A$1:$A$12)),$B7),"")
For this I selected C7 to C12, and typed the same formula and pressed ctrl+alt+Enter. But in all cells it is taking $B7 (and not $B7, $B8, $B9.... etc)
and since it is array formula I can't edit individual cell.
Please guide.
Thanks
Hi Chandoo,
Cool stuff. You need to clarify that the answer of 5 represents the 1st month in the year that has a Friday the 13th, and not the number of Fridays the 13th in the year. Subtle, but important difference.
Thanks,
Pablo
I like the MMULT() function far more, but here's how I would have tackled it. It uses an EDATE() base and MODE() over 100 years. I'm assuming that 100 years is enough time to catch the next year with 3 friday 13th's. Array entered, of course.
{=MODE(IFERROR(YEAR(IF((WEEKDAY(EDATE(DATE(C3, 1, 13), ROW(INDIRECT("1:1200"))))=6), EDATE(DATE(C3, 1, 13), ROW(INDIRECT("1:1200"))), "")), ""))}
Finding all the Friday the 13ths in a Year:
=SUMPRODUCT((DAY(ROW(INDIRECT(DATE(C3,1,1)&":"&DATE(C3,12,31))))=13)*(TEXT(ROW(INDIRECT(DATE(C3,1,1)&":"&DATE(C3,12,31))),"ddd")="Fri"))
{=sum(if(day.of.week(DATe($YEAR;{1;2;3;4;5;6;7;8;9;10;11;12};13);1)=6;1;0))}
just list the years