Red Amber Green help

[myoung76]

Hello,

I have a Red, Amber, Green (RAG) score that I would like to average i.e. if there are 6 categories (work streams) and each each one is scored in terms of progress either red, amber or green. If the majority of work streams are green then what would that be as an average percentage.

Hope that makes sense.

thank you very much.

Maggie

[Special-K]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[myoung76]

Thank you file attached

[myoung76]

Sorry cannot upload my attachment, even using go advanced.

[MrShorty]

I find that these kind of questions often need to start as math questions -- in this case, exactly what do you mean by "average"?

In statistics, there are three common measures of central tendency:
1) mean or arithmetic average where you sum up the data, divide by the number of points. For something like this, I would assign a number to each color using a lookup table and function (maybe 0=red, 1=amber, 2=green), then use a simple AVERAGE() function on the result. In this case, you get 1.25, which is something between amber and green.
2) median, where you sort the data and take the middle value if n is odd or the average of the middle two values if n is even. In this case, the middle two values would be one amber and one green, so this measure of central tendency is, again, something between amber and green. If I wanted to do this in Excel, I would again assign a number to each color using a lookup table/lookup function, then use the MEDIAN() function on those values.
3) Mode, where you take the value that occurs the most. In Excel, I would use a COUNTIFS() function to count up how many of each color there are, then use a MAX() function to determine the most frequent color, then use a lookup function to identify which color that is. You will need some consideration for how to handle when two or three colors tie for the most frequent. I could also see using the same assign a number to each color using a lookup table/function and use those numbers in the MODE() or MODE.MULT() function

What measure of central tendency are you intending to use here? What parts do you have trouble implementing?