Excel question - working out average colour

[Jed Shields]

Hi guys,

I'm putting together a simple RAG chart ie Red, Amber, Green against 15 categories. I'm trying to get an average colour of all the categories e.g. if I had 13 Red, 1 Amber and 1 Green then Red would be the average colour. It's not necessarily the colour that has the most occurances though, e.g. 5 Red, 4 Amber and 6 Green wouldn't be a Green average, it's probably Amber.

Am I making sense? What's the best way of trying to work this out?

 

[gillywibble]

Pivot point table.

 

[Jed Shields]

How would that work?

 

[Malakite]

I dont get your last example, why would amber be the average?

4 Amber
5 Red
6 Green

6+5+4 = 15
15/3 = 5
Red is the average?

 

[Lee]

You could number R,A, and G as 1,2 and 3, work out the average (mean) and convert it back into to the nearest colour, R, A or G using conditional formatting.

 

[Jed Shields]

I dont get your last example, why would amber be the average?

4 Amber
5 Red
6 Green

6+5+4 = 15
15/3 = 5
Red is the average?

That won't work as they will always add up to 15. So 1 R, 0 A, 14 G = 1+ 0 + 14 = 15
15 / 3 = 5 = Red, which it clearly isn't...

 

[Jed Shields]

You could number R,A, and G as 1,2 and 3, work out the average (mean) and convert it back into to the nearest colour, R, A or G using conditional formatting.

My dad suggested this method and it appears to work ... cheers.