How to do an array formula with a structured reference?

Question

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.

But when I'm doing =MEDIAN(IF((classic_v2_Log[ [ Epochs] ]=A35),ClassicRAW!F:F)) it's just one range and the #N/A is still there. — Vinicius
– Vinicius, Commented Nov 29, 2018 at 10:23
But the first range is part of a table, say 50 rows, and the second reference is a whole column (outside the table?). Make it the same and that should work. — SJR
– SJR, Commented Nov 29, 2018 at 10:29
It worked! Thanks, can you please write it as an Answer so I can set it as the solution? — Vinicius
– Vinicius, Commented Nov 29, 2018 at 12:12

SJR · Accepted Answer · 2018-11-29 12:15:35Z

1

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))

answered Nov 29, 2018 at 12:15

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to do an array formula with a structured reference?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related