MAX and AVG query

vipa2000

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.

Screenshot 2024-05-03 150226.png

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 😀

vipa2000

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😀

DKazatsky2

Hi @vipa2000,

Give this a try.

=MAX(COLLECT({Surveys and Works - Runtime}, {Task ID}, <>"D1"))

Hope this helps,

Dave

vipa2000

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😀