Giving letters a numerical value, then adding the total.
 

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.

Giving letters a numerical value, then adding the total.
 

Insert/ Name
--
David Biddulph

"Richard in Stockholm" <Richard in
wrote in message ...
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.

Giving letters a numerical value, then adding the total.
 

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


"Richard in Stockholm" wrote:

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.

Giving letters a numerical value, then adding the total.
 

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

"David Biddulph" wrote:

Insert/ Name
--
David Biddulph

"Richard in Stockholm" <Richard in
wrote in message ...
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.

Giving letters a numerical value, then adding the total.
 

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

Richard

"FSt1" wrote:

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


"Richard in Stockholm" wrote:

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.

Giving letters a numerical value, then adding the total.
 

One way

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


you can just add more letter and their respective times




--
Regards,

Peo Sjoblom


"Richard in Stockholm" <Richard in
wrote in message ...
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.

Giving letters a numerical value, then adding the total.
 

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.
--
David Biddulph

"Richard in Stockholm" wrote
in message ...
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

"David Biddulph" wrote:

Insert/ Name
--
David Biddulph

"Richard in Stockholm" <Richard in
wrote in message
...
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.

Giving letters a numerical value, then adding the total.
 

Try this in Swedish

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



--
Regards,

Peo Sjoblom



"Richard in Stockholm" wrote
in message ...
Thanx - my swedish version of excel 2007 didn't like that but I'm trying
all
variations as we speak.

Richard

"FSt1" wrote:

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


"Richard in Stockholm" wrote:

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.

Giving letters a numerical value, then adding the total.
 

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??

"David Biddulph" wrote:

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.
--
David Biddulph

"Richard in Stockholm" wrote
in message ...
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

"David Biddulph" wrote:

Insert/ Name
--
David Biddulph

"Richard in Stockholm" <Richard in
wrote in message
...
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.

Giving letters a numerical value, then adding the total.
 

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

--
Regards,

Peo Sjoblom


"Richard in Stockholm" wrote
in message ...
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??

"David Biddulph" wrote:

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.
--
David Biddulph

"Richard in Stockholm"
wrote
in message ...
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

"David Biddulph" wrote:

Insert/ Name
--
David Biddulph

"Richard in Stockholm" <Richard in

wrote in message
...
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.

Giving letters a numerical value, then adding the total.
 

fantasktiskt !!!!

Gud jag vill krama dig !!!!

Richard

"Peo Sjoblom" wrote:

Try this in Swedish

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



--
Regards,

Peo Sjoblom



"Richard in Stockholm" wrote
in message ...
Thanx - my swedish version of excel 2007 didn't like that but I'm trying
all
variations as we speak.

Richard

"FSt1" wrote:

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


"Richard in Stockholm" wrote:

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.

Giving letters a numerical value, then adding the total.
 

That helps me on my way too - thank you

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom


"Richard in Stockholm" wrote
in message ...
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??

"David Biddulph" wrote:

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.
--
David Biddulph

"Richard in Stockholm"
wrote
in message ...
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

"David Biddulph" wrote:

Insert/ Name
--
David Biddulph

"Richard in Stockholm" <Richard in

wrote in message
...
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.

Giving letters a numerical value, then adding the total.
 

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" wrote:

Try this in Swedish

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



--
Regards,

Peo Sjoblom



"Richard in Stockholm" wrote
in message ...
Thanx - my swedish version of excel 2007 didn't like that but I'm trying
all
variations as we speak.

Richard

"FSt1" wrote:

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


"Richard in Stockholm" wrote:

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.

Giving letters a numerical value, then adding the total.
 

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



--
Regards,

Peo Sjoblom



"Richard in Stockholm" wrote
in message ...
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" wrote:

Try this in Swedish

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



--
Regards,

Peo Sjoblom



"Richard in Stockholm"
wrote
in message ...
Thanx - my swedish version of excel 2007 didn't like that but I'm
trying
all
variations as we speak.

Richard

"FSt1" wrote:

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


"Richard in Stockholm" wrote:

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.

padding numbers in excel
 

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

padding numbers in excel
 

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

<Joe wrote in message ...
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

padding numbers in excel
 

=IF(A1<"",LEFT(A1,1)&0&RIGHT(A1,LEN(A1)-1),"")
Regards,
Alan.
"Joe" wrote in message ...
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

padding numbers in excel
 

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


"Joe" wrote in message ...
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