Countif with Median
Hi there!
I am trying to gather the median for a set of numbers but only if it falls in a certain category. I have a formula used in the column I am pulling the median from, and in instances where I don't have all the data, the cell defaults to 0 (as i am using a division formula) which skews the median.
Example: I want to pull the median from the "Construction start to complete (days)" column, but only count the sites that are in the "Construction" phase.
I've tried incorporating Countif or If into the Median formula but I haven't had any luck.
Any and all help is appreciated!
Answers
-
Use a combo of MEDIAN and COLLECT. See below, replace column names as needed
=MEDIAN(COLLECT([Construction start to complete (days)]:[Construction start to complete (days)], Phase:Phase, @cell ="Construction"))
-
@Leibel Shuchat Thank you!
I forgot to mention that i am referencing a different sheet, so how would this affect the formula?
-
Just replace the column names with cross sheet references
-
Here is my formula, it is still showing up as unparsable.
=MEDIAN(COLLECT({New Stores Tracking Sheet Range 4}:{New Stores Tracking Sheet Range 4}, {New Stores Tracking Sheet Range 7}:{New Stores Tracking Sheet Range 7}, @cell ="Construction"))
-
When using cross sheet references you don't put it in twice with the colon separator:
=MEDIAN(COLLECT({New Stores Tracking Sheet Range 4}, {New Stores Tracking Sheet Range 7}, @cell ="Construction"))
-
@Leibel Shuchat thank you!
I am hoping to build onto this formula to show the above median but separate it out into dates in the past from the current day and dates in the future from the current day. Below is the formula I'm trying, but not quite sure what to use in place of the AND function. Any thoughts?
=MEDIAN(COLLECT({New Stores Tracking Sheet Range 3}, {New Stores Tracking Sheet Range 7}, @cell = "Construction"), AND({New Stores Tracking Sheet Range 8}@row < = DATE(2022, 11, 1))
-
No need for the AND:
=MEDIAN(COLLECT({New Stores Tracking Sheet Range 3}, {New Stores Tracking Sheet Range 7}, @cell = "Construction", {New Stores Tracking Sheet Range 8},@cell<=DATE(2022, 11, 1)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!