Getting MEDIAN to work after IF statement

Alex Gardner

All data is in the same worksheet.

Days to Live is a column formula calculation that usually has a number, but sometimes an error, because there is no live date and it's a column formula (this is fine for me as I don't show it directly).

=MEDIAN({Days to Live}) works fine, even when there are records included that have no live date and the error (invalid data type) is shown.

However, I would like to show (in a roll-up table that populates widgets on a dashboard) the median days to live by individual relationship manager.

I can make the IF({Relationship Manager}....) piece work with just about anything else, but do not get it to work with the MEDIAN statement.

An example of what I think should work is this (give me the median days to live for specific person), but it does not work.

=IF({Relationship Manager},"John Doe", MEDIAN({Days to Live}, " "))

What am I doing wrong?🙏

Nick Korna

Hi @Alex Gardner,

A formula like this should work:

=MEDIAN(COLLECT({Days to Live},{Relationship Manager},"John Doe"))

In a table with names in one column you can obviously substitute a row reference in place of the name.

Hope this helps, if you still have any problems then just post!

Nick Korna

No problem at all, happy to have helped!

Nick Korna

Hi @Alex Gardner,

A formula like this should work:

=MEDIAN(COLLECT({Days to Live},{Relationship Manager},"John Doe"))

In a table with names in one column you can obviously substitute a row reference in place of the name.

Hope this helps, if you still have any problems then just post!

Alex Gardner

Thank you @Nick Korna, this solution you provided worked on the very first attempt! I am overjoyed! Really, really appreciate the help!

Nick Korna

No problem at all, happy to have helped!