Average if

[bflan0524]

in my column C i have two criteria where it says Changed or Unchanged in every row
column M i have a score ranging from 1-5

on another sheet i am trying to get the avg score for the ones that say unchanged in column C and then same for those that say Changed

this is what i had but its not working, any advice

=AVERAGEIF('Managed Interviews Jan-Jun ''16'!C:C,"UNCHANGED",'Managed Interviews Jan-Jun ''16'!M:M)

 

[Momentman]

When you say "its not working", How do you mean?

Wrong answer or it gives an Error

 

[Joe4]

I think the issue may be with how you referenced the other sheeet.
Exactly what is the name of the other sheet?
To see exactly what the sheet reference in the formula should look like, type in the equal sign in any cell from the sheet you are pasting this formula on, then go to the other sheet, select any cell, and hit enter.
Now, you should have a formula that references a cell in this sheet. Examine what that sheet reference looks like in that formula and compare it to what you have.

 

[Special-K99]

What does "not working" mean?
Returning 0 where you expect a different value?

What does this return?
=COUNTIF(C:C,"UNCHANGED")
If it says zero then none of the contents of column C contain "UNCHANGED"

Things to look for:
spaces in column C, ie "UNCHANGED " instead of "UNCHANGED"
If the data comes from the web char(160) may appear in Excel as a space but is not.

The other thing is do you really have a sheet that actually contains ONE double quote in its name? Sounds unlikely.

 

[bflan0524]

sorry, good question i get a #DIV/0 error, but i am not trying to divide anything just trying to get the AVG of the #s in that column

 

[Momentman]

sorry, good question i get a #DIV/0 error, but i am not trying to divide anything just trying to get the AVG of the #s in that column

An average is a SUM/COUNT so where a count gives zero , an average will return #Div/0 error

So check that Unchanged is spelt correctly in both places (Formula and in the range being referenced)

Cos that error indicates it cant find any cell matching the String being sought for

 

[Special-K99]

Suggests none of the cells in column C are "UNCHANGED"

Actually type UNCHANGED in one of the cells where the M cell has a non zero value and see if it returns a value.

 

[bflan0524]

I think the issue may be with how you referenced the other sheeet.
Exactly what is the name of the other sheet?
To see exactly what the sheet reference in the formula should look like, type in the equal sign in any cell from the sheet you are pasting this formula on, then go to the other sheet, select any cell, and hit enter.
Now, you should have a formula that references a cell in this sheet. Examine what that sheet reference looks like in that formula and compare it to what you have.

name of the sheets are Managed Interviews Jan-Jun '16 and Summary, the summary sheet is where i was doing the formula

 

[Joe4]

sorry, good question i get a #DIV/0 error,

Are your numbers in column M really numbers, or numbers entered as text?

Try this on one of those values in column M and see what it returns:
=ISNUMBER(M2)

If it returns FALSE, column M is text and not numeric, which is why your formula isn't working.
If the value in column M are hard-coded (and not the result of formulas), you can use Text to Columns to convert them to numbers.
If they are the result of a formula, adding something like "+0" on the end of your formula should convert them to numbers.

 

[bflan0524]

Suggests none of the cells in column C are "UNCHANGED"

Actually type UNCHANGED in one of the cells where the M cell has a non zero value and see if it returns a value.

thanks i did that formula in column C and i got a return of 574