Quick Steps to Make Effective Data Visualisations

Original.png

This is an example of the default settings for a bar chart in Excel. This is functional but could be far more effective; here are my main steps I use to create quick, effective data visualisations.

  • Remove chart borders.
  • Show data in a useful order (alphabetically, or ranked, or some other important order).
  • Keep gridlines and reduce their colour, or remove them entirely.
  • If you remove gridlines, consider adding data labels.
  • If data labels have been added consider removing y-axis labels.
  • Add axis titles where the values shown aren’t inherently obvious.
  • Reduce the colour used in the chart.
  • Highlight the important data points.
  • Use white space effectively, but don’t leave excessive amounts.
  • Where possible use a chart title that helps the user see the points that you’ve highlighted and their context. Annotate any other points you’ve highlighted, why are they interesting?
  • Use colour to create a relationship between your annotations/title and the highlighted data points.

These steps will help you turn your default Excel charts into impactive and attractive data visualisations:

New.png

Advertisements
Data Visualisation Excel Examples How to

HOW TO… Excel: Create a hierarchical series of drop down boxes

As a result of prodding around the excel subreddit I found a query from user innocuous_gorilla about creating drop downs that are dependent upon each other. Due to working at NHS England where we have a massive array of organisation types and hierarchies, I quickly learnt how to do this! For example, in NHS England we have different regions, sub-regions and commissioning groups which tend to be massive long lists if un-filtered, so it makes it hard to select one in a drop down where all options available. Using the following method, you can make a set of drop downs that all depend on a master drop down(s), so choosing a region or sub-region filters the commissioning group drop down box to only those groups that belong to the chosen parent region.

Here’s the link to the workbook example I’ll be walking through in the following tutorial, where we’ll deal with the example brought up by innocuous_gorilla.

We’ll tackle the workbook a section at a time, where I’ll describe what each part is for and how it works.

Part 1

Pic 0

This is our named range index. We’ve included this to make it a bit easier to track what named ranges are available within the spreadsheet, and what each named range is responsible for. I’ll be referencing these later as these are used with our combo boxes.

Part 2

Pic 1.PNG

Here we’ve simply typed a list for each of our options. The product list will be used to drive our first drop down (all drop downs used in this example are Combo Boxes), so we’ve saved this as a named range (follow this example in my previous tutorial to understand how to make a named range if you are unaware).

Part 3

Pic 2

Next up to the right of these lists, we’ve got a couple more lists which separate out the potential options that each of our products has available, note these are all differing lengths which makes it a bit harder to deal with when creating our dynamic lists.

Part 4

Pic 3

Next, we have a table giving us the maximum number of each option available to each of our products. This will allow our colour and size lists to populate dynamically and be the correct length. This can be generated by counting the tables in part 3 but were typed this time as it was easy to do so.

Part 5

Pic 4.PNG

Now we have our combo boxes – these have been populated using our named ranges. Each of the ‘List’ named ranges informs the corresponding combo box, and the ‘LinkCell’ named ranges are the Cell linkage for each corresponding combo box (if you’re lost check my other tutorial that covers how combo boxes work). The grey cells to the right of the drop downs are our ‘LinkCell’ and ‘Selected- -‘ named ranges. The ‘Selected’ named ranges have the following formulae:

=IFERROR(INDEX(ColourList,ColourLinkCell),INDEX(ColourList,$E$20))

This is indexing our ‘List’ named range and selecting the colour that corresponds with the linked cell. If this ever causes an error (which will happen when a lower-level drop down (in this case our colour and size drop downs) has a linked cell that is out of the range of the index, which occurs when changing between products if the product’s max number of colours/sizes is less than the previous selected product), we simply select the max possible option within the list (E20 is our max number of colours of the selected product).

Part 6

Pic 5.PNG

Finally we have our Colour and Size lists. The yellow cell to the left (both follow the same structure) has the following formula:

=INDEX($B$14:$B$16,ProductLinkCell)

Here we’re simply checking the table of maxes for the max number of the colours of the selected product, by indexing the column of colours based on the chosen product row number.

The counting numbers on the left of each list have the following formula:

=IFERROR(IF(E21+1>$E$20,"",E21+1),"")

This counts to the max number of the currently selected product for colours/sizes. We dragged this down to the potential largest max number for each option (4 for colours and 3 for sizes). If there’s an error or the number is greater than the max, we instead show a blank (“”).

The lists themselves are made up of the following formula:

