Giving letters a numerical value, then adding the total.

[Guest]

I do a lot of timetables where we use letters of the alphabet to represent
different shifts for example E = 8.25 hrs work, E1 = 8 hours. I usually do
all the timetables in excel. I was wondering how do I assign numerical values
to each letter/code so that it adds up the total time in one cell? I want the
letters to remain but for them to have numerical values and then have one
cell that adds up all the hours worked as a number.

For example if one person works E, E, E, E on four different days (i.e. 8.25
hours on four different days/cells) how do I get excel to give E an value of
8.25 so that the box that adds up the total values shows the total (33)?

Is there a function where I can give values to specific letters in a block
rather than putting in a formula in each cell (if there is one of course) ?

This sounds rather complicated but would save me a lot of work. I would be
sooooo grateful to anyone who could help me.

 

[David Biddulph]

Insert/ Name

 

[Guest]

hi,
you might try a helper column with an if formula...

=IF(B3="E",8.25,IF(B3="E1",8,0))

then sum the helper column. not sure how many codes you have but you can
nestle 7 codes in an if formula. the formula i supplied has 2.

Regards
FSt1

 

[Guest]

Thank you for replying but I didn't really understand that - I'm a bit slow
on the Excel wagon. Could you possibly expand ??

Richard

 

[Guest]

Thanx - my swedish version of excel 2007 didn't like that but I'm trying all
variations as we speak.

Richard

 

[Peo Sjoblom]

One way

=SUMPRODUCT(COUNTIF(A1:A100,{"E";"E1"}),{8;8.25})


you can just add more letter and their respective times

 

[David Biddulph]

Insert/ Name/ Define
E
refers to
=8.25

Insert/ Name/ Define
E_1
refers to
=8

If you use the formula =E+E_1, you'll get the result 16.25
If you use the formula =E+E+E+E (or =4*E), you'll get the answer 33.

 

[Peo Sjoblom]

Try this in Swedish

=PRODUKTSUMMA(ANTAL.OM(A1:A100;{"E";"E1"});{8;8,25})

 

[Guest]

Thanks - it's helped me part way - I discovered that I can use the sum
function with this - is there any way you can ?? rather than putting in the
E+E /E*4 formula??

 

[Peo Sjoblom]

I gave you a single formula solution without having to go and define any
names

 

[Guest]

fantasktiskt !!!!

Gud jag vill krama dig !!!!

Richard

 

[Guest]

That helps me on my way too - thank you

I gave you a single formula solution without having to go and define any
names

 

[Guest]

Have just remebered that the computers at work are in English (as opposed to
my computer that is in Swedish), how would this formula be on English excel ??

 

[Peo Sjoblom]

=SUMPRODUCT(COUNTIF(A1:A100,{"E";"E1"}),{8;8.25})

 

[Joe]

I have a similar problem I'm hoping someone can help me with. I have a fairly large spreed sheet in excel 25000 rows by 350 columns (so lots of numbers). In each cell, there is a value that is made up of letters and numbers such as:

h01a01
y02d07
y120g06
y89a05

The first letter has a single meaning (th project).

The number(s) that follows this first letter, 01, 02, 89, 120 also refer to a single unit -that is there is a number 1, 2, 89, 120 and so on. The problem is that using the sort function in excel returns the number 89 after 120 as shown above because excel reads the first number "1" of 120 and the "8" of 89 and says "hey 1 come before 8". Of course I realize the solution is to put a "0" in fromnt of 89 so that the number is 089 -problem solved by I have no idea how to write a formula in excel to take care of the problem. PLEASE HELP! Thanks, Joe

 

[David Biddulph]

=LEFT(A1)&TEXT(MID(A1,2,LEN(A1)-4),"000")&RIGHT(A1,3)

 

[Alan]

=IF(A1<>"",LEFT(A1,1)&0&RIGHT(A1,LEN(A1)-1),"")
Regards,
Alan.
in message news:[email protected]...

 

[Rick Rothstein \(MVP - VB\)]

Assuming 1 through 9 have the single leading zero as show for 01 and 02 (and
assuming your first value is in A1)...

=IF(LEN(A1)=6,REPLACE(A1,2,0,"0"),A1)

and copy down.

Rick


in message news:[email protected]...