Excel Corner: Working with Collections of Data

March 28th, 2008

This courtesy of Windmill Software….Dan

Excel has a little-used feature that provides great flexibility when analysing tables of data. It is called an Array Formula.

You can use an array formula

• To return several values
• To apply a function only to values matching your conditions.
• To perform calculations on values matching two or
  more criteria

An array formula is special in Excel: to enter it you press the Ctrl, Shift and Enter keys together on the keyboard. You must do this even after you edit the formula. If the array formula has been entered properly curly braces appear around it. (You cannot type the braces manually.)

An array formula acts on two or more sets of values known as array arguments. It is made up of normal Excel functions, which is why you must press the CTRL+SHIFT+ENTER keys to tell Excel that this is an array formula.

Here are some examples of array formulas that you should be able to adjust for your own data.

You can download an Excel spreadsheet containing examples of array formulas from http://www.windmill.co.uk/excel/array.xls

To find the three largest values in a range
An array formula can return several values at once. This is often quicker than entering a normal function several times in a spreadsheet, and reduces workbook size.

1. Select a group of three cells to hold the results of the calculation.
2. Enter the formula =LARGE(E7:E67,{1;2;3}) then press CTRL+SHIFT+ENTER

You must always select the cells to hold the results
first. The formula will be copied into each cell you select.

How it Works…

1. The LARGE function returns the xth largest number in a set of data. If you enter it as an array formula, you can specify an array of positions to be returned. In our example we have specified that we want the first, second and third largest numbers.
2. It returns an array, so we could use it with, say, the average function to average the top three numbers: =AVERAGE(LARGE(E7:E67,{1;2;3}))
3. We could achieve the average without using an array formula. But with an array formula we can do it using just one cell!

To Average Only Values Over 5 in the Range D7 to D27

Enter the formula =AVERAGE(IF(D7:D27>5,D7:D27)) then press CTRL+SHIFT+ENTER

How it Works…

1. The first part of the IF statement - that is the first array argument - checks whether each value in the range D7 to D27 is greater than 5. It stores the results in an array in memory: storing a value if the condition is met and “FALSE” if it is not. To see what Excel is doing, highlight the IF statement - (IF(D7:D27>5,D7:D27) - and press the F9 key. You’ll see something like this {7;6;6;6;6;7;6;6;7;6;7;6;FALSE;FALSE;FALSE;FALSE;FALSE}
2. The second part of the IF statement - the second array argument - specifies the location of the values to be stored. In this case we are storing the values which were checked to see if they were over 5. However, you could check values in column F, say, but average the corresponding values in Column E with =AVERAGE(IF(D7:D27>5,E7:E27))
3. AVERAGE is a normal Excel function which takes an array of numbers and returns their average value, eg =AVERAGE(1,2,3) equals 2. It ignores any text or logical values (TRUE or FALSE). Here the array stored in memory by the IF statement is being averaged.
4. IF evaluates a condition and returns one value if the condition is TRUE and another if the condition is FALSE. It has the format IF(test,value_if_true,value_if_false). If you don’t specify what value to return, it will return TRUE or FALSE as appropriate. In our example - IF(D7:D27>6,D7:D27) - we have instructed IF to return a value in the range D7 to D27 (the second array argument) if the condition is true. We haven’t specified what to do if the condition is false and so IF will return “FALSE”, which the AVERAGE function will ignore.

To count the number of times temperature was over 0 oC AND humidity below 50%

If temperature is in cells B7:B67, and humidity is in cells C7:C67, enter =COUNT(IF((B7:B67>0)*(C7:C67<50),C7:C67)) then press CTRL+SHIFT+ENTER

How it Works…

1. This time we are using a COUNT function. This counts the number of cells that contain numbers. It will ignore any text or “TRUE” or “FALSE” conditions. This means we can again use it with an IF function.
2. In this example we want two conditions to be true: (B7:B67>0) and (C7:C67<50). Multiplying the results of the two tests together means that if one of the conditions was false, a “FALSE” value will be stored in the array and the result will not be counted.
3. The second array argument, C7:C67, specifies the value to store if the conditions are true. This could equally be the temperature range of B7:B67. The actual values stored doesn’t matter: the COUNT function is only counting the number of values which aren’t false and using either range will work. (See point 4 above.)

To count the number of times temperature was over 0 oC OR humidity below 50%

If temperature is in cells B7:B67, and humidity is in cells C7:C67, enter =COUNT(IF((B7:B67>0)+(C7:C67<50),C7:C67)) then press CTRL+SHIFT+ENTER

How it Works…

• It works as the previous example, but instead of multiplying the two tests together we add them together. This is because adding a FALSE value to a number results in the number. (Try it in an Excel sheet. You will find that =3+FALSE equals 3.) This means that a + results in an OR condition.

Error Messages you may Receive, and the Solutions

“#VALUE”

- Make sure you have pressed CTRL+SHIFT+ENTER to
enter your array formula

“You cannot change part of an array”

- This message occurs when you try to delete or copy
only one of the results of a multi-result array
formula. Instead select all the results then
delete or copy.
- It also appears if you try to edit a multi-result
array formula and forget to press CTRL+SHIFT+ENTER
afterwards.

Note: Array formulas returning a single reading may slow a spreadsheet down. If one of the cells referenced in the formula changes, all the cells referenced will be recalculated. Don’t reference bigger ranges than you need.

Further Reading
For more tips on using Excel for data acquisition and analysis see http://www.windmill.co.uk/excel.html and http://www.windmill.co.uk/xlchart.html

Entry Filed under: Data Acquisition