Assigning a numerical value to a letter in excel

**lisa07956** · 04-15-2013, 09:07 AM

Hello

I am trying to assign a numerical value to letters used in an excel spreadsheet.

L=7.5 / E = 7.5 / SL= 6.25 / SE = 6.25 / N = 9

It is for a shift pattern spreadsheet so names are down the left hand side (Column a) then mon - sun in row 1 and then depending on the shift worked the letter shown in the spreadsheet - then i need to show a column with total hours worked - depending on the shift letters. So if someone has worked 3 L shifts and 4 N shifts total hours worked would show as 58.5

My excel knowledge is pretty basic and i have been looking and believe this could be done using a lookup table but i cant work out what formula to put in the total hours column to get it show

Any help would be very much apprecaiated

Thanks

Lisa

IMAGE.jpg

**benishiryo** · 04-15-2013, 09:22 AM

hi Lisa, welcome to the forum. assuming data in B4:H4, try:
=SUMPRODUCT(--(LOOKUP(B4:H4,{"E";"L";"N";"SE";"SL"},{7.5;7.5;9;6.25;6.25})))

do note that my alphabets are arranged in ascending order or it won't work. you could also put the alphabets in column K & values in column L, then:
=SUMPRODUCT(--(LOOKUP(B4:H4,K4:K8,L4:L8)))

**Tony Valko** · 04-15-2013, 09:29 AM

Yes, create this 2 column table:

L.....7.5
E.....7.5
SL...6.25
SE...6.25
N.....9

Assume that table is in the range A10:B14.

B2:H2 = shift codes worked

Then, the formula would be:

=SUMPRODUCT(SUMIF(A10:A14,B2:H2,B10:B14))