# Average if

#### bflan0524

##### Board Regular
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

=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
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

##### Well-known Member
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

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
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

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
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.

Last edited:

#### bflan0524

##### Board Regular
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

Replies
16
Views
193
Replies
3
Views
231
Replies
26
Views
476
Replies
0
Views
525
Replies
37
Views
1K

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.

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.

### Which adblocker are you using?

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

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