Average if

bflan0524

Board Regular
Joined
Oct 7, 2016
Messages
174
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)
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
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
55,974
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,350
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
174

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,066
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,350

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
174
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
55,974
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
174
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,637
Members
414,398
Latest member
dhune

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