Can I create a MEDIANIFS formula for multiple value sets?

Numbers does not allow a user to create new functions.

you could use applescript which means you would have to trigger the script to run (it would not update as you entered or changed data)

Not fully automated, but quick and easy... Have you considered using the built-in Numbers filtering capability? Filter on the date column to derive your subset, select the results, command-c to copy, click once in a cell in another (existing) table, e.g. not on the canvas, and command-v to paste. That gives a table with the subset. Then do your median calculations on that new table.

SG

bluedrew37203 wrote:

I want to create a MEDIANIFS formula (also MODEIFS, MAXIFS, MINIFS). I have 63 rows of values which correspond to the 53 specific weeks within a calendar year. I would like to create a formula that calculates the median of each row whose header column is less than a specified date, e.g. TODAY.

While the filter approach may be easier, if you need constant refresh, your table is sorted by date, and you don't have such big tables that Numbers becomes sluggish when you use "volatile" formulas like OFFSET, then you could try something like this:

The formula in B2 of summaries:

=MEDIAN(OFFSET(Table 1::B$1,0,0,C2))

The formula in C2:

=MATCH(A2,Table 1::A,1)

The MATCH in C2 finds the row number of the date in your sorted table.

The OFFSET starts at the anchor cell B1 and forms an array extending down the number of rows contained in C2. (Like UNION.RANGES and a few others, OFFSET is one of the few functions in Numbers that creates an array in memory that can be addressed by other functions.)

The MEDIAN then calculates its result based on that array.

The two formulas consolidated would look something like this:

=MEDIAN(OFFSET(Table 1::B$1,0,0,MATCH(A5,Table 1::A,1)))

SG

bluedrew37203 wrote:

Thanks. That is helpful and as elegant of a solution in Numbers that I will likely find. Unfortunately, I'm working with a document that does have pretty large tables, so OFFSET may result in sluggish performance.

I have learned spreadsheets on Numbers exclusively over the past two or three years, so I do hope Apple will add greater array functionality to it soon. It seems quite odd that they have not as of yet.

Thanks for the gold star.

How large is "pretty large"?

Excel doesn't have MEDIANIFS either. But filtering is often a powerful tool, both in Excel and Numbers.

SG