ARRAYS Part 3 - DATA VALIDATION and TEXT

Introduction

In the last post we looked at incorporating the functionality of the SUMPRODUCT function into a multi-criteria INDEX and MATCH look-up array formula. With our source data, we are counting text values (species names); this is fine if there are no spelling errors or unknown text in the source data. In this post, we will use data validation to eliminate the possibility of errors, and then look at how to display the result of the look-up array formula in a line of text.

Dynamic Data Validation

Data validation allows us to create a drop-down list of options, and therefore ensures that there can be no errors or free text entered in to the species column of the Garden Bird Tracker so that our tables calculate properly:

We will use the same technique to provide the criteria for our array look-up formula, and ensure that we can add more species to the list at any time without having to alter the source range for the list. 

To create a dynamic list that can be added to without re-defining the source of the drop-down, I've converted the list in my DATA sheet to an Excel table:

I've then named the table TBLSPECIES:

By default, Excel will name tables in sequence as Table 1, Table 2, etc. Giving the table a name that reflects its content makes referencing the correct table in formulas and functions a lot simpler.

I've then created a second table for M/F and named this TBLMF:

In the example below, I've applied a data validation rule in the cell to the right of SPECIES(highlighted in green), allowing selection from a List, and then used an INDIRECT function to set the source as a table, rather than a range of cells. The INDIRECT function is used to create a reference to range defined as a string of text - in this example the text is the name of our table, which has a dynamic range. The table range will expand and reduce automatically, so this reference will always include all the options in the table:

I've applied the same rule in the cell next to M/F, this time using the source reference TBLMF.

Array Formula Look-up

Using the same array formula that we developed in the last post, and changing the criteria references to the cells that contain the drop down-lists, we now have a two-click look-up to find the weight by species and male/female match:

Text

The final thing to look at is how we display the result of the formula. By inserting strings of text, an additional reference and an IF function into the array formula, the output can be displayed in a much more visually pleasing way:

The formula in the example above is in cell F5, and is entered as an array formula. I've then merged the output across cells F5:L5 and applied some text and background formatting.

Note that the text is surrounded by quotation marks("?") and that formula elements, references and text is joined with an ampersand (&).  This can also be accomplished with the CONCATENATE function (or  the CONCAT function in later versions of Excel), but  the formula will be significantly longer.

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

In the next post, an introduction to dynamic array formulas!

ARRAYS Part 2 - INDEX and MATCH

Introduction

In the previous post we looked at the SUMPRODUCT function, and then modified it to act as a SUM and a COUNTIFS function in the two tables of data we had created earlier. In this post, we're looking at the INDEX and MATCH functions, using them to look up values, and then combining these with part of the SUMPRODUCT function to perform multiple criteria look-ups.

Table 3

For this post, I've created a third table which lists the weight of the male and female of each species in the list:

INDEX

The INDEX function returns the value in a cell at the intersection of a row and column number in a given array or range of data. In the first formula below, the array is cells A1 to C21, and we are asking for the value in the cell in row 5 of column 1:

In the second formula, I have defined the array as a single column range (A1:A21), and then specified row 5. Although the function syntax is (ARRAY, ROW NUMBER, COLUMN NUMBER), indexing a single dimension array causes Excel to accept this as the second condition, and then takes the user defined row number as the first condition. 

MATCH

The MATCH function returns the row or column number of a specified value in a horizontal or vertical range:

This function can be used to supply the row condition to an INDEX function, and, combined, the two provide a look-up solution that replaces VLOOKUP and HLOOKUP and is not limited to finding values to the right or below the criteria in a range.

INDEX and MATCH 

In the example below, you can see the evolution of INDEX, then MATCH and then INDEX and MATCH combined:

The first part of the formula creates an array of data from cells A1 to B21. The MATCH function then finds the row number in the range A1 to A21 that matches the criteria in cell H9, which evalutes to row 5. Following this, we specify 0 for an exact match, and then finally specify that we want the corresponding value in column 2 of the array. Note that in this instance, I selected more than one column in the INDEX stage, so it is necessary to specify the column.

