1. ## 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!

2. ## 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.

3. ## 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>

4. ## 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. ## 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!

6. ## 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
•