Assign Value to Letters for Averaging

**thesonofdarwin** · 04-09-2010, 09:08 PM

I'm looking for a way to assign values to letters so I can average columns but retain the letters in the column. I've been able to successfully overwrite my letters with appropriate values and subsequently avg, but that's not what I'm looking to do.

I've attached a sample of what I'm trying to accomplish. I'm looking to keep a running average of each column on the right where D=1, P=2, and M=3.

Sidenote: File isn't as diabolical as it may seem

Trying to put something together for employees to keep their own running tab of accomplishments so they are actually prepared for reviews.

Edit: Fixed attachment.

**teylyn** · 04-09-2010, 09:18 PM

There's no data in your sheet, just headers. It is not obvious what you want to average. Fill in some data and manually create the expected result.

**thesonofdarwin** · 04-09-2010, 09:19 PM

Originally Posted by teylyn

There's no data in your sheet, just headers. It is not obvious what you want to average. Fill in some data and manually create the expected result.

Sorry, I noticed that as I was closing excel. The dreaded not remembering to save files. It's already fixed.

**teylyn** · 04-09-2010, 09:26 PM

What is the expected result for the sample?

**thesonofdarwin** · 04-09-2010, 09:30 PM

The result would go under the Averages column L1 where each cell would keep a running average of the corresponding column. So L2 =AVERAGE(C:C), L3 =AVERAGE(D:D), and so on. Expected result would be a value between 1 and 3 as D=1, P=2, M=3.

**teylyn** · 04-09-2010, 09:31 PM

Maybe like this in L2, adapt column references for the other averages

=(COUNTIF(C:C,"D")+(COUNTIF(C:C,"P")*2)+(COUNTIF(C:C,"M")*3))/COUNTA(C:C)

**thesonofdarwin** · 04-09-2010, 09:37 PM

That works perfectly! I just altered the count to subtract the header:

=(COUNTIF(C:C,"D")+(COUNTIF(C:C,"P")*2)+(COUNTIF(C:C,"M")*3))/(COUNTA(C:C)-1)

Thanks so much for the quick response. I was trying many things from defining a table and using vlookups.

This is much more succinct and logical. Thanks

**teylyn** · 04-09-2010, 09:43 PM

I was just coming back here to correct the formula, since the header cont only just struck me. Good on ya for picking it up yourself.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save