In the example below, I've increased the array to take in column C, and specified 3 as the column to return the value from:

Multiple criteria look-up 

In the previous post, we used SUMPRODUCT to find the number of species for each day of the week based on a range of logical tests that we coerced into an array of 1s and 0s. We can use the same principal with INDEX and MATCH to perform a look-up based on more than one criteria, and the value returned can be text or numbers (SUMPRODUCT can only return a numeric, calculated value). In the following example, the look-up criteria for the MATCH function is "1"; going back to the virtual array calculation that we coerced SUMPRODUCT to create, the calculated output where all the criteria evaluated as TRUE was "1", whereas all other calculations resulted in "0". Using this logic, and entering the formula as an array formula, the MATCH function will find the row where all the arguments are TRUE, and evaluate to "1" and supply this as the first condition in the INDEX function. The column has been set by providing a single column reference, which becomes the second condition:

The key to this formula is that it calculates across arrays of data; this formula will fail unless it's entered as an array formula by pressing CONTROL+SHIFT+ENTER (CSE). Note the curled parentheses that surround the entire formula ("{}"); these are generated by the CSE entry and are not typed around the formula.  In the first version above, MATCH criteria is set at "1", which will be the result of the array calculations of the logical tests; the following calculation uses the multiplication
operator. Notice the similarity of the interior of this formula to the SUMPRODUCT function that we used before:

 In the second example, the MATCH criteria is set at "2", and the operator is an addition; as we have two conditions that will evaluate to "1", it follows that the MATCH we are looking for is "1+1=2". This is simply to demonstrate how the arrays of data are handled, and it would be preferable not to use addition or subtraction as they are not necessary. Using division between the arrays and setting the MATCH criteria at "1" gives the same result as multiplication. In the final example below, you can see that you can add any number of calculations after the arrays you are calculating; as long as the output of all the calculations evaluates to 1, then the result will be identical:

  

This formula can be increased to include as many criteria as you require and will work on horizontal and vertical arrays, or a mixture of the two in a two dimensional array. As mentioned earlier, this formula can return a result from anywhere in a range of data, and is not restricted to looking to the right or below the criteria.

I hope you enjoyed this post - please get in touch if you have any questions or need any help.

In the next post, we are going to use Data Validation to create dynamic drop down lists as the criteria for this formula!

Arrays Part 1 - the SUMPODUCT function

Introduction

In the last post we used the COUNTIFS function to dynamically calculate the data for Table 1, and a simple SUM function in Table 2 for the weekly total of each species. In this post, we are going to use an unusual approach to achieve the same result, and look at how the SUMPRODUCT function can be adapted to a variety of unintended uses, based on array calculations.

The SUMPRODUCT function

The SUMPRODUCT function multiplies the value of each line of the first column by the value in the corresponding line in the second column, and then sums the result:

In this example, the calculation is the PRICE column multiplied by the QUANTITY column; SUMPRODUCT makes this calculation for each row in the selected range, and creates a virtual array in RAM of the results which is then summed as the final result.  

The formulas used are to the right of the results in the example above. The first example is the basic PRODUCT and SUM result, and the second introduces a further multiplication to include 15% tax. Ranges D4:D7 and E4:E7 are the arrays that these examples are working with (in the second example, the tax calculation is a static value, not an array). Any number of arrays can be included, but will be limited by the amount of RAM that is available. In the example below, I've introduced a criteria - only calculate the result if the value in array D4:D7 = Robin (referenced to cell G13):

Notice that after the parenthesis for the first array, there is a multiplication operator, whereas in the first example, the arrays were separated by a comma. The first part of this formula is a logical test on each row of the column D array: Does the value equal Robin? This will generate a Boolean value of either TRUE or FALSE, which we are unable to use. The multiplication operation against the PRICE array forces Excel to convert the values in the SPECIES array to either 1 (TRUE) or 0 (FALSE), which can then be used in the subsequent calculations. This is widely referred to as a "coerced" value.

Although the function naturally works with arrays of data, it is not and array formula. The array formula equivalent can be seen below:

