1

Say I have a table "Data" (created with the "Format as table" button, with headers). In another table (say "Table"), I have a column ("Target column") that has values in the column names of the "Data" table and a column "Median". In the "Median" column, I want to have the median value of the column that is given in the "Target column" column of table "Data".

I tried to do Median(Data[A2]) where A is the column index for the "Target column" column. This of course doesn't work. I suppose something like it could work with INDIRECT, but I'm not sure that's the most performant solution.

Say my table "Data" has columns "val1", "val2", "val3" and my table "Table" I have columns "Target column", "Median". For any column name of table "Data" in the "Target column", I want to have the median of that column in the table "Data" in the column "Median" of the table "Table".

Joseph Doob
  • 113

1 Answers1

1

Either of these should work:

This searches the header row of the Data table for the heading you've entered in the Target column of your Table table. It then uses the result of that to return the correct column from the Data table and calculates the median for that.

=MEDIAN(INDEX(Data,,MATCH([@Target],Data[#Headers],0)))

or

This creates a text version of the formula you need to reference the column, INDIRECT then changes it to a formula that you can use MEDIAN on.

=MEDIAN(INDIRECT("Data[" & [@Target] & "]"))

The first one would be preferable as it's not volatile - i.e. it won't recalculate whenever anything changes in the workbook.

Darren Bartrup-Cook
  • 515