Solved

# Excel function to calculate based on conditions

Posted on 2011-04-25
275 Views
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
0
Question by:Kyle Witter

LVL 25

Expert Comment

This is somewhat of a task because of the way you have it layed out...

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(B3>40,B3-40,0),0)+IF(Payroll!C3="H",IF(C3>40,C3-40,0),0)+IF(Payroll!C4="H",IF(D3>40,D3-40,0),0)+IF(Payroll!C5="H",IF(E3>40,E3-40,0),0)+IF(Payroll!C6="H",IF(F3>40,F3-40,0),0)+IF(Payroll!C7="H",IF(G3>40,G3-40,0),0))

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

LVL 25

Expert Comment

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

0

LVL 11

Expert Comment

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
0

LVL 41

Expert Comment

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)*(\$B3:\$G3-40)*TRANSPOSE(Payroll!\$C\$2:\$C\$7="H"))

will look like:

{=SUMPRODUCT((\$B3:\$G3>40)*(\$B3:\$G3-40)*TRANSPOSE(Payroll!\$C\$2:\$C\$7="H"))}

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)*(Payroll!\$B\$3:\$G\$3="H"))

=\$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
0

LVL 41

Accepted Solution

However, as a kicker, let's assume Person 1-6 and beyond is not the same order as what's in the Payroll tab (the above formula options assume that).  Let's also assume we don't want to do iterative vlookups or the like, or build even more sophisticated SUMPRODUCT statements...

There are really a few basic tells:  what is the employee's name, what are the hours worked, by employee, and what's the employee's status...

I wrote a quick Function calcOvertime(empRng As Range, hourRng As Range, empStatus As Range) As Double as follows:

``````Function calcOvertime(empRng As Range, hourRng As Range, empStatus As Range) As Double
Dim emply As Range
Dim emplyStatus As Range
Dim emplyCol As Long

emplyCol = 1
For Each emply In empRng
Set emplyStatus = empStatus.Find(emply.Value)
If Not emplyStatus Is Nothing Then
If emplyStatus.Offset(0, 2).Value = "H" And hourRng.Cells(1, employcol).Value > 40 Then
calcOvertime = hourRng.Cells(1, emplyCol).Value - 40 + calcOvertime
End If
End If
emplyCol = emplyCol + 1
Next emply

End Function
``````

See attached, in Cell H3, where this function is used.

Enjoy!

Dave
demo-r2.xls
0

## Featured Post

### Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …