Excel function to calculate based on conditions
I'm working on a spreadsheet that calculates overtime based on criteria given in another worksheet. I've attached a demo to get a better idea. I'm trying to do this in Excel 2010. Basically I have 6 employees (person 1 - person 6). Five of those employees don't get overtime, so their hours cannot be counted. I've created another worksheet (Payroll) that has a simple table with employee names and their rate and their status. Status "H" allows them to receive overtime, so therefore I want them to calculate overtime in the "overtime hours" field on the (Hours) worksheet. The trickiest of it all is that I don't want it to calculate hours for anything LESS than 40 hours. In the demo I've attached, the example I'm using should output 5 in the Overtime hours column, though there is a possibility at a later date that there will be more employees with the "H" status, so I need it to be able to look out and disqualify employees based on that criteria. I hope this makes sense. demo.xlsx
You might try doing it like this...
h/s date1 date2 date3 date4 Total hours total OT hours
Emp1 n 45
Emp2 n 44
Emp3 y 40
h/s date1 date2 date3 date4 Total hours total OT hours
Emp1 n 45
Emp2 n 44
Emp3 y 40
There isn't a simple way to make this completely dynamic, however, the attached attempts to demonstrate how to do this with a lookup on row 3 and if conditions on row 8
Overtime-Calculation.xlsx
Overtime-Calculation.xlsx
If you put the following formula in cell H3, confirming the array function with ctrl-shift-enter, you don't have to reconfigure your spreadsheet:
=SUMPRODUCT(($B3:$G3>40)*(
will look like:
{=SUMPRODUCT(($B3:$G3>40)*
if you pull up the formula and hit CTRL-SHIFT-ENTER
Presumtion - employee order from left to right is the same as the payroll tab, from C2 to C7.
However, if you reconfigure the payroll tab, the TRANSPOSE and CTRL-SHIFT-ENTER for array function will not be needed.
This is what I've done in the attached...
And the revised formula (with the Payroll tab data transposed) is:
=SUMPRODUCT(($B3:$G3>40)*(
=$B3:$G3>40 - returns an array of True/False for data > 40 hours
=$B3:$G3-40 - returns the hours worked less 40\
=Payroll!$B$3:$G$3="H" - returns an array of True/False for employees having "H" status
When all is multiplied, only the hours > 40 for employees with status of "H" for hourly are accounted for.
This formula can be freely copied down.
See attached.
Enjoy!
Dave
demo-r1.xlsx
=SUMPRODUCT(($B3:$G3>40)*(
will look like:
{=SUMPRODUCT(($B3:$G3>40)*
if you pull up the formula and hit CTRL-SHIFT-ENTER
Presumtion - employee order from left to right is the same as the payroll tab, from C2 to C7.
However, if you reconfigure the payroll tab, the TRANSPOSE and CTRL-SHIFT-ENTER for array function will not be needed.
This is what I've done in the attached...
And the revised formula (with the Payroll tab data transposed) is:
=SUMPRODUCT(($B3:$G3>40)*(
=$B3:$G3>40 - returns an array of True/False for data > 40 hours
=$B3:$G3-40 - returns the hours worked less 40\
=Payroll!$B$3:$G$3="H" - returns an array of True/False for employees having "H" status
When all is multiplied, only the hours > 40 for employees with status of "H" for hourly are accounted for.
This formula can be freely copied down.
See attached.
Enjoy!
Dave
demo-r1.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For example...
In cell G3 on your Hours sheet.... The following formula would work, except that it will not transpose properly as you copy paste it to below cells...
=SUM(IF(Payroll!C2="H",IF(
Checks to see if employee is H or S..."hourly/salary", before calculating OT.
The way it is layed out however, with Emps at top rather than at the side,..makes transposing the formula impossible. Therefore you would have to manually type this in and transpose the cell numbers yourself.