"averageifs(indirect..." with named ranges

[teatimecrumpet]

Hi,

I'm trying to use the formula below:

=AVERAGEIFS(INDIRECT("var1"),var2,"Y")


to average a column.

However, I'm getting "#value!" because some of the cells in the named range "var1" is "#value!" is there a way to modify the formula to avoid those cells (without removing them from the column of data".

Thanks,

 

[T. Valko]

Hi,

I'm trying to use the formula below:

=AVERAGEIFS(INDIRECT("var1"),var2,"Y")


to average a column.

However, I'm getting "#value!" because some of the cells in the named range "var1" is "#value!" is there a way to modify the formula to avoid those cells (without removing them from the column of data".

Thanks,

If you're using Excel 2007 or later those aren't good named ranges since those are also valid cell addresses. Maybe use:

var1_
var2_

If the named ranges are dynamic ranges defined using functions like OFFSET then INDIRECT("range") won't work.

Try this array formula**:

=AVERAGE(IF(var2_="Y",IF(ISNUMBER(INDIRECT("var1_")),INDIRECT("var1_"))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[teatimecrumpet]

Hi Valko,

Thanks that did the trick. I changed the named ranges to that for posting on the forum. But that is very useful to know.


Thanks Again!

 

[T. Valko]

Hi Valko,

Thanks that did the trick. I changed the named ranges to that for posting on the forum. But that is very useful to know.


Thanks Again!

You're welcome. Thanks for the feedback!