Variable named range in worksheet function

[Barb Reinhardt]

I'd like to use a named range of "FactorA" (which contains multiple cells),
etc, and I'm trying to do something like this

=AVERAGE(IF(INDIRECT("Factor" &$B17)=$D17,IF($F$2:$F$9=E$16,$R$2:$R$9)))

Where B17 contains the A. Can I do this?

Thanks,
Barb Reinhardt

 

[Peo Sjoblom]

Yes. For that particular formula to work the range should be of equal size
to the other cell ranges or else you will probably get a N/A error

--


Regards,


Peo Sjoblom

 

[Barb Reinhardt]

I'm actually getting a REF error when I put the named range into the
Indirect.

This formula works fine (in an adjacent cell)
=AVERAGE(IF($F$2:$F$9=$D18,IF($F$2:$F$9=F$16,$R$2:$R$9)))

And the range name is currently defined to be $F$2:$F$9. I still commit it
with CTRL SHIFT ENTER.

Thanks,
Barb Reinhardt

 

[Barb Reinhardt]

FWIW, when I step through the calculation, I get REF on the INDIRECT("Factor"
& $B17) part.
Thanks,
Barb Reinhardt

 

[Peo Sjoblom]

How does your named range look, I assumed you used a straightforward one
like
Sheet!!$A$2:$A$9
if you use offset to make it dynamic it won't work


--


Regards,


Peo Sjoblom

 

[Barb Reinhardt]

I used OFFSET. Is that my problem? I like OFFSET. Why won't it work that
way?

Thanks,
Barb Reinhardt

 

[T. Valko]

See this:

http://tinyurl.com/6lufyy