Search code examples
excelexcel-formulaarray-formulas

Apply functions like MEDIAN to individual elements in an array formula - Excel

I am using the MEDIAN function to calculate the overlap of two date ranges. The formula looks something like this

=MEDIAN(A18,C18+1,$C$8+1)-MEDIAN(A18,C18+1,$B$8)

A18 & C18 are date range 1

B8 & C8 are date range 2

I would like to turn this into an array formula similar to this to sum the results for multiple rows

{=SUM(MEDIAN(A18:A24,C18:C24+1,$C$8+1)-MEDIAN(A18:24,C18:24+1,$B$8))}

This only works if MEDIAN is applied separately to each element in the arrays A18:A24 & C18:C24.

Currently, Excel concatenates the arrays contained within each MEDIAN call and returns the overall median, leaving only one value for SUM to sum. And that is the 'wrong' result for what I need to do.

Is there a way of forcing excel to apply MEDIAN to each element in my arrays? Ie is there a way to force excel to return an array from the MEDIAN function.

If this is possible it allows me to avoid a 120,000 element array or a macro enabled workbook.

Solution

  • Cool MEDIAN formula. But you can't use MEDIAN as you desire with an array formula since MEDIAN always returns a single result (not an array).

    I have a solution to your problem, but it's somewhat sluggish/respetitive:

    (I have included comments to the right of the formula to explain what is happening)

    = IF(C18:C24>C8,     // (For each cell in C18:C24) If the value is greater than C8
     C8,             // Then C8 is the upper bound
     C18:C24)        // Else, the specific value from C18:C24 is the upper bound
  -                  // (minus sign)
  IF(A18:A24<B8,     // (For each cell in A18:A24) If the value is less than B8
     B8,             // Then B8 is the lower bound
     A18:A24)        // Else, the specific value from A18:A24 is the lower bound

    This returns an array of the overlaps of date ranges, except that it is possible that these results will return negative numbers (if an overlap doesn't occur), but in this case you want to return 0.

    You can get around this with another IF statement.

    = IF(formula < 0, 0, formula)

    Where formula is the first formula I have above.

    Side note: Normally in this situation, you'd be able to avoid typing out formula twice with something like this:

    = MAX(formula,0)

    Except this won't work when the formula itself returns an array since MAX only returns a single result. This is a similar problem you are experiencing with MEDIAN in the first place.

    All in all without comments, this is the formula to sum up the values:

    = SUM(IF((IF(C18:C24>C8,C8,C18:C24)-IF(A18:A24<B8,B8,A18:A24))<0,0,
          IF(C18:C24>C8,C8,C18:C24)-IF(A18:A24<B8,B8,A18:A24)))

    Of course, this is an array formula, must be entered with Ctrl+Shift+Enter instead of just Enter.