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
- 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!