Not sure where to start

brent

New Member
Joined
Apr 1, 2002
Messages
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Jay
That is it. Thanks so much. Ido have more than 4 values but that will be easy to do now, Thanks
Brent
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top