Coercing Boolean values 

Where the result of a logical test of TRUE or FALSE is going to be used in a calculation, whether alone or within a wider formula, the value needs to be coerced to produce 1 or 0. In the examples below, we can see two methods of forcing Excel to provide a numerical rather than Boolean value:

In the LOGICAL TEST column is a simple comparison between each row of REFERENCE 1 and REFERENCE 2, with the Boolean result in the next column. In the COERCED TEST column are two methods of coercing the Boolean value: the first method precedes the logical test with two minus operators "--"; the second simply multiplies the result by 1. The results are in the COERCED OUTPUT column. 

In the example below, the formula applied to the LOGICAL TEST column can be seen below the tables (note the absolute and mixed cell references). Because there is a multiplication operator following the first array, the values are immediately coerced to 1 or 0 for all three arrays, and the results are then multiplied by the AWARD value of  £250. In the right table, you can see the way in which these arrays are calculated. There is only one row where all of the results of the logical test are TRUE in the left table; in the right table, this is the only row where the multiplication evaluates to 1. The virtual array that is created by the multiplication sequence can be seen in the RAM ARRAY column. The final calculation is a multiplication of the the virtual array by 250 which results in one row evaluating to 250: (1*1*1*250=250). All the other multiplications in the virtual array result in 0. For example, the calculation in the first row is (1*0*0*250=0):

We can use logical testing and coerced values to adapt the SUMPRODUCT function to perform as a COUNTIFS function in Table 1 and a SUM function in Table 2.

Table1

In Table 1, we previously used the COUNTIFS function to dynamically calculate the values for each day of week and each species. With SUMPRODUCT, we can use either version of these formulas which are also dynamic:

The difference between the two versions is to show the methods used to coerce Boolean values ( "--" and/ or "*"). It isn't actually necessary to use the "--" in the second formula - I've included it simply to show where it would fit into a formula that does not include a subsequent mathematical operator, as in the example we saw earlier:

The references are highlighted by border colour for each version, and show where the references are absolute and mixed. In this example, I've added a row that contains the dates for the current week, which forms part of the references used. Note that the far right cell is equal to the far left cell, then working from the penultimate right cell, a simple subtraction reduces that date by one day as it's dragged back to the left.

Table 2

With table 2, the SUMPRODUCT function has been stripped down to a single array, which will simply produce a sum of that array:

Note that the array is horizontal - it's the entire row of Table 1 per species - and because there is no logical test, there is no value to coerce. 


Side by side the two tables look like this:

The SUMPRODUCT function can by used in a variety ways that it was never intended for due to the way it is able to work with arrays of data, without being and array formula. Understanding the way in which the arrays of data are handled, and how to coerce logical test results, opens the door to creating array formulas that can perform multiple criteria look-ups across two dimensional arrays, find multiple instances of the same criteria in a range, and much more.

I hope you enjoyed this post - if you have any questions or need any help, please get in touch.

In the next two posts, we're going to look at the INDEX and MATCH functions, how we can use them as a highly flexible look-up function and how to use the coerced values of logical tests to specify more that one look-up criteria! 

Pivot Table Chart Data Sourcing

Introduction

In the last post we created a dynamic table using the COUNTIFS function to update Chart 1 of our Garden Bird Tracker with the count of each species by day across the current week: 

In this post, we're going to change the Chart 1 source data to a Pivot Table, and take a look at the advantages and disadvantages of this approach in a project with a simple data-set.

Creating a Pivot Table

To create a Pivot Table, the data must be arranged logically, with each column having a header, and there must be no gaps across the range of columns. Blank rows are not a problem and can be filtered out of the Pivot Table later.

To start, place the your cursor in any of the cells in the data in the Bird Tracker tab of your workbook, go to the Insert tab on the ribbon, and chooses Pivot Table:

A dialogue box will appear with the source and location options for the Pivot Table.