=IFERROR(INDEX($H$3:$J$6,$E21,MATCH(SelectedProduct,$H$2:$J$2,0)),"")

This indexes the tables in part 3, looking for the row number corresponding to the cell to the left of the current cell and looking for the column corresponding to the currently selected product. If an error occurs, we show a blank (“”).

Part 7

These lists are then turned into the ‘List’ named ranges which use the following formula:

=Sheet1!$J$21:INDEX(Sheet1!$J$21:$J$23,Sheet1!$I$20)

This begins an array at the start of the list, then indexes the entire list to end the array at the maximum possible number for the selected product.

Hopefully understanding how each element works will help you to break this down and apply it to your own work! Thanks so much for reading and I hope this helped. If you have any further queries please ask in the comment section below.

How to How to... Excel

HOW TO… VBA: Compare VBA Combo Box Values with Their Lists

Preamble

So, a recent issue I’ve had using VBA to create UserForms to handle data input is data validation. Throughout my UserForm I’ve included a variety of Combo Boxes that contain lists of information the user can select. In most instances I would like the user to have inputted exactly one of the options within the drop down provided with the combo box; VBA has its own property to handle whether Combo Boxes require an exact match – the MatchRequired property. While this can be used, it creates an issue where if a user leaves a combo box unfilled the combo box will say there is a no match and therefore an error – locking the user out of the form until they input something that is a match. My UserForm is used for modifying and inputting requests our team has had, therefore certain Combo Boxes won’t be able to be filled until a request itself has been completed, with fields such as ‘Quality Assured By:’ only being relevant when the work done has been QA’d. In this instance this Match Required error would cause the user to be forced to enter incorrect data to proceed with the form if they’d accidently clicked into this combo box.

To get around this I turned off the MatchRequired property and created my own data validation Subroutine to check if the value held by each combo box matches a value within the combo box’s list. To do this we will need to create an array of arrays and then iterate through those arrays, something that’s fairly straight forward in more robust languages like Python and C# but a bit more finicky in VBA. Below is some example code which we’ll go through step by step.

Code

Sub ExampleDataValidation()
Dim failBools(0 To 2) As Boolean
Dim fail As Boolean
Dim errorMsg As String
Dim tempArray() As Variant
Dim comboBoxes(0 To 2) As Object
Dim comboValue(0 To 2) As String
Dim comboList(0 To 2) As Variant
Dim i As Integer, j As Integer
Dim matchChecker As Boolean

fail = False

For i = 0 To 2
    failBools(i) = False
Next i

errorMsg = "The following fields are incorrect:"

Set comboBoxes(0) = UserForm1.ComboBox1.Object
Set comboBoxes(1) = UserForm1.ComboBox2.Object
Set comboBoxes(2) = UserForm1.ComboBox3.Object

For i = 0 To 2
    comboValue(i) = comboBoxes(i).Value
    ReDim tempArray(0 To comboBoxes(i).ListCount - 1)
    For j = 0 To comboBoxes(i).ListCount - 1
        tempArray(j) = comboBoxes(i).List(j)
    Next j
    comboList(i) = tempArray
Next i

For i = 0 To 2
    matchChecker = False
    
    For j = 0 To 2
        If comboValue(i) = comboList(i)(j) And comboValue <> "" And matchChecker = False Then
            matchChecker = True
        End If
    Next j
    
    If matchChecker = False AND comboValue(i) <> "" Then 
        failBools(i) = True
    End If

Next i

For i = 0 To 2
    If failBools(i) = True Then
        fail = True
        If i = 0 Then
            errorMsg = errorMsg & vbNewLine & "ComboBox1"
        ElseIf i = 1 Then
            errorMsg = errorMsg & vbNewLine & "ComboBox2"
        Else
            errorMsg = errorMsg & vbNewLine & "ComboBox3"
        End If
    End If
Next i
    
globalVars.dataValidationFail = fail

If fail = True Then
    MsgBox errorMsg
End If

End Sub

We’ll separate each element of this code into distinct chunks and then explain each process line by line. Some basic level of VBA is assumed like how For loops work and how arrays work.

First, we start by declaring our variables:

Dim failBools(0 To 2) As Boolean : Here is our Boolean array, this is how we determine which 
                                   combo box has an incorrect value when constructing an error message. 
                                   We’ve used 0 to 2 as our indices because we are only going to be looking at 3 combo boxes (0, 1, and 2). 
                                   The upper bound will need to be set to however many elements your going to be checking for this and all other arrays.

