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
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!