Not sure where to start

brent · Apr 2nd, 2002, 10:38 PM

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

Jay Petrulis · Apr 2nd, 2002, 10:41 PM

Quote:

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 ]

brent · Apr 2nd, 2002, 10:51 PM

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.

Jay Petrulis · Apr 2nd, 2002, 11:06 PM

Quote:

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

brent · Apr 2nd, 2002, 11:15 PM

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

Dave Hawley · Apr 3rd, 2002, 01:15 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 2nd, 2002, 10:38 PM	#1
brent New Member Join Date: Apr 2002 Posts: 3	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

Apr 2nd, 2002, 10:51 PM	#3
brent New Member Join Date: Apr 2002 Posts: 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.

Apr 2nd, 2002, 11:15 PM	#5
brent New Member Join Date: Apr 2002 Posts: 3	Jay That is it. Thanks so much. Ido have more than 4 values but that will be easy to do now, Thanks Brent

Apr 3rd, 2002, 01:15 AM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.