Thanks:  0
Likes:  0

# Thread: Not sure where to start

1. First of all I am a newbie at excel. So excuse me if this is a simple problem.

I am trying to use a worksheet to plan employees schedules. I would like to sum the hour totals of each colomn/row but, here is the catch, the cell data is a range of text and numbers. To top it off the numbers are not the value of what they are. For example D=12 hours, 7=8 hours, 23=8 hours, N=12 hours. Is there a way to asign values to text etc so that the colomns/rows add up?

Thanks
Brent

2. On 2002-04-02 21:38, brent wrote:
First of all I am a newbie at excel. So excuse me if this is a simple problem.

I am trying to use a worksheet to plan employees schedules. I would like to sum the hour totals of each colomn/row but, here is the catch, the cell data is a range of text and numbers. To top it off the numbers are not the value of what they are. For example D=12 hours, 7=8 hours, 23=8 hours, N=12 hours. Is there a way to asign values to text etc so that the colomns/rows add up?

Thanks
Brent
Hi Brent,

Probably not too difficult, but will require some thought and some tinkering. Please post all the details of how the data is set up and the text/values criteria and you'll find a lot of help here.

Lookup tables, named formulas, and the like can probably be used successfully, so please let us know more details.

Bye,
Jay

[ This Message was edited by: Jay Petrulis on 2002-04-02 21:44 ]

3. It is a pretty simple spreadsheet.
Dates at the top. Persons name down the LH side. Each cell long the employees line will have the associated shift that they are working. So a typical week for Joe might be

M T W T F S S

D 7 7 N

So the guy works for shists in the week. Monday a 12 hour day, Wed/Thur a 8 hour shift beinning at 7am and a 12 hour night shift on Saturday.

4. On 2002-04-02 21:51, brent wrote:
It is a pretty simple spreadsheet.
Dates at the top. Persons name down the LH side. Each cell long the employees line will have the associated shift that they are working. So a typical week for Joe might be

M T W T F S S

D 7 7 N

So the guy works for shists in the week. Monday a 12 hour day, Wed/Thur a 8 hour shift beinning at 7am and a 12 hour night shift on Saturday.

Hi Brent,

So there are only 4 possible values, D, N, 7 or 23?

If so, and with the following:
The first employee in A2, header days in B1:H1, values in B2:H2

=(COUNTIF(B2:H2,"D")+COUNTIF(B2:H2,"N"))*12+(COUNTIF(B2:H2,7)+COUNTIF(B2:H2,23))*8

placed in cell I2 should work for you. Copy down the list.

Let us know if this works for you.

Bye,
Jay

5. Jay
That is it. Thanks so much. Ido have more than 4 values but that will be easy to do now, Thanks
Brent

6. Hi Brent

Rather than have text, why not use a Custom format, this way you could enter the number 12 in a cell, then Custom format as "N" so it shows as "N"

The other option (better) is to assign values to the Text. Go to Insert>Name>Define and type "N" in the "Names in workbook:" box, then: =12 in the "Refers to:" box, Click Add then OK. Now when you need the "N" in any cell just type: =N From now on this will have a value of 12 and you can use a normal Sum Function.

These 2 options are the correct way to set up a spreadsheet and as you are new, you should form these good habits early.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•