• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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
0
Kyle Witter
Asked:
Kyle Witter
  • 2
  • 2
1 Solution
 
Ron MalmsteadInformation Services ManagerCommented:
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
 
Ron MalmsteadInformation Services ManagerCommented:
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
 
RunriggerCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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

Open in new window


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

Result of function implemented
Enjoy!

Dave
demo-r2.xls
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now