Averaging sum of Cells with Letters

[Greenland]

Hello,

I'm hoping someone can solve this very complicated problem I'm having with an Excel Sheet.
I have a table that is occupied by cells that represent 3 colors combined with letters. In other words...I created dropdown lists for every cell that offers just 3 options. (R Y and G)
I created colors to fill the cells based on the letter they choose. R=Red. Y=Yellow and G=Green. All good there. People love just using dropdowns to fill the cells.

I have a row at the bottom that is supposed to figure out averages from the individual columns above.

Obviously its not logical to write a formula that knows how to average colors or letters so I associated number values to each letter. R=1, Y=2 and G=3. I have 3 separate formulas for each of those associations.
I think I created them correctly.

Now I'm stumped! How do I tell the bottom cell in column G to average the total values of all of the above cells in that same column based on my formulas?

In this example...I would need cell G18 to be able to add, then average the total of G4 thru G17 and use my 3 formulas that explain the numerical values of each letter in those cells. I will then need each bottom cell to do the same for their respective columns as well once I can figure out what to enter in the cell.

Thanks so much to anyone that may be able to walk me through this.

 

[Marcelo Branco]

Welcome to Mr Excel forum

Do you want a weighted average?
Example:
G4:G17 contains 4 Rs, 4 Ys and 5 Gs
As R=1, Y=2 and G=3 the calculation of the weighted average would be
(4x1 + 4x2 + 5x3)/(4+4+5) = 27/13 = 2.076923

Is this what you need?

M.

 

[Aladin Akyurek]

Is this what you are after?

In G18 enter and copy across:

=SUMPRODUCT(SUMIFS($V$2:$V$4,$U$2:$U$4,G$4:G$17))/COUNTIFS(G$4:G$17,"?*")

I leave turning the result "into a color" to you.

I don't get what you mean by "I associated number values to each letter. R=1, Y=2 and G=3. I have 3 separate formulas for each of those associations". I used those associations as letter values in the foregoing formula.

 

[Greenland]

Welcome to Mr Excel forum

Do you want a weighted average?
Example:
G4:G17 contains 4 Rs, 4 Ys and 5 Gs
As R=1, Y=2 and G=3 the calculation of the weighted average would be
(4x1 + 4x2 + 5x3)/(4+4+5) = 27/13 = 2.076923

Is this what you need?

M.

Yes!!! Exactly what I need!
Then somehow turning that 2.07 to yellow so the cell turns yellow too....

 

[Aladin Akyurek]

See post #3 ...

 

[Greenland]

Aladin,

That's not quite what I'm after. It yields strange results. It came up with a crazy high number of 78.2 or something way off. Thank you though for the time and effort you put into the formula. I DO greatly appreciate it.

td

 

[Marcelo Branco]

Is not clear where the table Letter-Formula is located. Is it in columns U and V or in columns U and X?

M,

 

[Greenland]

U and X. Starting at rows 2.

Being kinda new with Excel,..and perhaps stupid mixed in,...I have a separate formula for each letter. So the first formula that is set for letter R is located at X2...... and then X3 for letter Y.......... and X4 for letter G.
and the Letters are located beginning at U2 for R, U3 for Y and U4 for G.

 

[Marcelo Branco]

U and X. Starting at rows 2.

So adjust Aladin's formula to
=SUMPRODUCT(SUMIFS($X$2:$X$4,$U$2:$U$4,G$4:G$17))/COUNTIFS(G$4:G$17,"?*")

M.

 

[Greenland]

Columns U and X. each starts at row 2.
See my convoluted reasoning for the way I did the formulas in the photo below: