Average duplicates

I have a list of 7 cars, a drop down list where I can pick the color and an additional column where I can input the top speed.

On the bottom I have a grid made for average. What I want those green cells to do is to automatically show the name of the color and average they're speed.

Any advice?

Happy holidays!

Want to show me how to do it?
Never used a pivot table b4

AZ-XL,

Thanks a lot! That's exactly the formulas I was looking for!!

Pepe,

Thanks to you too. I couldn't figure it out, but I'll do some practice with the Pivot Table

Happy 2014!

Originally Posted by AZ-XL

Hi

Used this array formula to bring colour names only one time. Used helper column there too
=IFERROR(INDEX($B$3:$B$9,SMALL(IF($D$3:$D$9=1,$A$3:$A$9,""),ROW(A1))),"")

To average the colours
=IFERROR(AVERAGEIF($B$3:$B$9,B11,$C$3:$C$9),0)

Hello AZ-XL,

When you have some time could you please explain me how did you come out with the row formula?
Reason I ask is because I would like to change the CAR# from 1 to 7, to 101 to 107 or other numbers. And when I change those numbers the formula crushes.

Thanks!