Average if

bflan0524

Board Regular
Joined
Oct 7, 2016
Messages
184
Office Version
  1. 2010
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)
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
When you say "its not working", How do you mean?

Wrong answer or it gives an Error
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,302
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019
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.
 
Last edited:

bflan0524

Board Regular
Joined
Oct 7, 2016
Messages
184
Office Version
  1. 2010

ADVERTISEMENT

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

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,425
Office Version
  1. 2019

ADVERTISEMENT

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.
 
Last edited:

bflan0524

Board Regular
Joined
Oct 7, 2016
Messages
184
Office Version
  1. 2010
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,302
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

bflan0524

Board Regular
Joined
Oct 7, 2016
Messages
184
Office Version
  1. 2010
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,951
Messages
5,834,538
Members
430,295
Latest member
amdis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top