Not sure where to start
Not sure where to start
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Not sure where to start

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com