Results 1 to 5 of 5

20051013, 23:34 #1
 Join Date
 Sep 2002
 Location
 Honolulu, Hawaii, USA
 Posts
 63
 Thanks
 1
 Thanked 0 Times in 0 Posts
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.
Mahalo for your feedback,
John Jacobson

20051013, 23:47 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20051013, 23:50 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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)))

20051014, 00:05 #4
 Join Date
 Sep 2002
 Location
 Honolulu, Hawaii, USA
 Posts
 63
 Thanks
 1
 Thanked 0 Times in 0 Posts
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 arrayenter 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

20051014, 00:15 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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