Link to home
Start Free TrialLog in
Avatar of d_melnyk
d_melnykFlag for Canada

asked on

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
Avatar of aikimark
aikimark
Flag of United States of America image

I think it would be helpful if you put some data into the workbook, reflecting different working scenarios that you've described above.
Avatar of d_melnyk

ASKER

Hi aikimark ... I have attached the spreadsheet with some sample data  included
Overtime.xls
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can hide the new columns
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.
=SUM(OFFSET($G$11,IF(I11=0,0,I11-11),0):G11)*G11

Open in new window

Thanks... that fixed the bug! Great work!