Dim fail As Boolean: A bool that will be triggered if any of the failBools array are true, we’ll pass 
                     this to a global variable that can be accessed by other subroutines to allow other subroutines to respond
                     to the results of this subroutine.

Dim errorMsg As String: Here’s the string we’ll store our error message in.

Dim tempArray() As Variant: Here’s a temporary array we’ll use to construct and pass the lists of each 
                            of the combo boxes to our array of arrays.

Dim comboBoxes(0 To 2) As Object: An array of objects we’ll store our combo box objects within. 
                                  This allows us to more easily iterate over each of the objects (less typing and cleaner code!).

Dim comboValue(0 To 2) As String: An array of strings, for holding the values of each of the combo boxes.

Dim comboList(0 To 2) As Variant: Here’s the main event, our array of variants (a data type that 
                                  VBA determines on the fly) that will act as our array of combo box lists.

Dim i As Integer, j As Integer: These are our two integers we’ll be using to iterate through our For loops.

Dim matchChecker As Boolean: This bool will be turned to True if we find a match between 
                             a comboValue and a comboList.

Next, we need to set-up some of our variables values:

fail = False

For i = 0 To 2			This section is just resetting our fail Booleans before the 
    failBools(i) = False        subroutine begins, so we don’t accidentally store old values.
Next i

errorMsg = "The following fields are incorrect:" : This is just setting up the start of our error
                                                   message.

Set comboBoxes(0) = UserForm1.ComboBox1.Object	Here we are setting the comboBox
Set comboBoxes(1) = UserForm1.ComboBox2.Object	array to each of the combo boxes in
Set comboBoxes(2) = UserForm1.ComboBox3.Object	our UserForm.

Now we construct our first For loop.

For i = 0 To 2 
    comboValue(i) = comboBoxes(i).Value		The combo box value at index i is 
                                                stored at index i of our combo value array.

    ReDim tempArray(0 To comboBoxes(i).ListCount - 1)	Here we set our tempArray up to the 
                                                        correct Upper Bound of the current list. Combo box list count starts at 1, 
                                                        whereas we start at 0 so we need to take 1 away from the 
                                                        total count to create the correct length array.

    For j = 0 To comboBoxes(i).ListCount – 1		Now we iterate through the list of the combo box at index i.
         tempArray(j) = comboBoxes(i).List(j)	        We’re setting each element of tempArray to be equal to the corresponding element in the combo box’s list.
    Next j

comboList(i) = tempArray	Finally, we set the comboList at index i to be the newly constructed array.
Next i

Our next For loop will be where we determine whether a combo box value matches one of the values within that combo box’s list.

For i = 0 To 2					
matchChecker = False	At the start of each loop we reset the matchCheker bool.
    
    For j = 0 To 2
        If comboValue(i) = comboList(i)(j) 	If there’s a match between the current combo box value and the value of the same combo box’s list’s j-th index AND 
        And comboValue <> "" 			The combo box value isn’t blank AND
        And matchChecker = False Then	        The matchChecker hasn’t already found a match THEN
            	matchChecker = True             Set matchChecker to True as we found a match.
        End If
    Next j
    
    If matchChecker = False AND comboValue(i) <> "" Then	If the checker couldn't find a match and the comboValue isn't blank, 
        failBools(i) = True			                then set the corresponding failBools index to True.
    End If

Next i

Our final For loop will construct the errorMsg based on which failBools were triggered.

For i = 0 To 2
    If failBools(i) = True Then
        fail = True	This fail variable is set if any failBools were triggered so we can change a global bool after this loop.
        If i = 0 Then
            errorMsg = errorMsg & vbNewLine & "ComboBox1"	Using the index we can work 
        ElseIf i = 1 Then					out which combo box is
            errorMsg = errorMsg & vbNewLine & "ComboBox2"	causing our issue, adding it to 
        Else							the error message if it
            errorMsg = errorMsg & vbNewLine & "ComboBox3"	was triggered.
        End If
    End If
Next i

Finally, we trigger a global bool variable and create our error message. The global bool can be used by other subroutines to work out whether they need to continue as a result of this data validation subroutine.

globalVars.dataValidationFail = fail	globalVars is simply the name of the module 
                                        we are using to store all global variables in
If fail = True Then	                the project.
    MsgBox errorMsg
End If

This subroutine can be run when leaving the UserForm and committing the inputted data to the WorkBook. Upon any failures, we can prevent the UserForm from continuing to exit until our user has either removed the incorrect data or corrected it.

