Complicated "IF then Else" excel formula

Hi Folks:
   I am trying to create a spreadsheet to calculate overtime amounts owing based on a few factors:. The parameters are as follows:

     - there are two columns  one for job A (Classes taught) and one for job B (Customer Service Hours);
     - the number of rows in the spread sheet is 365 (one for each day of the year);
     - The rate of pay for Job A is a fixed amount per class taught;
     - The rate of pay for Customer service hours worked will incur overtime charges as follows:
          - if the individual has "worked" more than five days (i.e. taught a class OR worked customer service or both) in a row then on the 6th  
            day Customer Service hours are paid at time and a half. (1.5 times customer service rate)
          - On the 7th and subsequent days, customer service hours are paid at double time (2 times customer service rate) as long
             as the person has either taught a class or worked customer service hours.
     - The overtime payment cycle is only broken when there is a day where the person has NOT taught a class and NOT worked
        any customer service hours.

       The cycle can repeat - i.e. works six days in a row, the 6th day customer service hours are paid at time and a half and on the seventh day customer service hours will be paid at double time. On 8th day no class and no customer service hours. Starting on 9th day the cycle would reset; The cycle would "reset" anytime there is a day when no classes are taught and no customer service hours are worked.

I have attached a preliminary spreadsheet example.

I am reasonably fluent with Excel in a basic way, but this one is some what beyond me... any help would be greatly appreciated.

Best regards, Dave Melnyk
Overtime.xls
d_melnykAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
I think it would be helpful if you put some data into the workbook, reflecting different working scenarios that you've described above.
0
d_melnykAuthor Commented:
Hi aikimark ... I have attached the spreadsheet with some sample data  included
Overtime.xls
0
aikimarkCommented:
In the attached workbook you will find the following changes:

New column G = Worked
This column reflects the Worked status for that day
=INT(OR(B11<>0,D11<>0))

Open in new window


New column H = Consecutive Days
This column reflects the number of days worked since the last non-working day
=SUM(OFFSET($G$11,I11,0):G11)*G11

Open in new window


New column I = Last Day Off Row
This column is the row of the last day off.
=SUMPRODUCT(MAX(($G$11:G11=0)*ROW($G$11:G11)))

Open in new window


Updated formula for column E
=D11*($C$5*(IF(H11<6,1,IF(H11<7,1.5,2))))

Open in new window

Overtime.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
You can hide the new columns
0
d_melnykAuthor Commented:
Thanks for the assistance... I had kind of figuredthat a few counters were needed... great work!
0
aikimarkCommented:
Did it pass your tests?

I was worried about the validity of the OFFSET($G$11,I11,0) term in the formula.
0
aikimarkCommented:
If not, try this.
=SUM(OFFSET($G$11,IF(I11=0,0,I11-11),0):G11)*G11

Open in new window

0
d_melnykAuthor Commented:
Thanks... that fixed the bug! Great work!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.