Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with formula please (Office 2000)

    Hi,

    Forgive me but my brain is dead tonight.
    In the attached spreadsheet is employee tips. The tips themselves are entered manually. I want to enter the revenue associated with these tips. It worked great but today I found out that 1, an employee could work both am and pm shifts and could work a banquet as well. I had set it up for am or pm only. I was "playing" with a new formula in C5 but as I said, my brain can't come up with a formula to include all variables.

    Any thoughts?

    Thank you!
    Attached Files Attached Files
    Louise

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with formula please (Office 2000)

    If someone could work AM, PM and a banquet, you need three cells per employee (to enter A, P, and . You can then use a formula like this:

    =IF(NOT(ISERROR(MATCH("A",D57,0))),C$30)+IF(NOT(ISERROR(MATCH("P",D57,0))),C$33)+IF(NOT(ISERROR(MATCH("B",D57,0))),C$36)

    See attached version.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with formula please (Office 2000)

    Hi Hans,

    Thanks a lot! Using three cells each is just fine. Question: Looking at the formula, it looks like it doesn't matter which cell is used for the A,P, or B right? Just making sure as I've not heard of the "Match" before.

    My brain thanks you very much! <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>
    Louise

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with formula please (Office 2000)

    MATCH looks for the value in any of the three cells, it doesn't matter which of the three contains an "A" etc.
    You can look up MATCH in the Excel help, or see MATCH - Excel - Microsoft Office Online.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with formula please (Office 2000)

    Hi Hans,

    Goodness! Don't you ever sleep? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I like that Office website. Same as Excel help but much nicer. I had looked up MATCH in Excel help after your response but didn't understand their explanation and how it applied to what we were doing. Your explanations are always better and make more sense! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Have a great day / night!
    Louise

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with formula please (Office 2000)

    C5, formula :

    =SUMPRODUCT(--ISNUMBER(MATCH({"A","P","B"},D57,0)),N(OFFSET(C$29,{1,4,7},0)))

    Regards
    Bosco

Posting Permissions

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