Finding an average based on criteria from other cells

[TnD_Guy]

Okay, so here is my spreadsheet:

LINK

Here is what I'm trying to do. I want to know the average total days that were worked by people that meet various criteria.

1) Like, I wanna know the average total days worked by workers in Dept. A1. Is there a formula that will provide averages for only entries that are in the same row as entries for Dept. A1?

2) Or what if I want to know the average total days worked by workers in Dept. B3, but only those with a "Y" value in the "Certified?" column? Like, I want to know the average days worked by people who were both Dept. B3 workers and also Certified.

[Pete_UK]

It would help if you attached a sample Excel workbook, rather than a link to a picture of one.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

There is a function AVERAGEIFS which will calculate the average for several criteria, but I cannot try it out on your picture.

Pete

[TnD_Guy]

I've attached the actual book to this reply, in case that helps.


Thank you

[Pete_UK]

Your first query could be answered by this formula (e.g. in H2):

=AVERAGEIFS(D2:D20,E2:E20,"A1")

The second one can be achieved with this formula (e.g. in I2):

=AVERAGEIFS(D2:D20,E2:E20,"B3",F2:F20,"Y")

The first parameter in the AVERAGIFS function is the range that you want to work the average out for, and that is then followed by pairs of parameters which set up the criteria that you want to apply, so you have:

=AVERAGEIFS(average_range, criteria1_range, criteria1, criteria2_range, criteria2 and so on .....)

Hope this helps.

Pete

[TnD_Guy]

The first parameter in the AVERAGIFS function is the range that you want to work the average out for, and that is then followed by pairs of parameters which set up the criteria that you want to apply, so you have:

=AVERAGEIFS(average_range, criteria1_range, criteria1, criteria2_range, criteria2 and so on .....)

Hope this helps.

Pete

Very helpful, thank you!!

[Pete_UK]

You're welcome - thanks for the rep.

Pete