Excel function to calculate based on conditions

Posted on 2011-04-25
Last Modified: 2012-05-11
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
Question by:Kyle Witter
    LVL 25

    Expert Comment

    by:Ron M
    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...


    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.
    LVL 25

    Expert Comment

    by:Ron M
    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

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


    will look like:


    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:


    =$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.


    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

    Open in new window

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

    Result of function implemented


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    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 …
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now