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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Philip J Smith - 23 Feb 2007 09:10 GMT

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

Reply to this Message

Ron Rosenfeld - 23 Feb 2007 12:44 GMT

>When I use {=FREQUENCY(INDIRECT($A$1),Bin_Range)} I get a column of #REF!.
>
[quoted text clipped - 10 lines]
>
>Phil

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

Reply to this Message

Philip J Smith - 23 Feb 2007 13:36 GMT

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

> >When I use {=FREQUENCY(INDIRECT($A$1),Bin_Range)} I get a column of #REF!.
> >
[quoted text clipped - 14 lines]
> workbook?
> --ron

Reply to this Message

Toppers - 23 Feb 2007 13:41 GMT

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

> When I use {=FREQUENCY(INDIRECT($A$1),Bin_Range)} I get a column of #REF!.
>
[quoted text clipped - 10 lines]
>
> Phil

Reply to this Message