Would like to input 3 letters into a cell, each having a different numerical value

[jdoon5261]

Hi Everyone,
New to the forum. I'm a CATE teacher and am working on a professionalism rubric that I can carry with me in the lab. I am getting stuck on this part. I would like to input one of 3 letters into a cell and have it correspond to a numerical value that a formula in another cell will see as that numerical value.
A layman's example would be: Y=100, P=75, n=50. I have this formula "=SUM(F2*0.25)+(((G2+H2)/2)*0.25)+(((J2+K2)/2)*0.25)+(((M2+N2)/2)*0.25)" in another cell that would need the numerical value of the letter in order for it to work.
I know my formula is somewhat amateurish but I'm a noob to all of this.
Thanks
John.

 

[Becky]

Welcome to the forum

Could you talk me through your formula please, and also give some details on what isn't working?

 

[jdoon5261]

Hi Becky,
Sorry but I accidentally deleted the link to this site.
I've been working on my worksheet and have learned quite a bit since October. I'm still running into an issue with the original problem that I posted above. I should be better able to explain it now though.
My goal is to input 1 of 3 possible letter characters into a cell and have it represent a numerical value that can then be used in the formula of another cell. I've seen some examples like =IF("Y"=0, "N"=1) but I can't seem to make that work for me. I can only assume I'm doing something wrong.
I hope you are still out there and can help me.
Thanks.
John

 

[jdoon5261]

Wow, I remember why I kept putting this part of my worksheet off. Because it is kicking my butt.
a pic might help.

the area I'm talking about is the fields below the participation column. I would like the letters "Y", "E", and "V" to equal 100. And the letter "N" to equal 0. If the cell is left blank then no value should be returned.
The formula in the N4 cell would then average out the values of the "I4" thru "I5" cells. The "N4" cell value is then used in an =average formula in cell "AA4"
I hope this better explains it.

 

[jdoon5261]

Is there a way to edit my posts?

 

[Becky]

This would do what you want, but it's pretty messy! Could be simplified down but I'm pushed for time.

=IF(I4<>"",VLOOKUP(I4,I6:J9,2,FALSE))+IF(J4<>"",VLOOKUP(J4,I6:J9,2,FALSE))+IF(K4<>"",VLOOKUP(K4,I6:J9,2,FALSE))+IF(L4<>"",VLOOKUP(L4,I6:J9,2,FALSE))+IF(M4<>"",VLOOKUP(M4,I6:J9,2,FALSE)))/COUNTA(I4:M4)

Where cells I6:J9 is a table showing the corresponding values to the letters (you could put this anywhere in your workbook). Basically the formula looks at each day in turn, considers if there if there is data in the cell, and if so it looks up the corresponding value from the table. Then all values are added together, and divided by the number of days that data has been input for. In this example, the result is 75.

Hope this is what you were looking for but if I've misunderstood please let me know!