Thanks so much for reading, I really hope this helped. If you have any further queries regarding this method, please ask in the comments section below.

How to How to... Excel how to... vba

Starting Back at Work

At the start of the month, I started back with my old team at NHS England working as (you may have already guessed it isn’t clinically based) a data analyst. I’m incredibly pleased to be back as my old job here was my stepping stone into the world of data analytics and Excel. Here I learnt how to leverage Excel to utilise a massive amount of its potential, use VBA to automate my work processes, create dashboards and create effective data visualisations. In an effort to get better at my job I pored over blogs like storytellingwithdata.com, visualisingdata.com, and darkhorseanalytics.com (to name a few) spent countless hours adjusting and tweaking my work to better suit my users and strived to be better at every possible occasion.

The NHS is close to my heart and I deeply believe in the good that it does for our country, which may be a large reason why I have felt so compelled to work as hard as I have to improve myself and improve my work for the NHS. Within my first two weeks I’ve already pushed myself to understand and write queries in SQL to manipulate one of the NHSs largest data sets, SUS – a skill that I’ve been itching to acquire due to its necessity as a data analyst. We’ve also been seeing a large push in the NHS England analytical community to explore data science techniques, so rounds of training for SAS, Python and R have been offered (of which I will be attending none, but mainly because of limited places and because I’ve been learning Python in my own time for data visualisation – look forward to my next blog post most likely covering using Python to create data viz!).

Ultimately due to this, as it may already be evident for those of you that are regular visitors to the site (hopefully a non-zero number!), I will be ramping down content production. The last couple of months have been slow periods for me relative to the rest of the year, due to interviews, moving closer to work and a holiday to Florida with my parents, but I’m hoping to keep releasing new content at least once a month. Thanks so much for those of you who have been keeping up with my content and I really do hope it’s helped!

General

Analytics and Ethics

Upon the enlightening release of the Cambridge Analytica exposé that revealed the company’s underhanded tactics it utilises to influence and manipulate people with a combination of data analytics, espionage, and ‘honeypotting’, I think it’s incredibly important to discuss how the analytical community needs to ensure a commitment to honest and unbiased analytics, and ethically sourced data.

It should always be on the analyst’s mind about how their work may be used and interpreted by all parties who consume the analysis that they’ve produced. This is underlined by the importance of clarity in data visualisation. Producing data visualisations that have little to no confounding elements (such as dual axis and scales that don’t start at zero) is important to ensure that our analysis can be correctly and easily interpreted by our audience, and minimise the risk that our audience draws false conclusions from the visualisation. If there are any particular nuances to the data that may cause an incorrect interpretation of that data, it’s important that it is made clear to the user and isn’t allowed to cloud their judgement. Techniques like visualising uncertainty are therefore incredibly useful when showing analysis that includes things like forecasting or correlation analysis that rely heavily on confidence intervals.

Evidently when it comes to enacting these methods, to create clarity in data visualisations, there is a wide scope for interpreting how this should be carried out. This creates the subjectivity of data visualisation which opens a gap which is ripe for ethical dilemmas! One thing that we, as analysts, have to be salient of is making sure we don’t infer our biases in the analyses we carry out. When ensuring our audience don’t mistakenly draw false conclusions from the data we’re presenting to them, we need to make sure that these ‘false’ conclusions aren’t simply the conclusions that we don’t want them to make because it doesn’t align with our world view. This is especially important in the land of data journalism. The vast majority of publications come with a litany of biases; which side of the political spectrum they lean toward, their views on particular hot-button topics, and whomever sponsors their content. It’s important that the data being presented in these publications isn’t being twisted to create misleading and false narratives that align with the publication’s biases. Doing so is morally reprehensible and incredibly manipulative, but we see it time and time again, and the general public that aren’t well versed in statistics and good data visualisation will fall for these cheap tricks again and again.

This is simply a microcosm of the scope for this topic. It’s obvious that despite my misgivings, analytics will still be used for a variety of dishonest and malicious practices – hell, most marketing companies use analytics to make sure us chumps in the public are enticed as much as possible by their product/message/service and that’s not likely to stop any time soon. Hopefully, in time and likely after a large enough scandal with big data (e.g. accounting and the Enron scandal influencing the profession’s push for a greater emphasis on ethics, and physics with the Manhattan project) we’ll solidify a code of ethics that all analysts will strive to follow.

Write-Up

HOW TO… Excel: Create a Dynamic Data Range for a Chart

