# why is my average if statement not working

#### sullyman4

##### Board Regular
here is my averageif statement
=average(if(office=b8,roa4)

Ctrl enter shift

Office is a column named range
B8 is the result of an Index statement that returns a name of an office from the Office Range and ROA4 is another named range that contain the number I want to average.

so the statement should look in cell b8 which says Cinn, then it should average all the numbers in the Roa4 column where the name in office = Cinn

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
And "not working" means...

I get 0.00 with out using Ctrl shift enter and #num! when I use Ctrl Sh enter.
the answer should be 1.4

Hi sullyman4

A guess. You are using an array formula. An array formula does not allow whole columns.

Are office and roa4 whole columns? If that is the case you have to replace them with ranges with identical size.

Ex: Office refers to B:B and roa4 refers to C:C

Change to Office refers to B1:B1000 and roa4 refers to C1:C1000.

Hope it helps
PGC

You are short a right-paren on the formula posted...

I can create the same formula and it works for me. If the tested value isn't found in the index range ("office" in your example), I get a #Div/0! error.

It works when I don't use named ranges (whole Columns) in my formulas. It doesn't make sense but what do I know. maybe there is something else..

thanks

It works when I don't use named ranges (whole Columns) in my formulas. It doesn't make sense but what do I know. maybe there is something else..

thanks

If the names you define refer to whole columns like B:B, formulas that operate on arrays cannot process them (which is by design).

thats it
thanks

Replies
0
Views
142
Replies
4
Views
173
Replies
1
Views
361
Replies
7
Views
328
Replies
12
Views
405

1,206,711
Messages
6,074,479
Members
446,071
Latest member
gaborfreeman

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

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