Search code examples
exceldynamic-arrays

How do I calculate the average from an array of conditional medians using a single formula?

I need to perform a 2-Step calculation in one formula.

The dataset contains several entries for each article and looks like that:

enter image description here

As of now, I use a "helper"-column to obtain an array of conditional Medians first and calculate the average afterwards.

Step 1: Calculate the Median %-Price Change per product.

enter image description here

Step 2: Calculate the average from these Medians (counting each article once).

enter image description here

I would like to combine these two steps in one formula (i.e. by making use of dynamic array functions).

Thanks in advance.

Solution

  • I'm thinking maybe a helper column is easier. However, it can be done in O365 which my employer has kindly provided:

    =AVERAGE((INDEX(SORTBY(B:B,A:A,1,B:B,1),MATCH(UNIQUE(FILTER(A:A,A:A<>"")),A:A,0)+FLOOR((COUNTIF(A:A,UNIQUE(FILTER(A:A,A:A<>"")))-1)/2,1))+
INDEX(SORTBY(B:B,A:A,1,B:B,1),MATCH(UNIQUE(FILTER(A:A,A:A<>"")),A:A,0)+CEILING((COUNTIF(A:A,UNIQUE(FILTER(A:A,A:A<>"")))-1)/2,1)))/2)

    So basically the match gives you an array of the row numbers where each group starts, and the floor/ceiling functions give you an offset from there to the mid-point of the sorted numbers within that group.

    See this for description of 'case-free' form of the median function.

    Assumes that the data is pre-sorted on the group letter, but not on the numbers within each group.

    enter image description here

    So Index now gives you an array without a hack - I didn't know that before attempting this.