Take the 2-minute tour ×
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 webapps.stackexchange.com 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,

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

Your Answer

 
discard

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