This is a guest post by Myles Arnott from Clarity Consultancy Services – UK.
In this and next 3 posts, we will learn how to make a Dynamic Dashboard using Microsoft Excel.
At the end of this tutorial, you will learn how easy it is to set up a dynamic dashboard using excel formulas and simple VBA macros.
[Click here for large version of the image]
Introduction:
The dashboard also demonstrates the standard approach I use in all of my models which is to incorporate three key sheets in addition to the data and analysis tabs.
These are:
- Home page
- Inputs (or drivers)
- Helpsheet
The dynamic dashboard can be downloaded here [mirror, ZIP Version]
The dashboard file works in Excel 2007+. Pls. enable macros to get it work.
The plan is to break this dashboard tutorial down into four parts over the next four weeks. If further topics fall out as a result of discussions either Chandoo or I will pick them up and if necessary post further parts.
- Part 1: Introduction & overview
- Part 2: Dynamic Charts in the Dashboard
- Part 3: VBA behind the Dynamic Dashboard a simple example
- Part 4: Pulling it all together
I would like to take a quick opportunity to give credit for some of the elements of functionality in the model:
- Boxcharts – Chandoo [Link]
- Scrolling report – Chandoo [Link]
- Competitor analysis – Chandoo [Link]
- Use of camera tool – Chandoo [Link]
- In cell microcharts – Chandoo [Link]
- Helpsheet – John Walkenbach
Okay so lets get started with an overview:
What is the objective of the report?
The Dynamic Dashboard is intended to provide pertinent summary information to aid management decision making. Combining a high level of flexibility within each report and then allowing the user to choose which reports to include and where to position them allows an enormous amount of flexibility over the message to be communicated.
What does this Dynamic Dashboard do?
The dynamic dashboard allows the user to select a report from the range of reports within the model and decide where to position it on the page. The user can select “hide” to hide a report that they do not want to see or select “view” to preview it prior to choosing its position.
- Clicking on either the hyperlink name or the report image will take you to the report.
- Each report is highly flexible allowing the user to cut the data in many ways to show management the most pertinent information.
Overview of Dashboard Tabs:
Home Page
I always include a homepage in my models and often set an auto_open routine to select this as the first page seen on opening. The Home page is designed to present the contents of the model to the user and provide links to each page for easy navigation.
The Dynamic Dashboard
This is the main tab for pulling together the dashboard and will be covered in parts 3 and 4.
Inputs
This is the page for all validation lists and drivers.
Help Sheet
Once again a sheet that is in all of my models. This user form based help sheet provides the user with a quick help function and complements the accompanying user notes. I find it helpful to lay it out in tab order.
This is how the Help user form looks once opened. The user can either choose the topic from the dropdown or by clicking next.
Chart 1 and 2 : Flexible pie charts
Dynamic pie charts with the option to select the KPI, period and product/salesperson to be analyzed. These are covered in part 2.
Chart 3 & 4: Flexible line charts
Dynamic line charts with the option to select the KPI, period and product/salesperson to be analyzed. These are also covered in part 2.
Chart 5: Box Chart
Details on how to create these box charts.
Chart 6: Scrolling Report of KPIs
Chandoo’s blog on how to create this scrolling report can be found here. Micro charts which is of my favorite blogs from Chandoo are covered here.
Chart 7: Scrolling Comparison Chart
Details on how to create this scrolling chart.
Chart 8 : Executive Summary
A simple executive summary. Please see Chandoo’s article on a twitter board for an alternative view.
So that was an overview of the model and its main tabs.
What Next?
Next week we will look at Part 2 of this series and learn how to construct dynamic charts.
Download the complete dashboard
Go ahead and download the dashboard excel file. The dynamic dashboard can be downloaded here [mirror, ZIP Version]
It works on Excel 2007 and above. You need to enable macros and links to make it work.
Added by PHD:
Myles has taken various important concepts like Microcharts, form controls, macros, camera snapshot, formulas etc and combined all these to create a truly outstanding dashboard. I am truly honored to feature his ideas and implementation here on Chandoo.org. I have learned several valuable tricks while exploring his dashboard. I am sure you would too.
If you like this tutorial please say thanks to Myles.
Related Material & Resources
- Excel Dashboards – Tutorials & Templates Section of PHD
- 6 Part Tutorial on Making KPI Dashboards in Excel
- Recommended Product: Jorge Camoes’ Dashboard Training Kit
This is a guest post by Myles Arnott from Clarity Consultancy Services – UK.
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