Excel naming dynamic variable

Question

I'm creating an excel sheet which takes a sum of variables based on value of A,B,C and the number along with it 10A,20B, or 9C So,

10A = 10, 20B = 40, 9C = 27

to put it in simple terms

A=1, B=2, C=3

10A = 10(1); 20B = 20(2); 9C = 9(3);

Is that possible to do in Excel 2016?

Answer 1

Please try:

=LEFT(A1,LEN(A1)-1)*(CODE(RIGHT(A1))-64)

(If to be added up: {=SUMPRODUCT(LEFT(A1:C1,LEN(A1:C1)-1)*(CODE(RIGHT(A1:C1))-64))})

Answer 2

Make a table with your values. In my example, I called TB_VALUES

Then you can combine your table with a VLOOKUP to search for a letter and make the operation.