The advanced score meter chart is nothing else than such an application that helps us display each key performance indicator in a different way till now.
We have already seen several solutions – like gauge chart – on the net but these are all looked alike except for a very few.
We keep ourselves to the previously written down principles meaning that on the Excel Dashboard School blog you will only see unique Excel templates and dashboards.
This will be like this today, we will introduce the making of an advanced score meter chart but we wouldn’t settle only for this, we go further and the article will contain some very unique and original solutions.
Even the array formulas will be at our help!
On these pictures can be seen that we have endeavored to create such user interface that you can tell at a glance what is displayed and the meaning of it. Let’s say a few words of the widget!
Advanced Score Meter Chart – Using Slicer Tool
The effectuation of the interval division that makes the score meter chart unique. We all know the classic scale with degrees ranging from week to excellent.
We will use these and in a dynamic way! As a default we will be able to handle values between 0% and 100%.
Our advanced score meter chart will have that special characteristic that we will set each element of the color chart by giving arbitrary values. As usual red is the smallest / weakest value, the dark green is the best / highest value.
The division is not needed to be even, we can differ from the average division rule of 0%-20%; 21%-40%; …81%-100%.
Why is this so useful for us? Using the opportunities given by free configuration there will be KPIs available for display that are differ from the customary. We have defined the percentages in the ‘Slicers’ column. But we have to bring your attention to one important issue!
When changing values (any one out of the five) pay attention so that the value doesn’t exceed 100% because we can maximum only display this on the chart.
In our example we increased the middle interval (yellow color) to 30% so we had to reduce the interval containing the weakest value to 10%.
(Of course it is not mandatory to reduce exactly this; the bottom line is their sum must be exactly 100%).
The advanced score meter only displays correctly in this case! We have applied a control field in the ‘Slicers’ column, if its value is 100% than everything is all right.
Thousands of variations can be imagined by modifying the percentage, always use the appropriate ones for the situation / task.
This advanced score meter chart looks spectacular as a part of a complex dashboard!
We have created the ‘MIN’ and ‘MAX’ columns that we have made intervals using the rules of running total. Based on the current ‘Slicers’ settings these are the following: 0-10%; 11%-30%; 31%-60%; 61%-80%; 81%-100%.
Using Arrays – The Frequency Formula
The usage of array formulas not specifically for begginers, definitaly requires advanced knowledge and we will talk about them in detail for they are important in this solution. We set the indicator value to 70% and the array formula will decide that in the ‘FREQUENCY’ colomn what value will correspond to this percentage.
In our case between 61% and 80% that’s why the number 1 indicator can be seen there.
How do we get this result? Highlight the cells as we do on the picture. After this click on the formula bar and write in the formula. DO NOT press enter!
But instead use the <Ctrl> + <Shift> + <Enter> combination! Then the results will be automatically enterend in all cells. And you will notice that the formula appears with curly brackets around it.
Assigning TEXT to values
There’s only one thing left to do and this is to display the text assigned to the percentage values on the advanced score meter chart. We would like to accomplish that the text of the category appear in a text box. We have to bring out the good old VLOOKUP formula.
The first parameter of the formula will be the ‘1’ indicator (located in the FREQUENCY column), the second parameter in turn will look for text assigned to the given interval in the ‘TEXT’ column. And we are all done!
Thank you for staying with us today, the above detailed chart can be downloaded from this link. Just play with our advanced score meter chart!