why is my average if statement not working

[sullyman4]

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

 

[Aladin Akyurek]

And "not working" means...

 

[sullyman4]

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

 

[pgc01]

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

 

[dcardno]

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.

 

[sullyman4]

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

 

[Aladin Akyurek]

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

 

[sullyman4]

thats it
thanks