MAX and AVG query
Hi, this is linked to a another thread and I am struggling.
Row 1 of my main sheet contains helper fields. On a separate sheet I am developing metrics. I want the metrics to exclude row 1 in its calculations as it's skewing the data. Row 1 contains a Task called D1 - see screenshot.
My metric code is =MAX({Surveys and Works - Runtime}) and then I have similar for AVG.
I have tried using the IF statement based around excluding D1 and/or the date field being blank, but keep getting UNPARSEABLE. I have trawled for a long time on this with no obvious answers. Thanks in advance, Paul 😀
Best Answer
-
Thanks Dave. Never used the COLLECT command. I had to tweak the TASK ID as this was on another sheet and it gave an error, but all good. Cheers😀
Answers
-
Hi @vipa2000,
Give this a try.
=MAX(COLLECT({Surveys and Works - Runtime}, {Task ID}, <>"D1"))
Hope this helps,
Dave
-
Thanks Dave. Never used the COLLECT command. I had to tweak the TASK ID as this was on another sheet and it gave an error, but all good. Cheers😀
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 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!