| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why doesn't the INDIRECT function work when nested in FREQUENCY?
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why doesn't the INDIRECT function work when nested in FREQUENCY?
On Fri, 23 Feb 2007 01:10:00 -0800, Philip J Smith
wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why doesn't the INDIRECT function work when nested in FREQUENC
Thanks for the confirmation :-). Do you know if this is "fixed" in Excel 2007?
"Toppers" wrote: Phil, It does appear from further testing that you DO get an error if the range is dynamic i.e. use OFFSET function. "Philip J Smith" wrote: 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. "Toppers" wrote: Phil, Works OK for me. Just check range name in A1 is correct as this will give a #REF! error if incorrect. "Philip J Smith" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why doesn't the INDIRECT function work when nested in FREQUENC
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. "Toppers" wrote: Phil, Works OK for me. Just check range name in A1 is correct as this will give a #REF! error if incorrect. "Philip J Smith" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why doesn't the INDIRECT function work when nested in FREQUENC
Phil,
It does appear from further testing that you DO get an error if the range is dynamic i.e. use OFFSET function. "Philip J Smith" wrote: 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. "Toppers" wrote: Phil, Works OK for me. Just check range name in A1 is correct as this will give a #REF! error if incorrect. "Philip J Smith" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why doesn't the INDIRECT function work when nested in FREQUENC
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 "Ron Rosenfeld" wrote: On Fri, 23 Feb 2007 01:10:00 -0800, Philip J Smith wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why doesn't the INDIRECT function work when nested in FREQUENC
Sorry ... no idea.
(I did another test with MATCH and had the same problem which (perhaps?) infers INDIRECT doesn't work with dynamic ranges.) "Philip J Smith" wrote: Thanks for the confirmation :-). Do you know if this is "fixed" in Excel 2007? "Toppers" wrote: Phil, It does appear from further testing that you DO get an error if the range is dynamic i.e. use OFFSET function. "Philip J Smith" wrote: 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. "Toppers" wrote: Phil, Works OK for me. Just check range name in A1 is correct as this will give a #REF! error if incorrect. "Philip J Smith" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why doesn't the INDIRECT function work when nested in FREQUENCY?
Phil,
Works OK for me. Just check range name in A1 is correct as this will give a #REF! error if incorrect. "Philip J Smith" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why doesn't the INDIRECT function work when nested in FREQUENC
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 "Toppers" wrote in message ... Sorry ... no idea. (I did another test with MATCH and had the same problem which (perhaps?) infers INDIRECT doesn't work with dynamic ranges.) "Philip J Smith" wrote: Thanks for the confirmation :-). Do you know if this is "fixed" in Excel 2007? "Toppers" wrote: Phil, It does appear from further testing that you DO get an error if the range is dynamic i.e. use OFFSET function. "Philip J Smith" wrote: 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. "Toppers" wrote: Phil, Works OK for me. Just check range name in A1 is correct as this will give a #REF! error if incorrect. "Philip J Smith" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why doesn't the INDIRECT function work when nested in FREQUENC
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. Valko" wrote: 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 "Toppers" wrote in message ... Sorry ... no idea. (I did another test with MATCH and had the same problem which (perhaps?) infers INDIRECT doesn't work with dynamic ranges.) "Philip J Smith" wrote: Thanks for the confirmation :-). Do you know if this is "fixed" in Excel 2007? "Toppers" wrote: Phil, It does appear from further testing that you DO get an error if the range is dynamic i.e. use OFFSET function. "Philip J Smith" wrote: 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. "Toppers" wrote: Phil, Works OK for me. Just check range name in A1 is correct as this will give a #REF! error if incorrect. "Philip J Smith" wrote: 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Why doesn't the INDIRECT function work when nested in FREQUENC
You're welcome. Thanks for the feedback!
Biff "Philip J Smith" wrote in message ... 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. Valko" wrote: 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 "Toppers" wrote in message ... Sorry ... no idea. (I did another test with MATCH and had the same problem which (perhaps?) infers INDIRECT doesn't work with dynamic ranges.) "Philip J Smith" wrote: Thanks for the confirmation :-). Do you know if this is "fixed" in Excel 2007? "Toppers" wrote: Phil, It does appear from further testing that you DO get an error if the range is dynamic i.e. use OFFSET function. "Philip J Smith" wrote: 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. "Toppers" wrote: Phil, Works OK for me. Just check range name in A1 is correct as this will give a #REF! error if incorrect. "Philip J Smith" wrote: 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Thinking that indirect should work | Excel Discussion (Misc queries) | |||
| Indirect - Why does it seem to only work sometimes? | Excel Worksheet Functions | |||
| INDIRECT function do not work when other file is closed | Excel Discussion (Misc queries) | |||
| nested Indirect to other WS | Excel Worksheet Functions | |||
| Does file need to be open for INDIRECT to another workbook to work | Excel Discussion (Misc queries) | |||
Linear Mode
