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)

 

[bflan0524]

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.

so good news and bad news haha
this worked, i had a formula that was returning the # before from a diff column =LEFT(L2,1) thats how i was getting the #, so when i did the ISNUMBER it was false
so i did paste special values on all of column M now, but i still get the #DIV error

 

[Special-K99]

Your initial description of the formula contains TWO quotes next to each other before the 16. Typo?

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

 

[Special-K99]

in a blank column lets say N

=M1*1
copy it down the column

now copy column N
paste special over column M
delete coluimn N

you should now have all numbers in column M

 

[Joe4]

That means it is not finding any matches.

You can just change this formula:
=LEFT(L2,1)
to this to return numbers:
=LEFT(L2,1)+0

Make sure that your values words in column C match EXACTLY.
If there are any extra spaces before or after your words, they won't match exactly.
You can use the LEN function to verify that the length of the entry in column is what it should be (should be 7 for "CHANGED" and 9 for "UNCHANGED").

 

[Special-K99]

Do a =COUNTIF(C:C,"UNCHANGED")
If it comes back with 0 then you need to amend column C to contain UNCHANGED or your AVERAGEIF wont work

 

[bflan0524]

AHHh i got it, i had UNCHANGED in the formula but in the column it was unchanged

thank you everybody for the input