Getting MEDIAN to work after IF statement
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?🙏
Best Answers
-
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!
-
No problem at all, happy to have helped!
Answers
-
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!
-
Thank you @Nick Korna, this solution you provided worked on the very first attempt! I am overjoyed! Really, really appreciate the help!
-
No problem at all, happy to have helped!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!