In the "Choose table or range" option above, you can see that the reference is "TBLTRACKER". This is because I converted the data in my Bird Tracker tab to an Excel Table, and then named it "TBLTRACKER" (an Excel table is not a Pivot table, or vice versa). There are many advantages to converting data into a named Excel table, but for our project today, we can simply select the first four column headers, and then drag down the range to a row that is well beyond the current last row of data. I would suggest going down to row 500 so that we won't need to update the source range for the Pivot table for a while:

In the location option, we are going to select Existing Worksheet, go over to out Data sheet, select any cell in a blank are of the sheet and then click OK:

You'll now see this in your Data sheet, which is the empty Pivot table template that Excel has generated:

And on the far right, the Pivot Table Field List where we can select information to be displayed:

:

As you can see, the possible option are the four column headers in out Bird Tracker tab that we selected (if Excel encountered a column with no named header when we selected our range of data, it would generate an error message and fail). We are free to drag any of the four fields into any area of the Pivot table and produce different layouts, different filter options, and count or sum the source data as we choose. To emulate the dynamic table that we created, we need the rows to show the species, the columns to show the day of the week, and the values for each row and column to be the number counted (see the lower part of the Field List panel above. In this way, the layout produced is identical, and in the order we need for Chart 1: 

Note that the date field has been added to the Pivot table filter area. In our dynamic table, the COUNTIFS function automatically calculates the week based on the date held in our Data tab, but we need to manually filter the Pivot table we have created to the date range for the current week. To do this, click on the DATE filter drop-down, and select the required dates in the same way that any filter in Excel works:

Now that we have the correct data in our Pivot table, we can change the source data for Chart 1 by right clicking in the chart area, selecting Select Data, going back to our data tab and selecting this range in the Pivot table:

Click OK, and the chart is now using the Pivot table data as a source. If  you right click and select Select Data again, you'll see the reference is no longer a range of cells, but a Pivot table:

Pivot table pros and cons

Pivot tables are extremely powerful and allow detailed analysis of large amounts of data without the need to write long or complex formulas. Using multiple Pivot tables, Pivot table slicers and Pivot charts gives you the ability to create highly detailed and interactive reports, from a broad overview right down to a single data component. Using a Pivot table based presentation in a business situation gives you the ability to answer any question about the data with a few mouse clicks.

There are some disadvantages with using Pivot tables: every time data is added to the pivot table source, the table must be refreshed to pick up the new data:

The COUNTIFS formula in our dynamic table will recalculate in real time all of the data in our Bird Tracker without intervention.

Unless the Pivot table's source data is an Excel named table, then the source data range will need to be adjusted if new rows or columns are added to the data. Converting your source data range to an Excel table solves this, but not the refresh issue. With our dynamic table, the COUNTIFS function is calculating against whole column references, regardless of how many rows those columns contain, and will never need updating.

With our Pivot table, we also need to manually filter the date field to the current week. With our dynamic table, the COUNTIFS function will automatically calculate the current week's figures, again, without any intervention.

The refresh and filter issues can be overcome by using Visual Basic for Applications (VBA) and embedding the macro in the Data worksheet so that it refreshes the Pivot table and filters the dates every time the sheet is activated. This is something that we will look at in future posts. 

The final thing to bear in mind is that large Pivot tables can be very difficult to read, so some consideration is needed when designing them. In many cases, multiple Pivot tables are preferable, and as mentioned, can be linked to one or more Pivot table slicers (Excel 2010 onwards).

I hope you enjoyed this post - if you have any questions or need any help, get in touch.

Coming up in the next post - using the SUMPRODUCT function as a COUNTIFS function, and as a bridge to Array Formulas!

Do you use Excel in your business or workplace?

I can help you with

SPREADSHEET SUPPORT

SPREADSHEET DESIGN AND CONSTRUCTION

SPREADSHEET UPGRADES

DATA ANALYSIS

WORKPLACE TRAINING

Get in touch and get ahead

     excel.swindon@gmail.com

Using functions to make dynamic charts (2)

Introduction 

In the last post, we looked at using the IF function to vary the values for Series 2 of Chart 2 and ensure the images hover above the data bars for Series 1 as the values for Series1 change. In this post, we are going to use the COUNTIFS function in Table 1 to automatically calculate the species we have seen on each day of the week for Chart 1, and then use a simple SUM function in Table 2 to calculate the weekly totals that are supplied to Chart 2.

The COUNTIFS function

The COUNTIFS function is based on the same TRUE/FALSE logical test as the IF function, but adds the flexibility to check whether more than one criteria is met before calculating the result. For example, count how many times Dunnock appears in Column D of Tracker sheet for Monday of this week:

Here's a snapshot of the formula that is one of the cells in Table 1 of our Data sheet:

The first part of the formula is looking at Column C of the Tracker sheet and asking does this equal the value of cell N5 (minus 6) of the Data sheet.The second part of the formula says if the answer is TRUE, then count how many times in Column D the value matches the value in cell N7 of the data sheet. To understand the references in this formula, lets look at the Data sheet, and Table 1:

As you can see, cell N5 contains a date. The date is set at the Sunday of the current week (in this instance, 21/01/2018). In the cell where this formula is entered, we want to calculate the result for Monday, therefore we subtract 6 from Sunday's date which converts the date to 14/01/2018 (for Tuesday, we would subtract 5, for Wednesday we would subtract 4, etc.).

Cell N7 contains the name of the first species we want to check. So, how many times on 14/01/2017 does BLACK CAP appear in our Tracker sheet? The formula returns a count of how many times both logical tests are met.

Referencing

We are going to use this formula across the entire table, so we need to make sure that the cell references are fixed where they need to be, and at the same time, allow us to copy them down the columns and increment the row number for us. For this, we use the $ in the cell references to fix the columns, rows or a mixture of the two where we want them. This is called Absolute (completely fixed), Relative (fully mobile) and Mixed (where the column or the row is fixed, and the other mobile) Referencing. In our example, we see this:

Tracker Column C which contains dates is an absolute (fixed) reference (note that with no row numbers, this reference points to the whole column which will extend to over 1 million rows).

Data N5 is also fixed - we need to always refer to this cell for our date.

Tracker Column D is fixed - this contains our species for a given date.

The very last part of the formula is a mixed reference. Cell N7 of the Data sheet contains the first species in our table that we will check. We'll need to fix the Column N, but allow the row number to increase as we copy the formula down and across out table, so we have a $ preceding N, but not 7:

$N7

 To change cell referencing, place the cursor somewhere in the cell reference (between the N and the 7), and then press the F4 function key on your keyboard. This will put $ before the column and the row reference ($N$7). Press F4 again, and only the row will be fixed (N$7); press F4 again, and only the column will be fixed ($N7). 

As we copy the formula down, each species will be picked up as $N7 becomes $N8, then $N9, etc. If we copy the formula across from N7 to U7 and then check each of those cells, we'll see the formula is identical, as all the column reference as Absolute. This is exactly what we want, and means we only have to type the formula once in cell N7, and copy it across the row. This is what it'll look like:

One final thing to do before we copy the formula down our table columns - decrease the amount subtracted from the value in Data N5 by 1 for each column. As mentioned earlier, the date in Data cell N5 is set for the Sunday of the current week, so for Monday (Column N), the formula is $N$5-6, which gives our formula the correct date for Monday. In Column M7, change the formula to $N$5-5 so that we have Tuesday's date. Continue across the columns until you reach Sunday. In this cell, remove the subtraction, as the value in Data N5 is Sunday's date:

Now we can copy the formulas down their respective columns, and the table will automatically calculate the values in our table as the Tracker sheet is updated:

Table 2

Now that we have a dynamic Table 1, let's put a SUM function in Table 2 so that both Table and Charts are completely self-updating. In Table 2, we simply SUM the values for all the columns in each row:

Notice that none of the references are fixed in this formula; we are only copying it down a single column, and we need the row number to increment. Type the formula in the first cell of the column, and then copy it down to the last - it will automatically increment.

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

In the next two posts, we are going to look at two very different ways of calculating our Table 1 data by using Pivot Tables, and then a modified SUMPRODUCT function.

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.