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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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

New column G = Worked
This column reflects the Worked status for that day

Open in new window

New column H = Consecutive Days
This column reflects the number of days worked since the last non-working day

Open in new window

New column I = Last Day Off Row
This column is the row of the last day off.

Open in new window

Updated formula for column E

Open in new window


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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

I was worried about the validity of the OFFSET($G$11,I11,0) term in the formula.
If not, try this.

Open in new window

d_melnykAuthor Commented:
Thanks... that fixed the bug! Great work!
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.