Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from Mar 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")? – w3d 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

2 Answers 2

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.

share|improve this answer

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,


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


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:


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        
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.