Search code examples
excelexcel-formulaarray-formulas

How to do an array formula with a structured reference?

I need to calculate the MEDIAN of the rows matching some conditions.

Conditions are in the rows A, B, C and D and my infos are in a table called classic_v2_log. When I'm using the Sheet Name it works:

=MEDIAN(IF((ClassicRAW!B:B=A35)*(ClassicRAW!C:C=B35)*(ClassicRAW!D:D=C35)*(ClassicRAW!E:E=D35),ClassicRAW!F:F))

But if I try to use the Table reference it gives me a #N/A and if I try to use the Show Calculation Steps it goes beyond the Table.

I'm trying something like this and it's not working:

=MEDIAN(IF((classic_v2_Log[ [ Epochs] ]=A35),ClassicRAW!F:F))

I am confirming the formula with Ctrl+Shift+ENTER to make it as an array-formula.

Solution

  • The essential point is that the two ranges (arrays) need to be the same size. If one of the ranges is in a table and the other isn't you will have to ensure the second is the same number of rows as the first.

    For example, if your table ran from row 2 to row 50:

    =MEDIAN(IF((classic_v2_Log[ [ Epochs] ]=A35),ClassicRAW!F2:F50))