Using functions to make dynamic charts (1)

Introduction

In the last post we created our Garden Bird Tracker and used two tables contained in one worksheet as the source data for our two charts.  We made some formatting changes to the first chart, which shows the number of each species on each day of the week. We created a second chart which shows the totals for each species for the whole week, and then combined two chart series types so that we had a mixed bar and line chart. After that, went on to customise the result so that we had an image of each species arranged across the top of the chart area and positioned above the corresponding name and data bar:

In this post, we are going to use the IF function to get the birds to hover just above the data bars or perch on them, and adjust their position as we enter data into the Data sheet tables which are the source data for our charts. Here's what it'll look like:

The IF function explained

The IF function is one of a range of logical functions in Excel, and will return one of two results to a logical argument. In practice, you ask the function whether a reference or value meets a criteria that you specify, and it will return a result of either True or False. In computer science, the result of this logical test is a Boolean value - True has a value of 1, whereas False has value of 0. Here's an example of the IF function applied to our Bird Tracker:

As you can see, we are asking this:

Does the value in cell A2 = "ROBIN"? If so, tell me it's "TRUE", and if not, "FALSE". It does, so our result is "TRUE". Note that the words "TRUE" and "FALSE" have been entered by me in quotation marks. The first word is what to return if the argument is true, the second if it is false. You can specify any text that you wish for the two results. You can see that I've changed the two possible results in the IF function in cell B3 to "YES" or "NO", and that the result is "NO" because cell A3 does not equal "ROBIN".

The True or False result of the IF logical test can be used in a formula to instruct Excel to perform a different action depending on the result, and more than one IF function can be combined. We will look at this in more detail in future posts. For our project today, we only need a simple "TRUE" or "FALSE" result, so we'll move onto the next stage.

Using the IF function in the chart source data

At the moment, our table of source data for Chart 2 looks like this, with the second column fixed at a value of 10:

The first thing we are going to do is delete all the values in the second column, and type this formula in the first cell in the range which is in the same row as Black Cap:

This formula is fixing the customised Series 2 line Chart at exactly 2.5 above the The corresponding Series 1 bar chart. As the values for the bar chart change, the formula will recalculate and become the new value + 2.5.

Now, copy the formula down to cell C9 by placing you cursor over the small square in the bottom right corner of cell C1 and dragging it down the column. You'll see this when the formula is copied:

Click on your reports tab, and you'll see that Chart 2 now looks like this:

This is pretty close to what we want, but we need to lower the + value from 2.5 to get the birds to hover just above the data bar. With a value of +1.1, it looks better:

If we look at the two species we haven't seen this week - Blackbird and Ringed Dove - we see they're hovering when we don't want them to. That's because our formula has added 1.1 to 0 and positioned them too high above the baseline of the Horizontal Category Axis. We're going to use the IF function to tidy this up by checking if the value in the table equals 0, and if so, do something other than adding 1.1 to that value. We still want to add 1.1 to the other values, and the IF, TRUE, FALSE argument will do this for us. here's the formula we will put in cell C1:

The formula is saying: if B1 = 0, then add 0.75 (this value places the image just above the baseline), otherwise add 1.1 to the value of B1. Copy this down to cell C9, and then you you'll see this:

The charts now looks exactly as we want it to, and the hover heights will adjust as we enter more data into our table, making it a semi dynamic chart:

I hope you enjoyed this post - contact me if you have any questions or need any help.

In the next post, we're going to add more functions so that the two tables we use for our charts update themselves as we enter information into the Tracker sheet, making the charts completely dynamic.