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

x
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.

Commented:
I think it would be helpful if you put some data into the workbook, reflecting different working scenarios that you've described above.
Author Commented:
Hi aikimark ... I have attached the spreadsheet with some sample data  included
Overtime.xls
Commented:
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))
``````

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
``````

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)))
``````

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

Experts Exchange Solution brought to you by

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

Commented:
You can hide the new columns
Author Commented:
Thanks for the assistance... I had kind of figuredthat a few counters were needed... great work!
Commented:
``````=SUM(OFFSET(\$G\$11,IF(I11=0,0,I11-11),0):G11)*G11