No AVERAGEIFS so I tried AVG(COLLECT) but can't get it to work with multiple criteria

pietro108076

Hi, I'm trying to calculate the average of the operational speed [plants/h] only if the [job type] is "Grafting TOM" (cells highlighted in yellow) and also IF [week NO] is a certain value: so basically the average speed for each week for each job type.

This formula will be on a different Metrics sheet on the same folder (sheet attached below), I'm working on generating on this sheet the average of each specific job type (on the column [tasks]) for each week listed on this metric sheet.

I've tried AVG(COLLECT) formula as well for few hours but it seems that I'm missing something here, can't get it working.

Anyone could help giving me some hints to get over this?

TIA😊

Pietro

pietro108076

I'm testing this formula but not sure if it's correct:

Genevieve P.

Hi @pietro108076

Since you have two criteria (the week number and also the Task name), then yes! AVG(COLLECT is the way to go.

I would suggest replacing your row number (in this case, row 11) with the @row function so you can drag-fill this formula, and adding in an Absolute Reference for the Current Week cell so it doesn't move as you drag the formula around:

=AVG(COLLECT({Range to Average}, {Job Type}, Task@row, {Week no.}, $[Current Week]$9))

It actually looks like you have built the formula correctly, although it's hard to tell without seeing each of the Ranges. I presume that the one called Range 4 is the Plants/ column that you're looking to average, and Range 2 is the Job Type column, is that correct?

I tried to replicate receiving this error when trying to average 0s and it worked fine, without returning this error. How is that Plants column being populated? Are there any instances of errors in that column?