Continuing the theme of automatically updating charts, we’re going to look at how to make a chart deal with a dynamically changing data range. This is useful for situations where you have multiple measures that each have a different number of independent variables (the variables plotted on our x-axis, which was the month of the year in our last example). If you use the method as lined out in the last tutorial for this you will find that the chart updates automatically, but the number of variables shown on the x-axis will not change. To solve this, we will need to use Named Ranges; OFFSET; and COUNT to produce the ranges that will feed the automatically updating charts.

We’ll be continuing with the workbook used in the last tutorial (downloadable here) for our example.

Step 1.

Step1 Example

First, we’ll need to create our Named Ranges. We will need a named range for our horizontal axis labels and for our data. We’ll name these ‘ChartDateRange’ and ‘ChartOrgRange’. Now we’ll need to construct the OFFSET function that will be used to determine the ranges of the data dynamically. For both named ranges we will be using almost the same formula, just pointed at a different reference cell. Our ‘ChartDateRange’ will be as follows:

=OFFSET(Charts!$C$30,,,,COUNT(Charts!$C$31:$CF$31))

The first parameter is the ‘Reference Cell’; C30 – the starting cell of our date ranges. We will then pass through the next 3 parameters which are as follows:

  1. The number of rows to move from the reference cell.
  2. The number of columns to move from the reference cell.
  3. The height of the range starting from the reference cell.

Which means the final parameter with our COUNT function determines the width of the range starting from the reference cell. We count all the data within the data range; in this instance I’ve extended the range of the COUNT to include extra columns. We will be extending our data range further out to allow the chart to handle extra data in the backing sheets and adding in additional months of data.

Our ‘ChartOrgRange’ is then simply:

=OFFSET(Charts!$C$31,,,,COUNT(Charts!$C$31:$CF$31))

We’ve just shifted the reference cell down one row to C31 in this case.

Step 1b.

We’ll quickly extend the data ranges of our back sheets and our INDIRECT named range ‘SelectedData’ to also cover the new extended data range. We’ve extended the back sheets to column CF and made the blank data cells show an error by using:

 

=NA()

 

This is necessary to prevent us counting these cells as they will show as 0s when not errors.

 

To match this, we’ve extended ‘SelectedData’ to include columns CF, so the formula is now:

 

=INDIRECT("'"&'Output Sheet'!$H$16&"'!$C$7:$CF$16")

 

We also need to change our chart’s data table to have the formula:

 

=INDEX(SelectedData,OrgNameLinkedCell,MATCH(C$30,AverageTimeData!$C$6:$CF$6,0))

 

We’ve changed the MATCH function to now look at the range C6:CF6 in our back sheets. We then need to extend the data table all the way across to column CF too.

Step 2.

Step 2 Example

Now we can set the chart to look at these Named Ranges instead of the actual data range.

Right click the chart and go to ‘Select Data’. First, we’ll assign a new series data range, so click the ‘Legend Entries’ option for our current series then select ‘Edit’. Change the ‘Series values’ to:

=Charts!ChartOrgRange

It’s important to include the worksheet reference ‘Charts!’ as otherwise the chart will not accept the new input.

Next, we’ll change the horizontal axis labels range, click the ‘Edit’ button for ‘Horizontal (Category) Axis Labels’. Change the ‘Axis label range’ to:

=Charts!ChartDateRange

Once again, make sure you include the worksheet reference ‘Charts!’.

Step 3.

We can now add in new data to test our changes. To showcase this, we’re going to add in some new dummy data to just one of our back sheets; ‘StaffData’ in this case.

To achieve this, we’ve added in 2 more months of data using:

 

=RANDBETWEEN(20, 500)

 

This generates some random numbers for us between 20 and 500. To ‘freeze’ these values we’ll just copy them and paste them as values.

Once this is done you should be able to change freely between the measures and note that for Number of Staff we have data all the way up to May-17, and for the other two measures they only extend up to Mar-17.

And that should be it! This method is also especially useful for automation of Excel reports. If you set up the back sheets to automatically pull their data from wherever their data source is, utilising this method will allow your front end to also automatically update meaning you will have more time to check your data and free up your time to do more proper, less tedious work!

You can download the completed workbook here.

Once again thanks so much for reading and I really do hope this helps. If you have any comments or queries about this tutorial, please do reply below or email us at staticinterest@gmail.com.

How to How to... Excel

HOW TO… Excel: Create an Automatically Updating Chart

