Require a method to summarise my data

Hello, I'm a bit stumped as to the best way of displaying my data in meaningful way. To best explain it, I've attached a simple scenario in an excel file in the same format as my actual spreadsheet.

Working with the attached file, lets say I want a table that orders each colour of ball by size. Like so:

Colour	Average diameter
Red	8.2
Blue	4.9
Green	7.8
Yellow	9.6

The values above aren't true, but are what I'm trying to work out. Would something like this be possible? I am unsure how to go about it.

Thank you,
Rob

Hi, welcome to trhe forum

Im not sure I understand what you want here There are 2 functions that can be used to determine the order of a range of values, perhaps 1 of these will be of use?

1. RANK() will return the rank (1, 2 etc) of a value within a range
2. LARGE() will return a list (when copied down) of values from a range, ranked from highest to lowest (=SMALL() will do teh same in ascending order

It's complicated. I used ARRAYED formulas. With "red" in A17,

=SUM((INDEX($C$3:$F$12,,MATCH(A17,$C$2:$F$2,0))*$B$3:$B$12)/SUM(INDEX($C$3:$F$12,,MATCH(A17,$C$2:$F$2,0))))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

I checked it by typing out each ball diameter separately for red and got the same answer. See attached.

=SUM((INDEX($C$3:$F$12,,MATCH(A17,$C$2:$F$2,0))*$B$3:$B$12)/SUM(INDEX($C$3:$F$12,,MATCH(A17,$C$2:$F$2,0))))

Part 1: SUM(INDEX($C$3:$F$12,,MATCH(A17,$C$2:$F$2,0))) will give the total number of red balls (A17 = red)

Part 2: INDEX($C$3:$F$12,,MATCH(A17,$C$2:$F$2,0))*$B$3:$B$12) takes the number of red balls in each group and multiplies that * the avg diameter of that group.

Part 3, SUM,( Part 1/Part 2) is calculated for each group and then summed for the total red balls. This works out to be the average diameter of the red balls.

Did that make any sense?