Assigning a value to a color and averaging?

Occasional Visitor

So it is a little complicated and based on everything I've read I think I'll have to assign an underlying value to every single cell and I really don't want to do that.


Basically, I have made a key that coordinates 5 colors to their corresponding value: Red=1, blue=3, Purple=5, etc.


What I am looking to do is somehow functionally give each color that (stated above) value and then find the average of those values from those colors in a range of cells. I don't know how quite else to explain it but if you think you might have an idea and need more context I can email you a ss of my spreadsheet. 


Thank you so much for the help.


P.S. If you are thinking conditional formatting think the opposite, I would like a color assigned a value not a range of cells with a value assigned a color. (I'm working with words, not numbers so it doesn't compute)

1 Reply

@Sleep_is_key73 Google for "counting cells by colour excel" and you'll find many resources describing how this can be done. In your situation it most likely requires a small user defined VBA function that allows you to count cells of a particular colour in a range. Once you now how many cells of each colour you have, you can do your calculations, I guess.