Following on from our last series of HOW TO… Excel, we’re going to expand the concepts used to create the dynamic data table and create another sheet for the same workbook that will be the home of an automatically updating chart! Unlike pivot charts, this will be able to update as soon as new data is inputted into the data backsheets, and will be connected to the same ‘linked cell’ as our previous combo box, so the changes will be persistent throughout the workbook. This will allow us to create a consistent and pleasing user experience for whom ever will be using the tool.

We’ll be using the same workbook as the first tutorial so if you want to follow along please do download it here.

Step 1.

We need to set-up a measure input list and the linked cell used for our combo box as named ranges to make it easier to copy and paste the combo box and reference these ranges throughout our workbook.

Go to ‘Formulas‘ -> ‘Define Name‘ and define two new names, one pointing at ‘Output Sheet’!G16 (our linked cell) and the other at the range ‘Output sheet’!A16:A18 (our input list). We’ve called these our ‘MeasureLinkedCell’ and ‘MeasureInputRange’ respectively. These can be named however you would like but its important that it’s relevant to the task its being used for to make them easier to unpick for yourself and other people in the future.

Step 2.

Now we will set up a combo box using these two ranges (in this example we are going to be changing the existing combo box used for our ‘Output Sheet’). If you can’t remember how to do this please check the earlier tutorial as it covers this.

Step2

We’ll now create a new sheet to house our chart(s). We’ve called it simply ‘Charts’ in this example. Copy the combo box to this sheet; as we’ve used named ranges the links should remain intact to the old linked cell and input range. If you change the selection of this new combo box, it will also change the combo box on the other page as they are both linked to the same cell.

Step 3.

We will also need a new combo box to be used for our organisations as showing all 10 organisations in this case would create a very unwieldy chart. In this example we’ll be showing how to set-up a line chart that will drill down to different organisations and measures. Utilising the principles from this tutorial you should be able to create any type of chart you’d like, and tailor pick the data to create the chart.

Again, we will set-up some named ranges for our input range and linked cell for our organisation list. Due to the increasing number of lists we are accessing it may be worth moving these to their own sheet to create a more manageable workbook. We’ll be creating a new sheet called ‘Lists’ to do this and moving our previous named range references here too.

List Sheet 1

Side note: Our old linked cell (‘Output Sheet’!G16) for our output sheet will need to point to the named range now so that our output sheet continues to work.

Create the new named ranges for the organisation name list and the organisation name linked cell, in this case I’ve named these ‘OrgNameInputList’ and ‘OrgNameLinkedCell’. Back on the ‘Charts’ sheet we’ll create that new combo box and linked it to these named ranges. So far you should have a new Charts sheet with two combo boxes, one linked to measure names and another linked to organisation names. Here’s what I have so far, with some formatting at a basic level.

Charts Sheet 1

Step 4.

Now we need a dynamically updating data table that will take the input from the linked cells of these combo boxes to create the data array that will feed the chart on this sheet. With our named ranges and the INDEX function we can create some nifty looking formulae that retrieve the organisation name and measure name based on the linked cells. So to get the organisation name we need a cell with: =INDEX(OrgNameInputList,OrgNameLinkedCell))
And for the measure name:
=INDEX(MeasureInputList,MeasureLinkedCell)
To get the sheet name for our measures we’ll add in another section to our ‘Lists’ sheet with a measures sheet name list as follows:

List Sheet 2

Then we can add in another cell to find the sheet name with the following INDEX function:
=INDEX(Lists!$E$2:$E$4,MeasureLinkedCell)

Step 5.

Now to create the dynamic table it’s the same as in the previous tutorials using whatever methodology you would prefer (using INDIRECT, OFFSET or INDEX with/without the master data back sheet, see my other tutorials for more info). Here we’re going to use the INDIRECT method to create this dynamic data table. We’ve already got an INDIRECT named range that looks at the chosen data back sheet, which was
=INDIRECT("'"&'Output Sheet'!$H$16&"'!$C$7:$Z$16")
where H16 was our selected measure sheet.

So, we can create the data table to look as follows:

Charts Sheet 2

Using the formula
=INDEX(SelectedData,OrgNameLinkedCell,MATCH(C$30,AverageTimeData!$C$6:$Z$6,0))
where C30 is the reference to our dates row and AverageTimeData!C6:Z6 is the reference to the dates list that all the data back sheets utilise.

We don’t need to do a MATCH for the row number as all of our back sheets use the same list of organisations, so we can use the Linked Cell as that is the row number of the chosen organisation.

Step 6.

