Skip to main content

Hey everyone! Thanks so much for all the help you’ve given, I’m still learning and this community has made it a great experience!

I think what I’m trying to do can be solved with nested IF statements, just can’t seem to find the definitive answer looking through old posts. Here’s what I want to do.

I want to calculate the number of hours it takes to do a certain job for a certain employee, where each employee takes a different amount of time for that job.

For example, Employee 1 takes 4 hours to do Task 1, but Employee 2 takes 3 hours to do the same task. There are a bunch of employees and a bunch different tasks, and the hours are different for each employee. Is there a not so messy way to do this?

Thanks so much for the support!

EDIT: this is the formula that I have that works for one Employee and one task

IF(
AND(
{Employee} = “Employee 1”,
TASK = “TASK 1”
),
“4”
)

Where it will show 4 hours for Employee 1 to do task 1.

Where I get confused is how to nest a whole lot of these to cover all possible coniditions. Basically, for each employee, there could be any one of 8 tasks assigned, and the hours for each employee differ for each task.

If there is a much easier way to do this, I’m all ears!

Sounds like you need a pivot table. Complete the grid as you have it in the image and then use the pivot table app to do the sums by task by employee.

There is. It’s called the SWITCH() function. It’s a much more compact method of generating output based on specific input. The basic format looks like this:

SWITCH(
    INPUT_SOURCE,
    OPTION_1, OUTPUT_1,
    OPTION_2, OUTPUT_2,
    OPTION_3, OUTPUT_3,
    ...,
    OPTIONAL_DEFAULT_OUTPUT
)

If INPUT_SOURCE matches OPTION_1, then the function returns OUTPUT_1; if it matches OPTION_2, then OUTPUT_2 is returned, and so on. If none of the comparison options are matched, it returns the OPTIONAL_DEFAULT_OUTPUT if one has been provided, or nothing if not.

In this case, I can see the formula being a series of nested SWITCH() functions:

SWITCH(
    Employee,
    "Employee 1", SWITCH(
        TASK,
        "TASK 1", 4,
        "TASK 2", 2,
        ...etc
    ),
    "Employee 2", SWITCH(
        TASK,
        "TASK 1", 3,
        "TASK 2", 5,
        ...etc
    ),
    ...etc
)

There is. It’s called the SWITCH() function. It’s a much more compact method of generating output based on specific input. The basic format looks like this:

SWITCH(
    INPUT_SOURCE,
    OPTION_1, OUTPUT_1,
    OPTION_2, OUTPUT_2,
    OPTION_3, OUTPUT_3,
    ...,
    OPTIONAL_DEFAULT_OUTPUT
)

If INPUT_SOURCE matches OPTION_1, then the function returns OUTPUT_1; if it matches OPTION_2, then OUTPUT_2 is returned, and so on. If none of the comparison options are matched, it returns the OPTIONAL_DEFAULT_OUTPUT if one has been provided, or nothing if not.

In this case, I can see the formula being a series of nested SWITCH() functions:

SWITCH(
    Employee,
    "Employee 1", SWITCH(
        TASK,
        "TASK 1", 4,
        "TASK 2", 2,
        ...etc
    ),
    "Employee 2", SWITCH(
        TASK,
        "TASK 1", 3,
        "TASK 2", 5,
        ...etc
    ),
    ...etc
)

Thank you for this, I will experiment!

Sounds like you need a pivot table. Complete the grid as you have it in the image and then use the pivot table app to do the sums by task by employee.


Never used that app, i will try it out. Thanks

Terms & ConditionsAccessibility statement