Results 1 to 5 of 5
  1. #1
    Star Lounger
    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

  2. #2
    Plutonium Lounger
    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.

  3. #3
    Plutonium Lounger
    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)))

  4. #4
    Star Lounger
    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 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. #5
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

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