We’ll now create a line chart that uses this dynamic data table. Highlight the data table, and go to ‘Insert‘ -> ‘Charts‘ -> ‘2D Line Chart‘ to create the line chart. If you now use the combo boxes to select different combinations of measure and organisation you’ll see that the chart updates in response. The next steps are to tidy up the chart to make it more readable and understandable for our users.

Step 6a.

First, we will make the chart title update with the change of measure and organisation name. We will need to create a chart title in another cell that will be referenced by the chart title. In a cell nearby our data table (H27 in my case), add a formula such as this:

=$C$27&": "&$C$28

Where C27 is the chosen organisation name and C28 is the chosen measure name. This will create an output that looks like this: “Organisation: Measure”. We use ampersands (&) to allow us to mix strings (sections of formulae surrounded by quotation marks “”) and cell references. Now we need to link the chart title to this cell. Click into the chart title, navigate to the formula bar and type

=Charts!$H$27

Or click into the relevant cell that contains the new chart title after typing ‘=’.

Step 6b.

Next, we need to create a new vertical axis title to contextualise our data. For this we’ll need another list in our ‘Lists’ sheet that will contain the units of measurement for each of our measures. For this sample data set we need Hours, Units of Product (or just ‘Units’ for short) and No. of Staff for Average Time Taken, Total Throughput and Number of Staff respectively. We’ll then create a link to this list that provides us with the unit of measurement for the chosen measure using the formula:
=INDEX(Lists!$G$2:$G$4,MeasureLinkedCell)
where Lists!G2:G4 is the range for the ‘Units of Measurement’ list. We’ll now create a vertical axis title by selecting the chart, clicking the green plus that appears in the top right, navigating to Axis Titles and adding a Primary Vertical Axis Title.

Charts Sheet 3

Now click the axis title that appears, navigate to the formula bar and type:

=Charts!$H$28

Or click into the relevant cell that contains the axis title after typing ‘=’.

We should now have a working dynamic chart that updates the title and axis title when the measure and organisation name get changed.

You can now adjust the chart to your liking (preferably following good data visualisation principles!) but functionally everything should be working fine.

Next time we’ll discuss using OFFSET to create a dynamically adjusting data range for the chart so that it will automatically update when new data is added to the data set and creating methods to drill down into our data using different criteria.

As always thanks for reading, if you have any comments of your own or ways you’d do this instead please do let me know in the comment section below!

The final version of the workbook used is available here.

How to How to... Excel

Florida Ticket Price Comparisons

I’m going on holiday soon! I’m headed to Florida with my parents and being the cost savvy Brits that we are we’re always on the hunt for a bargain. We wanted to go do some of the Theme Parks (we settled on Disney and/or Universal Studios) and went about finding the cheapest ticket rates we could. During the internet hunt my Mum kept using the Calculator on her PC to do things like exchange rate conversions and to calculate price differences – but, being the Excel monkey that I am I proposed a much better solution: let’s whack it all in Excel and let me make some pretty charts!

We wanted to do around 4 days in total either all in Disney, all in Universal or 2 days in one and 2 in another. If it was far too expensive just 2 days in one of them may have been necessary. For our 2 day and 4 day prices we’ve used the listed price on the American site portal for Universal Studios Orlando and Walt Disney World. We also found that there was pretty good deals for a 14 day ticket for both parks on American Attractions with an extra £8 off by using the code MSE8 in the checkout (found on Money Saving Expert). The 14-day tickets would afford us a touch more flexibility if we really wanted to do more than 4 days. Due to parking prices for both parks, we’ve also looked at how expensive it would be for one person to buy an annual pass (which would be especially useful as my parents go to Florida often) as these annual passes often give free parking as an extra. We also found that the Universal Studios annual pass gives you 15% off on multi-day passes bought at the front gate, so I’ve factored that into our calculations (one issue is that currently Universal Studios are offering up to $20 off for buying online, but I couldn’t find the price before the discount to use as the ‘at-the-gate’ price so this may be a little underestimated).

So, after calculating the prices using an exchange rate of 1.40 USD to 1 GBP and adding sales taxes to the American prices, here are our findings – presented using lovely bullet charts (click here for my first post on bullet charts if you want to find out more):

Price Per Visit

Price Per Visit calculated using the total price for 3 people.

Total Price

