Display a letter that has a value which can be calculated

Hi I am trying to use excell to design a new rota. I am using letters to display the type of shift D (a day) and I want it to represent a number (2 sessions) which can be calculated, but in the sheet I want the letter to be left in the cell.

D = 2
L = 2.67
N = 3.2
SPA = 2

So if I put a schedule in like this
D | L | SPA - I can have a cell that calculates the total sessions at the end - but without changing the letters to numbers.

I have used the Name manager to assign numbers to letters - but this changes the letter to the assigned number.

I hope that makes sense - any help would be appreciated.
Thanks

Try this formla

Formula:

Please Login or Register  to view this content.

That worked great thank you for your quick reply.

@tudorbob You're Welcome. Glad to help . Thank You for the feedback.

If you finally get a solution please mark your thread as SOLVED:
- Click Thread Tools above your first post,you will see the word PREFIX on the upper left, press the button to select [SOLVED] select "Mark your thread as Solved".
- Please click on the *Add Reputation button at the bottom left. Whoever helps you, give it to whoever you like

Hi I have found that this is miss calculating the value - even though the terms are in "" it seems to pick up other text with the same letter in it and add it to the value of the letter. If you see the attached file you can see that I have added two letters to each term but when entering WE - wekend early the value is 4.8 instead of 2.67
Any suggestions?ROTA.xlsx

In formula I2 there is two times "WE";"WE".
I would adjust that.

Thanks, missed that. Will explore the vlookup option but this is working at present.
Great help

Cell I2 formula , Drag down

Formula:

Please Login or Register  to view this content.

That made it a lot easier - thanks
Tudor

@tudorbob You're Welcome. Glad to help . Thank You for the feedback and rep.