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
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!