Hi again.
Last week we prepared the first output report from our sample data.

You will recall that the engine for the output is in the ‘set up’ tab and is repeated below.

Cells D2 through to D17 is the table of years I created.
Cell D1 is linked to the year chosen in Output 1, in the above example this is the 13th item in the year table (2008).

Cells E2 through to E17 identify the row number in column A of ‘Ho_Maint’ where the relevant year appears the formula (in E2) is =MATCH(D2,raw_year_column,0)
In the example above 2008 is in row 50, and this is recorded in cell E1.

The syntax for the Match function is Match(value, array, match_type)
• value is the value to search for in the array – in our example this is the years in column D
• array is a range of cells that contains the value that you are searching for – in our example this is the range named raw_year_column
• match_type is optional. It is the type of match that the function will perform. The possible values are:
–  1 The function will find the largest value that is less than or equal to value. Ensure to sort the array in ascending order.
–  0 The function will find the first value that is equal to value. The array can be sorted in any order OR
– -1 The function will find the smallest value that is greater than or equal to value. Ensure to sort the array in descending order.

Cells F2 through to F12 is the table of regions I added earlier.

We now need to identify the relevant costs per region for the year that the user has chosen. To do this we need to find the row and the column numbers where this data is recorded.

From the above calculations we know the row number, which is recorded in E1 so column G2 to G12 calculates the column number (we will use row 2 East Midlands cells to explain this process).
=MATCH(F2,Ho_Maint!$A$1:$M$1,0)

In our example (cell G2) the result is 3, being the third column within the range (A1-M1) where the text ‘East Midlands’ appears.

So we now know that the data we are after is in row 50 and column 3 of ‘raw_data’ (the name I gave to the data in Ho_Maint list), but hang on a minute!
Actually this only identifies the Q1 value for the year, we need to get the average of the four quarters for the year.

This is calculated in H2 with formula
=AVERAGE(INDEX(raw_data,$E$1,G2),INDEX(raw_data,$E$1+1,G2),INDEX(raw_data,$E$1+2,G2),INDEX(raw_data,$E$1+3,G2))

This look complicated, but lets demystify it.
In plain English this calculates the AVERAGE of Q1, Q2, Q3 and Q4

The syntax for the Average function is Average( number1, number2, … number_n)
number1, number2, … number_n are numeric values – they can be numbers, named ranges, arrays, or references to numbers. There can be up to 30 values entered.

To identify the values for each quarter we use the INDEX function

There are two types of INDEX but the syntax for the one that we need is
Index(array, row_number, column_number)
• array is a range of cells or table.
• row_number is the row number in the array to use to return the value.
• column_number is the column number in the array to use to return the value.

For Q1 the formula is
(INDEX(raw_data,$E$1,G2))
and for Q2
(INDEX(raw_data,$E$1+1,G2)) etc. etc. for the four quarters.

This gives us the average cost per region (recorded in H2 to H12), which we now need to convert into the graphical representation in J2 to J12.

The way I have chosen to do this is use the ‘n’ character (saved in J1) but using the Wingdings font style. With costs ranging from 97 to 1100 we had to scale this down to about 25 characters. To do this I divided the cost by 50, but to maximise flexibility in use the value is saved in H1. This is recorded in I2 by using the simple formula =H2/$I$1

The REPT function gives us the result we are after. For J2 it is
=REPT($J$1,I2)

The syntax for the REPT function is:
REPT( text, number )
• text is the text value to repeat – in our example this is the ’n’ in J1
• number is the number of times to repeat the text value – the value in H2 that we calculated earlier

I could have placed this formula directly in Output 1 but have placed it here for clarity.

And that’s it, hopefully you found the above of interest and perhaps you can think of similar solutions to your data.

Next week we shall be looking at another dynamic graph.