AVERAGE function problem (probably)

[Davzx]

Hello,

I need some help with AVERAGE function, at least i think that's the function i need for this. I will try to explain example sheet as best as i can.
There is a product with final output measured in meters, it's a 3 shift production with speed changing depending on guy running the shift. What i need is average speed for final product?

Any help is greatly appreciated.

Example.xlsx

[AndyLitch]

Put this function in G3 and paste it across

=SUMPRODUCT(($A$2:$A$16=G$2)*($D$2:$D$16))/COUNTIF($A$2:$A$16,G$2)

[etaf]

=AVERAGEIF($A$2:$A$16,G2,$D$2:$D$16)

will provide the averages in your ? cells

[oeldere]

with an pivot table.

see the attached file.

[Davzx]

I guess i didn't explain it properly, i need average speed.

[oeldere]

Also average of speed.

See the attached file.

[AndyLitch]

1. Select the cells with formulas
2. Press CTRL-H
3. Replace all $D's with $C's

[Davzx]

Problem is not all shifts did same amount of meters which means average speed for final product can't be determined according to shifts only, it needs to be connected to meters done (different shifts had different time running the machine)
I know how to calculate it but i don't know how to persuade excel to do it for me

Average speed for product a is ~91,75

[etaf]

how do you arrive at 91.75
whats the calculation you use ?

[Davzx]

Took me 5min to find a way to upload attachment in reply

Example2.xlsx

[Davzx]

I found a workaround.

Thanks for your help, much appreciated.

[oeldere]

With sumproduct => see the attached file.

[AndyLitch]

Alternative
Example2.xlsx

[daddylonglegs]

You can avoid the need for a helper column by using this version

=SUMIF($A2:$A16,K2,$D2:$D16)/SUMPRODUCT(($A2:$A16=K2)+0,$D2:$D16/$C2:$C16)