The cheapest options are to buy a 4-day pass per person for Universal Studios, but the option that provides the most flexibility and I think for us the best value (I just love how vague value is as a concept) is to get the annual pass for one person and 14-day tickets for the other two people. For 4 visits, it’s about £30 more expensive per visit than the 4-day tickets plus annual pass, but as soon as we go a 5th time it becomes better value at £148.63 per visit. So, it saves us from being locked into only going 4 times; offers us discounts in general around the parks; it gives us the option to go the City Walk and restaurants like the Hard Rock Café or go to the cinema (which we would also get discounts at), and if we’d like to go to the parks after going for food for example, we could.

Here’s a link to my spreadsheet. I’ve done the calculations for up to 3 people, but if you wanted to piggyback off my work it wouldn’t be too hard to add in the calculations for more than 3 people provided you’re comfortable using Excel.

If you have any of your own money saving tips or sites please do mention them below, and if you find any better deals definitely comment, we’ve still got a couple weeks until we need to purchase the tickets, so it’ll be really useful for us too!

General Write-Up

Nintendo Console Dashboard Update

I was taking a glance around the Excel tag in the WordPress Reader, when I came across the following blog post about Linked Pictures in Excel.

This was a little bit of a revolution to me; when I made the Nintendo console dashboard I struggled to work out a passable fix for including dynamic pictures in VBA, ultimately deciding to scrap the code and just go with no pictures. Now that I know how to use Linked Pictures with named ranges to allow dynamic updates I could easily go back and update the old Dashboard!

Dashboard With PicsWhere it’s appropriate I’ll definitely use this method elsewhere, it saves busting out VBA and causing a potential hiccup when a user doesn’t enable macros which is always a concern I have when using VBA.

The link to the blog post above is a tutorial for using this method if you’d like to find out how to do it yourself!

The file is available here if you want to prod around yourself.

 

Excel Examples

Game Dev: Balancing Adventurer Guild Simulator

For the past 2 weeks I’ve been working on developing a game in my spare time: it’s tentatively titled ‘Adventurer Guild Simulator’, as it’s about running an adventurer guild in a fantasy setting (think Dungeons and Dragons). In the game you’ll be hiring adventurers to go on quests to raise the reputation of the guild and to thwart the plans of villains within the area. Adventurers will be fighting monsters, solving the secrets of locations, gaining experience and collecting riches on your behalf. The game is being developed in Unity, which I can highly recommend to any budding game developers!

In an effort to research game development tips and tricks I’ve been devouring the Game Developer Conference (GDC) videos on Youtube. These are lectures and seminars held by leading game developers within the international game dev community, which are full of interesting anecdotes and lessons to be learnt from their past tribulations. One that left an impression was about balancing a game using statistics and Excel, right up my alley! It was run by Ian Schreiber, presenting the summary of his college-level course on game balance.

While I’ve been mainly using dummy figures for the game as of yet, I’ve taken my first steps into balancing some aspects of the game. This is a work-in-progress, so these figures won’t be set in stone, but for now here’s my attempts at balancing the levelling curve of adventurers within the game, using the average number of encounters per quest (mission) to drive how I want the game to be balanced. As the highest duration an adventurer can be temporarily hired for will be 6 days within the game, I wanted them to level up around once if they were hired with the max duration. With this in mind, I’ve tried to get to an average of around 5-6 missions per level up, where the adventurer is on a mission that is level appropriate (i.e. matches their level).

The experience points (XP) required to level were worked out as follows:

=IF(Level<=10,MROUND(300+150*(Level^2.05),150),MROUND(300+150*(Level^2),150))

So, broken down it’s 300 as a base, plus 150 * (Adventurer’s level)x, rounded to the nearest multiple of 150. If the level is below 10 the exponent is increased by 0.05 (this was done to try and smooth the levels below 10 to closer to 5 missions per level up for x = 1.6 and x = 1.5).

The average number of missions required to level up was worked out with these formulae:

Average Number of Encounters per Mission:
= 35% (the chance of an enemy encounter) * 7 (the number of times during a mission an event occurs)

XP gained from a monster at X level:
=MROUND((Level^1.2)*35,35)

So it’s (Monster Level)1.2 * 35 as a base rounded to the nearest multiple of 35.

Finally, average number of missions to level up:
=XP required for next level / (XP gain from a monster of level X * Average number of encounters per mission)

To make it easier to interpret, I used the following graphs as I moulded the formulae above to fit the requirements. The XP required per level that stays closest to 5 is a choice between XP required(Power 1.6) and (Power 1.5), and as Power 1.6 stays closer to 5 below level 10, I think I’m going to use that as my calculation for now!

 

Number of Encounters Per LevelXP Requirement Curves

Excel Examples Game Dev