# Thread: Using an array formula to select values from a tab (Excel 2002)

1. ## Using an array formula to select values from a tab (Excel 2002)

Let's see if I can be a little more thorough with this question than I just was with a hurried erroneous reply.
Is there a way to create an array formula that would gather values from a column in a table of data based on criteria in another column in the table? Ideally, I want to use the MEDIAN() function in the same way the SUMIF() and COUNTIF() functions work, to operate on a select set of values in a table based on some criteria in another column in the table. I'm thinking that using an array formula as the argument in the MEDIAN function is the way to go, but I'm woefully unskilled with arrays.

A simple example is attached. Sheet1 has a table of sales data including year sold and price columns. I'd like formulas on Sheet2 to automatically calculate median sold prices by year. There's a variable number of sales per year and the data is not sorted, so for each year the formula would need to look at the entire range of sales data and select only those sales in that year. I used the COUNTIF() function to count number of sales. The intersection of ranges that I used for the COUNTIF function is not necessarily required for the array formula.

If an array formula will not work, then I suppose I will need to create a custom function, which I can do. Just seemed like a good oppty to get my head around arrays a little more. Also, if I turn to a custom function, I have to deal with making the function portable if I send the file to someone else.

John Jacobson

2. ## Re: Using an array formula to select values from a tab (Excel 2002)

Try this array formula in Sheet2!C2:

=MEDIAN(IF(Sheet1!\$D\$2:\$D\$68=Sheet2!A2,Sheet1!\$E\$2 :\$E\$68))

and fill down as far as needed. This type of formula will work with SUM, AVERAGE, VAR, MEDIAN, etc.

3. ## Re: Using an array formula to select values from a tab (Excel 2002)

Addition: if there are no data for a year, such as for 2005, the formula I posted will result in an error. You can suppress this with an extra IF combined with ISERROR:

=IF(ISERROR(MEDIAN(IF(Sheet1!\$D\$2:\$D\$68=Sheet2!A2, Sheet1!\$E\$2:\$E\$68))),"",MEDIAN(IF(Sheet1!\$D\$2:\$D\$6 8=Sheet2!A2,Sheet1!\$E\$2:\$E\$68)))

4. ## Re: Using an array formula to select values from a tab (Excel 2002)

Beautiful, Hans!
That shows me the trouble I was having before. I was trying to enter array formulas in all cells at once. Your formula works great when I array-enter it in each cell separately, well worth the minor effort. Also works with the intersection of ranges, too - and automatically updates when data changes or ranges are renamed. Cool! Now maybe I'll have time for lunch.
Many mahalos,
John Jacobson

5. ## Re: Using an array formula to select values from a tab (Excel 2002)

Be aware that arrays can be a lot of overhead and may cause sluggishness with large numbers of them especially with large tables of data

Pivot tables might be a better way or even creating a summary sheet in code. Look at the thread starting in <post#=315,619>post 315,619</post#> for a "similar" problem and the discussion of the sluggishness and the solution that was developed to get around it.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•