Menu

Adding Multiple Lines when the Function is Satisfied

Search this Thread

Prev

Previous Post

Next Post

Next

#2

Report Post

Posted to microsoft.public.excel.worksheet.functions

Sheeloo

Sheeloo is offline

external usenet poster

Posts: 793

Default

Adding Multiple Lines when the Function is Satisfied

Pl. send the file (with actual data) to me at

"pugsly8422" wrote:

I am having a few problems trying to come up with a function that can satisfy
this problem. Sometimes one employee may appear under more than one group
(bartender, barback, waitstaff, etc.). I need to insert a formula that will
check the other groups to see if this employee worked any hours in another
group that week (I would assume a VLOOKUP would work), and then distribute
the overtime hours accordingly. Right now, the function I am using is:

=IF(A2="","",MAX(0,SUMPRODUCT(($A$2:$A$53=A2)*ISNU MBER($D$2:$H$53),($D$2:$H$53))-40))

It is hard to explain what I am trying to do, but I'll do my best. This is
a timesheet and sometimes the same employee will appear in 2 different places
in a single week. It may look something like this:

Bartender: Mon Tue Wed Thu Fri Sat Sun Total O/T
Bob 8 5 6 9 2 28 2

Barback: Mon Tue Wed Thu Fri Sat Sun Total O/T
Bob 3 3 2 8 3 12 7

What this is saying is how many hours he worked at each position on each
day, and because there are different pay rates for each position, we have to
seperate them. Also, once someone reaches 40 hours total, they begin earning
overtime. I need a function that will check the other categories (bartender,
barback, waitstaff, host, training) to see if the employee worked any other
hours. If they did work additional hours I need it to add the hours up a day
at a time so that when it reaches 40 they will begin earning overtime. In
the example above, Bob had 40 hours halfway through Friday, so everything
after that was overtime. He worked 28 "normal" hours and 2 "overtime" hours
bartending and 12 "normal" hours and 12 "overtime" hours barbacking.

The only thing I can come up with is:

if(bobs name appears more than once,if(bob #1 monday hours+bob #2 monday
hours+bob #3 monday hours+bob #4 monday hours+bob #5 monday hours40,bob #1
monday thru sunday hours+bob #2 monday thru sunday hours+bob #3 monday thru
sunday hours+bob #4 monday thru sunday hours+bob #5 monday thru sunday
hours-40,if(bob #1 monday thru tuesday hours+bob #2 monday thru tuesday
hours+bob #3 monday thru tuesday hours+bob #4 monday thru tuesday hours+bob
#5 monday thru tuesday hours40,bob #1 monday thru sunday hours+bob #2 monday
thru sunday hours+bob #3 monday thru sunday hours+bob #4 monday thru sunday
hours+bob #5 monday thru sunday hours-40,if(bob #1 monday thru wednesday
hours+bob #2 monday thru wednesday hours+bob #3 monday thru wednesday
hours+bob #4 monday thru wednesday hours+bob #5 monday thru wednesday
hours40,bob #1 monday thru sunday hours+bob #2 monday thru sunday hours+bob
#3 monday thru sunday hours+bob #4 monday thru sunday hours+bob #5 monday
thru sunday hours-40..........,if(bob #1 monday thru sunday hours+bob #2
monday thru sunday hours+bob #3 monday thru sunday hours+bob #4 monday thru
sunday hours+bob #5 monday thru sunday hours40,bob #1 monday thru sunday
hours+bob #2 monday thru sunday hours+bob #3 monday thru sunday hours+bob #4
monday thru sunday hours+bob #5 monday thru sunday hours-40)))))))

I doubt that will fit, and am thinking there must be an easier way to do it.
After spending over a week on this I am about ready to pound my head on the
wall, hopefully someone can assist me, it will be greatly appreciated. If
there is any other information you need feel free to ask. I could also send
you the workbook if that would make it easier.

Thank you in advance.

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Not sure which function to use - need multiple lines of data	Bob	Excel Worksheet Functions	1	January 14th 08 06:06 PM
Function for adding lines	dpal	Excel Worksheet Functions	8	September 6th 07 07:57 PM
Adding a single function onto multiple cells that already have Num	J@Y	Excel Discussion (Misc queries)	5	January 23rd 07 08:07 PM
List Function - Adding new lines w/ formulas	Arun	Excel Discussion (Misc queries)	4	January 16th 07 08:12 PM
Adding Multiple Lines On A Secondary Axis?	al	Charts and Charting in Excel	1	November 23rd 06 11:17 AM

All times are GMT +1. The time now is 07:37 AM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement