# Computing an average based on conditional criteria

I am creating a spreadsheet in excel. I have one column with employee name, hourly wage, hours worked, and cost. I want an additional column (At work) that has a Yes or No option.

If yes then include the hourly wage in this column, if no, do not include it.

So at the bottom I can average daily wages (of the yes, at work) The yes or no is if they are at work.

-

## migrated from webapps.stackexchange.comMar 23 '13 at 21:33

This question came from our site for power users of web applications.

Make sure to add the Excel version for the people at Super User. – Jacob Jan Tuinstra Mar 23 '13 at 21:07
So what is your question? – Peter L. Mar 23 '13 at 21:43
Is the "daily wage" the value of the "cost" column? Which is presumably calculated from ("hourly wage" * "hours worked")? – w3dk Mar 23 '13 at 22:26
Just for clarification, do the edits to your question yesterday accurately reflect what you're trying to accomplish? The changes substantively change what the appropriate answer would be. – dav Mar 25 '13 at 11:58

Assuming you have a table like the following

``````name    wages   hrs worked  cost    at work
Bob     3       8           24      Y
Sally   4       8           32      N
``````

You can use the following formula at the bottom to determine average hourly wage

``````=SUMIFS(B2:B10,E2:E10, "Y") / COUNTIF(E2:E10, "Y")
``````

This will sum wages where there is a Y in 'at work' column and divide by the count of those people.

-

Assuming the revised question, and a table like the following:

``````name    wages   hrs worked  cost    at work
Bob     3       8           24      Y
Sally   4       0           0       N
``````

The simplest way to get a conditional average is to use =AVERAGEIF,

``````=AVERAGEIF(E2:E3,"Y",D2:D3)
``````

This formula will calculate the average of all the cells in `[cost]` column, where the value in the `[at work]` column is equal to "Y".

Back to your original question, the [at work] column can be calculated as

``````=IF(C2>0,"Y","N")
``````

As an alternative (but not a very good one), you could simply use the IF statement to return either the cost or a blank, and then return the average of the column. In this case, the blanks aren't used in the average calculation (unlike a 0, which would be used). Here's the alternate formula:

``````=IF(C2>0,D2,"")
``````

In which case the table would look like this (with an intentional blank for Sally at work):

``````    name    wages   hrs worked  cost    at work
Bob     3       8           24      24
Sally   4       0           0
``````
-