Why doesn't the INDIRECT function work when nested in FREQUENCY?

P

Philip J Smith

When I use {=FREQUENCY(INDIRECT($A$1),Bin_Range)} I get a column of #REF!.

A1 can contain one of two valid range names.
The frequency function works when either of the two are entered directly or
even when an IF function replaces the INDIRECT function e.g.
{=FREQUENCY(IF($B$1=1,Range1,Range2),Bin_Range)}

In all cases the formulae are entered as array functions.

Does anyone know why it doesn't work?

Regards

Phil
 
R

Ron Rosenfeld

When I use {=FREQUENCY(INDIRECT($A$1),Bin_Range)} I get a column of #REF!.

A1 can contain one of two valid range names.
The frequency function works when either of the two are entered directly or
even when an IF function replaces the INDIRECT function e.g.
{=FREQUENCY(IF($B$1=1,Range1,Range2),Bin_Range)}

In all cases the formulae are entered as array functions.

Does anyone know why it doesn't work?

Regards

Phil


It works OK here. Could it be that your range name is referring to a closed
workbook?
--ron
 
P

Philip J Smith

Hi.

I have checked that several times, copying from a list of range names
generated from Insert, Name, Paste, Paste List.

Does it matter that the ranges are variable length ranges determined by the
OFFSET function?

e.g. tblAbsResidual
=OFFSET('ETM Cash Db and Regression'!$M$13,'ETM Cash Db and
Regression'!$Q$5,0,'ETM Cash Db and Regression'!$S$5-'ETM Cash Db and
Regression'!$Q$5,1)

Regards
Phil.
 
T

Toppers

Phil,
It does appear from further testing that you DO get an error if
the range is dynamic i.e. use OFFSET function.
 
P

Philip J Smith

Hi Ron.

No. The ranges are on the same worksheet as the Frequency analysis, thanks
for your response - please see my note about the named ranges being of
variable length using the offset function.

Regards

Phil
 
T

Toppers

Sorry ... no idea.

(I did another test with MATCH and had the same problem which (perhaps?)
infers INDIRECT doesn't work with dynamic ranges.)
 
T

Toppers

Phil,
Works OK for me. Just check range name in A1 is correct as this
will give a #REF! error if incorrect.
 
T

T. Valko

A dynamic range is actually a named *formula* and gets evaluated by Indirect
as a *formula* and not a TEXT representation of a valid reference. That's
what causes the error.

There are other ways to do this, for example, using the CHOOSE function if
you have many ranges (but less than 29) but I'd need more info to make a
specific suggestion. If you only have a couple of ranges then your use of IF
should be ok:

=FREQUENCY(IF($B$1=1,Range1,Range2),Bin_Range)

Biff
 
P

Philip J Smith

Thanks for this. It makes sense now. The IF statement works well. I will
investigate the use of the choose statement, I hadn't thought of that.

Regards
Phil
 
T

T. Valko

You're welcome. Thanks for the feedback!

Biff

Philip J Smith said:
Thanks for this. It makes sense now. The IF statement works well. I
will
investigate the use of the choose statement, I hadn't thought of that.

Regards
Phil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top