Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect reference causes statistical function to throw up a #NUM!
Folks,
I ran into an odd behavior when trying to use an indirect reference to a range used as an argument in a statistical function. A: 1: Data 2: 1 3: 2 4: 3 5: 4 6: 5 B2: =ADDRESS(ROW(),COLUMN($A$1)) produces €œ$A$2€ C2: =FORECAST($A$2,OFFSET(INDIRECT($B$2),1,0,4,1),OFFS ET(INDIRECT($B$2),1,0,4,1))produces a value of 1. All good so far. D2: =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(ROW(),COLUM N($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),C OLUMN($A$1))),1,0,4,1)) produces an error #NUM! This is unexpected, since the formula in C2 works fine. Interestingly enough, if you use the same INDIRECT references in cell D2 as arguments for functions like SUM(), PRODUCT(), MIN(), MAX(), etc. it works fine. Try putting the formula below on E2. However, functions like CORREL(), COVAR(), etc. do not work. E2: =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))), 1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1 ))),1,0,4,1)) produces a value of 28 as expected Is this odd or what? Any body knows why using the OFFSET(INDIRECT(ADDRESS())€¦) combination does not work for some statistical functions? Hopefully we can all learn something new. Thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect reference causes statistical function to throw up a #NUM!
The problem is not with the statistical function, its an improper use of
INDIRECT. INDIRECT gathers info to form a cell reference, and then goes to that cell and pulls the value. Thus, when INDIRECT looks at B2, it sees $A$2, and sends this to the OFFSET function. Your latter formula however, INDIRECT sees $A$2, goes there, finds 1, and sends this to OFFSET. This makes no sense to the OFFSET function, causing your error. However, the way you have your formula currently written, it could be simplified to: =FORECAST($A$2,OFFSET($A$1,1,0,4,1),OFFSET($A$1,1, 0,4,1)) Since your ADDRESS function was set to return absolute references. Might I ask why you were going through the effort of using ADDRESS/INDIRECT? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JoCa" wrote: Folks, I ran into an odd behavior when trying to use an indirect reference to a range used as an argument in a statistical function. A: 1: Data 2: 1 3: 2 4: 3 5: 4 6: 5 B2: =ADDRESS(ROW(),COLUMN($A$1)) produces €œ$A$2€ C2: =FORECAST($A$2,OFFSET(INDIRECT($B$2),1,0,4,1),OFFS ET(INDIRECT($B$2),1,0,4,1))produces a value of 1. All good so far. D2: =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(ROW(),COLUM N($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),C OLUMN($A$1))),1,0,4,1)) produces an error #NUM! This is unexpected, since the formula in C2 works fine. Interestingly enough, if you use the same INDIRECT references in cell D2 as arguments for functions like SUM(), PRODUCT(), MIN(), MAX(), etc. it works fine. Try putting the formula below on E2. However, functions like CORREL(), COVAR(), etc. do not work. E2: =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))), 1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1 ))),1,0,4,1)) produces a value of 28 as expected Is this odd or what? Any body knows why using the OFFSET(INDIRECT(ADDRESS())€¦) combination does not work for some statistical functions? Hopefully we can all learn something new. Thanks in advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect reference causes statistical function to throw up a #NUM!
Luke,
Thanks for your help. The reason I am using the ADDRESS/INDIRECT is that I am combining a series of heuristic models to analyze data where new fields and/or records can be added to the dataset in add/insert mode and the models have to find the optimal amount of data to calculate some parameters. The example I submitted was just a something I made up with the minimal properties necessary to demonstrate the problem. I am using the column headers as references to make my formulas able to auto-update when new data is incorporated, and sometimes the initial reference to the target range is an interception and that is why the ADDRESS(ROW(),COLUMN()) is so important to me. So, the question is then: If I know the row# and column# of my initial reference cell, how can I combine it into a reference that would make sense to the OFFSET function as a single-cell range (not a value) for the FORECAST calculation??? "Luke M" wrote: The problem is not with the statistical function, its an improper use of INDIRECT. INDIRECT gathers info to form a cell reference, and then goes to that cell and pulls the value. Thus, when INDIRECT looks at B2, it sees $A$2, and sends this to the OFFSET function. Your latter formula however, INDIRECT sees $A$2, goes there, finds 1, and sends this to OFFSET. This makes no sense to the OFFSET function, causing your error. However, the way you have your formula currently written, it could be simplified to: =FORECAST($A$2,OFFSET($A$1,1,0,4,1),OFFSET($A$1,1, 0,4,1)) Since your ADDRESS function was set to return absolute references. Might I ask why you were going through the effort of using ADDRESS/INDIRECT? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JoCa" wrote: Folks, I ran into an odd behavior when trying to use an indirect reference to a range used as an argument in a statistical function. A: 1: Data 2: 1 3: 2 4: 3 5: 4 6: 5 B2: =ADDRESS(ROW(),COLUMN($A$1)) produces €œ$A$2€ C2: =FORECAST($A$2,OFFSET(INDIRECT($B$2),1,0,4,1),OFFS ET(INDIRECT($B$2),1,0,4,1))produces a value of 1. All good so far. D2: =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(ROW(),COLUM N($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),C OLUMN($A$1))),1,0,4,1)) produces an error #NUM! This is unexpected, since the formula in C2 works fine. Interestingly enough, if you use the same INDIRECT references in cell D2 as arguments for functions like SUM(), PRODUCT(), MIN(), MAX(), etc. it works fine. Try putting the formula below on E2. However, functions like CORREL(), COVAR(), etc. do not work. E2: =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))), 1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1 ))),1,0,4,1)) produces a value of 28 as expected Is this odd or what? Any body knows why using the OFFSET(INDIRECT(ADDRESS())€¦) combination does not work for some statistical functions? Hopefully we can all learn something new. Thanks in advance |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect reference causes statistical function to throw up a #NUM!
Hi,
You really should tell us what you are trying to do - your formula serves no purpose since you are assuming that the know X's and know Y's are identical - in which case the forecast is always 1 and you don't need a function at all. Second, if you fix A2 and B2 as absolute you are apparently not going to copy the formula, so in that case there is no need for absolute cell references. Since the forecast is for the value in A2 which is fixed, and since the result will always be 1 you can simply write =A2. You might want a formula like the following where column B is different than column A and you want a rolling forecast, but of course this is only a guess: =FORECAST(A2,OFFSET(A2,1,,4),OFFSET(B2,1,,4)) if you were -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JoCa" wrote: Folks, I ran into an odd behavior when trying to use an indirect reference to a range used as an argument in a statistical function. A: 1: Data 2: 1 3: 2 4: 3 5: 4 6: 5 B2: =ADDRESS(ROW(),COLUMN($A$1)) produces €œ$A$2€ C2: =FORECAST($A$2,OFFSET(INDIRECT($B$2),1,0,4,1),OFFS ET(INDIRECT($B$2),1,0,4,1))produces a value of 1. All good so far. D2: =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(ROW(),COLUM N($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),C OLUMN($A$1))),1,0,4,1)) produces an error #NUM! This is unexpected, since the formula in C2 works fine. Interestingly enough, if you use the same INDIRECT references in cell D2 as arguments for functions like SUM(), PRODUCT(), MIN(), MAX(), etc. it works fine. Try putting the formula below on E2. However, functions like CORREL(), COVAR(), etc. do not work. E2: =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))), 1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1 ))),1,0,4,1)) produces a value of 28 as expected Is this odd or what? Any body knows why using the OFFSET(INDIRECT(ADDRESS())€¦) combination does not work for some statistical functions? Hopefully we can all learn something new. Thanks in advance |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect reference causes statistical function to throw up a #NUM!
Shane, Thanks for your help. I should have known in advance that the
super-simplification of my example was going to cause confusion. Like I said in my reply to Lukes post, the example I submitted was just a something I made up with the minimal properties necessary to demonstrate the problem. Of course, if the know X's and know Y's are identical the slope is 1 and the intercept is 0, so the forecast is always the X value. It was done on purpose to minimize effort needed to understand the problem I am having. Please, take my example (including the absolute references) in context. It was only meant to demonstrate an apparent inconsistency I thought existed. Luke explained how my usage of the INDIRECT function was wrong. Explaining what I am doing, even if I reduce it to a minimum would be tedious for anyone to read. So, the question is then: If I know the row# and column# of an initial reference cell, how can I combine it into a complete reference that would make sense to the OFFSET function as a single-cell range (not a value) for the FORECAST calculation??? "Shane Devenshire" wrote: Hi, You really should tell us what you are trying to do - your formula serves no purpose since you are assuming that the know X's and know Y's are identical - in which case the forecast is always 1 and you don't need a function at all. Second, if you fix A2 and B2 as absolute you are apparently not going to copy the formula, so in that case there is no need for absolute cell references. Since the forecast is for the value in A2 which is fixed, and since the result will always be 1 you can simply write =A2. You might want a formula like the following where column B is different than column A and you want a rolling forecast, but of course this is only a guess: =FORECAST(A2,OFFSET(A2,1,,4),OFFSET(B2,1,,4)) if you were -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JoCa" wrote: Folks, I ran into an odd behavior when trying to use an indirect reference to a range used as an argument in a statistical function. A: 1: Data 2: 1 3: 2 4: 3 5: 4 6: 5 B2: =ADDRESS(ROW(),COLUMN($A$1)) produces €œ$A$2€ C2: =FORECAST($A$2,OFFSET(INDIRECT($B$2),1,0,4,1),OFFS ET(INDIRECT($B$2),1,0,4,1))produces a value of 1. All good so far. D2: =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(ROW(),COLUM N($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),C OLUMN($A$1))),1,0,4,1)) produces an error #NUM! This is unexpected, since the formula in C2 works fine. Interestingly enough, if you use the same INDIRECT references in cell D2 as arguments for functions like SUM(), PRODUCT(), MIN(), MAX(), etc. it works fine. Try putting the formula below on E2. However, functions like CORREL(), COVAR(), etc. do not work. E2: =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))), 1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1 ))),1,0,4,1)) produces a value of 28 as expected Is this odd or what? Any body knows why using the OFFSET(INDIRECT(ADDRESS())€¦) combination does not work for some statistical functions? Hopefully we can all learn something new. Thanks in advance |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect reference causes statistical function to throw up a #
Hmm. I believe I must apologize. In further analysis there does appear to be
something odd going on. In simplifying your formula down a bit: =FORECAST($A$2,OFFSET(INDIRECT("A"&2),1,0,4,1),OFF SET(A2,1,0,4,1)) vs. =FORECAST($A$2,OFFSET(INDIRECT("A"&ROW(A2)),1,0,4, 1),OFFSET(A2,1,0,4,1)) These two formula "should" produce the exact same result. They contain same functions, layout, etc. However, the one with the ROW function returns an error, while the first does not. In further analysis, it appears that the functions within OFFSET are returning arrays, instead of single values. We can compensate by forcing a SUM (of what should be a single value) =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(SUM(ROW()), SUM(COLUMN($A$1)))),1,0,4,1),OFFSET(INDIRECT(ADDRE SS(SUM(ROW()),SUM(COLUMN($A$1)))),1,0,4,1)) So, it appears that you can use your method of consturcting an ADDRESS, you will just need to use the SUM method to force a single-value. A potential problem now may be the nested function limit (currently at 6, limit is 7) but that's a whole other issue. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JoCa" wrote: Luke, Thanks for your help. The reason I am using the ADDRESS/INDIRECT is that I am combining a series of heuristic models to analyze data where new fields and/or records can be added to the dataset in add/insert mode and the models have to find the optimal amount of data to calculate some parameters. The example I submitted was just a something I made up with the minimal properties necessary to demonstrate the problem. I am using the column headers as references to make my formulas able to auto-update when new data is incorporated, and sometimes the initial reference to the target range is an interception and that is why the ADDRESS(ROW(),COLUMN()) is so important to me. So, the question is then: If I know the row# and column# of my initial reference cell, how can I combine it into a reference that would make sense to the OFFSET function as a single-cell range (not a value) for the FORECAST calculation??? "Luke M" wrote: The problem is not with the statistical function, its an improper use of INDIRECT. INDIRECT gathers info to form a cell reference, and then goes to that cell and pulls the value. Thus, when INDIRECT looks at B2, it sees $A$2, and sends this to the OFFSET function. Your latter formula however, INDIRECT sees $A$2, goes there, finds 1, and sends this to OFFSET. This makes no sense to the OFFSET function, causing your error. However, the way you have your formula currently written, it could be simplified to: =FORECAST($A$2,OFFSET($A$1,1,0,4,1),OFFSET($A$1,1, 0,4,1)) Since your ADDRESS function was set to return absolute references. Might I ask why you were going through the effort of using ADDRESS/INDIRECT? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JoCa" wrote: Folks, I ran into an odd behavior when trying to use an indirect reference to a range used as an argument in a statistical function. A: 1: Data 2: 1 3: 2 4: 3 5: 4 6: 5 B2: =ADDRESS(ROW(),COLUMN($A$1)) produces €œ$A$2€ C2: =FORECAST($A$2,OFFSET(INDIRECT($B$2),1,0,4,1),OFFS ET(INDIRECT($B$2),1,0,4,1))produces a value of 1. All good so far. D2: =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(ROW(),COLUM N($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),C OLUMN($A$1))),1,0,4,1)) produces an error #NUM! This is unexpected, since the formula in C2 works fine. Interestingly enough, if you use the same INDIRECT references in cell D2 as arguments for functions like SUM(), PRODUCT(), MIN(), MAX(), etc. it works fine. Try putting the formula below on E2. However, functions like CORREL(), COVAR(), etc. do not work. E2: =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))), 1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1 ))),1,0,4,1)) produces a value of 28 as expected Is this odd or what? Any body knows why using the OFFSET(INDIRECT(ADDRESS())€¦) combination does not work for some statistical functions? Hopefully we can all learn something new. Thanks in advance |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect reference causes statistical function to throw up a #
Further detail:
Because the FORECAST function is looking for an array of numbers, it's causing any function within that arguement to return an array. While this could be helpful, as OFFSET is not setup to deal with arrays, even single-digit ones, it causes the crash. Statistical functions tend to deal with arrays more often than traditional functions, so this is why the problem seems more prevalent in them. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Hmm. I believe I must apologize. In further analysis there does appear to be something odd going on. In simplifying your formula down a bit: =FORECAST($A$2,OFFSET(INDIRECT("A"&2),1,0,4,1),OFF SET(A2,1,0,4,1)) vs. =FORECAST($A$2,OFFSET(INDIRECT("A"&ROW(A2)),1,0,4, 1),OFFSET(A2,1,0,4,1)) These two formula "should" produce the exact same result. They contain same functions, layout, etc. However, the one with the ROW function returns an error, while the first does not. In further analysis, it appears that the functions within OFFSET are returning arrays, instead of single values. We can compensate by forcing a SUM (of what should be a single value) =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(SUM(ROW()), SUM(COLUMN($A$1)))),1,0,4,1),OFFSET(INDIRECT(ADDRE SS(SUM(ROW()),SUM(COLUMN($A$1)))),1,0,4,1)) So, it appears that you can use your method of consturcting an ADDRESS, you will just need to use the SUM method to force a single-value. A potential problem now may be the nested function limit (currently at 6, limit is 7) but that's a whole other issue. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JoCa" wrote: Luke, Thanks for your help. The reason I am using the ADDRESS/INDIRECT is that I am combining a series of heuristic models to analyze data where new fields and/or records can be added to the dataset in add/insert mode and the models have to find the optimal amount of data to calculate some parameters. The example I submitted was just a something I made up with the minimal properties necessary to demonstrate the problem. I am using the column headers as references to make my formulas able to auto-update when new data is incorporated, and sometimes the initial reference to the target range is an interception and that is why the ADDRESS(ROW(),COLUMN()) is so important to me. So, the question is then: If I know the row# and column# of my initial reference cell, how can I combine it into a reference that would make sense to the OFFSET function as a single-cell range (not a value) for the FORECAST calculation??? "Luke M" wrote: The problem is not with the statistical function, its an improper use of INDIRECT. INDIRECT gathers info to form a cell reference, and then goes to that cell and pulls the value. Thus, when INDIRECT looks at B2, it sees $A$2, and sends this to the OFFSET function. Your latter formula however, INDIRECT sees $A$2, goes there, finds 1, and sends this to OFFSET. This makes no sense to the OFFSET function, causing your error. However, the way you have your formula currently written, it could be simplified to: =FORECAST($A$2,OFFSET($A$1,1,0,4,1),OFFSET($A$1,1, 0,4,1)) Since your ADDRESS function was set to return absolute references. Might I ask why you were going through the effort of using ADDRESS/INDIRECT? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JoCa" wrote: Folks, I ran into an odd behavior when trying to use an indirect reference to a range used as an argument in a statistical function. A: 1: Data 2: 1 3: 2 4: 3 5: 4 6: 5 B2: =ADDRESS(ROW(),COLUMN($A$1)) produces €œ$A$2€ C2: =FORECAST($A$2,OFFSET(INDIRECT($B$2),1,0,4,1),OFFS ET(INDIRECT($B$2),1,0,4,1))produces a value of 1. All good so far. D2: =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(ROW(),COLUM N($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),C OLUMN($A$1))),1,0,4,1)) produces an error #NUM! This is unexpected, since the formula in C2 works fine. Interestingly enough, if you use the same INDIRECT references in cell D2 as arguments for functions like SUM(), PRODUCT(), MIN(), MAX(), etc. it works fine. Try putting the formula below on E2. However, functions like CORREL(), COVAR(), etc. do not work. E2: =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))), 1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1 ))),1,0,4,1)) produces a value of 28 as expected Is this odd or what? Any body knows why using the OFFSET(INDIRECT(ADDRESS())€¦) combination does not work for some statistical functions? Hopefully we can all learn something new. Thanks in advance |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect reference causes statistical function to throw up a #NUM!
This is quite subtle behavior due to ROW/COLUMN returning (single element)
arrays. You could try SUM() around each one to make them ordinary values. Normally if you enter OFFSET/INDIRECT in a formula, the reference is changed to a value automatically. But if arrays are included in the arguments, Excel needs the help of another function such as N() or T() to do the conversion. This can be useful in some situations eg for doing operations on more than one sheet. For a deeper understanding you need to delve into how values are represented internally, see http://msdn.microsoft.com/en-us/library/bb687869.aspx (i believe an R type pointer gets passed to a function that accepts xltypeRef arguments.) I think it was MVP Laurent Longre who discovered this. "JoCa" wrote: Folks, I ran into an odd behavior when trying to use an indirect reference to a range used as an argument in a statistical function. A: 1: Data 2: 1 3: 2 4: 3 5: 4 6: 5 B2: =ADDRESS(ROW(),COLUMN($A$1)) produces €œ$A$2€ C2: =FORECAST($A$2,OFFSET(INDIRECT($B$2),1,0,4,1),OFFS ET(INDIRECT($B$2),1,0,4,1))produces a value of 1. All good so far. D2: =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(ROW(),COLUM N($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),C OLUMN($A$1))),1,0,4,1)) produces an error #NUM! This is unexpected, since the formula in C2 works fine. Interestingly enough, if you use the same INDIRECT references in cell D2 as arguments for functions like SUM(), PRODUCT(), MIN(), MAX(), etc. it works fine. Try putting the formula below on E2. However, functions like CORREL(), COVAR(), etc. do not work. E2: =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))), 1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1 ))),1,0,4,1)) produces a value of 28 as expected Is this odd or what? Any body knows why using the OFFSET(INDIRECT(ADDRESS())€¦) combination does not work for some statistical functions? Hopefully we can all learn something new. Thanks in advance |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect reference causes statistical function to throw up a #NUM!
Lori, Thanks for your contribution. Identifying this problem properly was
very helpful. I like your suggestion and I really appreciate it! "Lori" wrote: This is quite subtle behavior due to ROW/COLUMN returning (single element) arrays. You could try SUM() around each one to make them ordinary values. Normally if you enter OFFSET/INDIRECT in a formula, the reference is changed to a value automatically. But if arrays are included in the arguments, Excel needs the help of another function such as N() or T() to do the conversion. This can be useful in some situations eg for doing operations on more than one sheet. For a deeper understanding you need to delve into how values are represented internally, see http://msdn.microsoft.com/en-us/library/bb687869.aspx (i believe an R type pointer gets passed to a function that accepts xltypeRef arguments.) I think it was MVP Laurent Longre who discovered this. "JoCa" wrote: Folks, I ran into an odd behavior when trying to use an indirect reference to a range used as an argument in a statistical function. A: 1: Data 2: 1 3: 2 4: 3 5: 4 6: 5 B2: =ADDRESS(ROW(),COLUMN($A$1)) produces €œ$A$2€ C2: =FORECAST($A$2,OFFSET(INDIRECT($B$2),1,0,4,1),OFFS ET(INDIRECT($B$2),1,0,4,1))produces a value of 1. All good so far. D2: =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(ROW(),COLUM N($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),C OLUMN($A$1))),1,0,4,1)) produces an error #NUM! This is unexpected, since the formula in C2 works fine. Interestingly enough, if you use the same INDIRECT references in cell D2 as arguments for functions like SUM(), PRODUCT(), MIN(), MAX(), etc. it works fine. Try putting the formula below on E2. However, functions like CORREL(), COVAR(), etc. do not work. E2: =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))), 1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1 ))),1,0,4,1)) produces a value of 28 as expected Is this odd or what? Any body knows why using the OFFSET(INDIRECT(ADDRESS())€¦) combination does not work for some statistical functions? Hopefully we can all learn something new. Thanks in advance |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect reference causes statistical function to throw up a #NUM!
Luke, Thank you so much for your help. You are a true pro for digging deeper
into the problem. Thanks for your suggestion. I appreciate it! "Luke M" wrote: Further detail: Because the FORECAST function is looking for an array of numbers, it's causing any function within that arguement to return an array. While this could be helpful, as OFFSET is not setup to deal with arrays, even single-digit ones, it causes the crash. Statistical functions tend to deal with arrays more often than traditional functions, so this is why the problem seems more prevalent in them. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Hmm. I believe I must apologize. In further analysis there does appear to be something odd going on. In simplifying your formula down a bit: =FORECAST($A$2,OFFSET(INDIRECT("A"&2),1,0,4,1),OFF SET(A2,1,0,4,1)) vs. =FORECAST($A$2,OFFSET(INDIRECT("A"&ROW(A2)),1,0,4, 1),OFFSET(A2,1,0,4,1)) These two formula "should" produce the exact same result. They contain same functions, layout, etc. However, the one with the ROW function returns an error, while the first does not. In further analysis, it appears that the functions within OFFSET are returning arrays, instead of single values. We can compensate by forcing a SUM (of what should be a single value) =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(SUM(ROW()), SUM(COLUMN($A$1)))),1,0,4,1),OFFSET(INDIRECT(ADDRE SS(SUM(ROW()),SUM(COLUMN($A$1)))),1,0,4,1)) So, it appears that you can use your method of consturcting an ADDRESS, you will just need to use the SUM method to force a single-value. A potential problem now may be the nested function limit (currently at 6, limit is 7) but that's a whole other issue. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JoCa" wrote: Luke, Thanks for your help. The reason I am using the ADDRESS/INDIRECT is that I am combining a series of heuristic models to analyze data where new fields and/or records can be added to the dataset in add/insert mode and the models have to find the optimal amount of data to calculate some parameters. The example I submitted was just a something I made up with the minimal properties necessary to demonstrate the problem. I am using the column headers as references to make my formulas able to auto-update when new data is incorporated, and sometimes the initial reference to the target range is an interception and that is why the ADDRESS(ROW(),COLUMN()) is so important to me. So, the question is then: If I know the row# and column# of my initial reference cell, how can I combine it into a reference that would make sense to the OFFSET function as a single-cell range (not a value) for the FORECAST calculation??? "Luke M" wrote: The problem is not with the statistical function, its an improper use of INDIRECT. INDIRECT gathers info to form a cell reference, and then goes to that cell and pulls the value. Thus, when INDIRECT looks at B2, it sees $A$2, and sends this to the OFFSET function. Your latter formula however, INDIRECT sees $A$2, goes there, finds 1, and sends this to OFFSET. This makes no sense to the OFFSET function, causing your error. However, the way you have your formula currently written, it could be simplified to: =FORECAST($A$2,OFFSET($A$1,1,0,4,1),OFFSET($A$1,1, 0,4,1)) Since your ADDRESS function was set to return absolute references. Might I ask why you were going through the effort of using ADDRESS/INDIRECT? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "JoCa" wrote: Folks, I ran into an odd behavior when trying to use an indirect reference to a range used as an argument in a statistical function. A: 1: Data 2: 1 3: 2 4: 3 5: 4 6: 5 B2: =ADDRESS(ROW(),COLUMN($A$1)) produces €œ$A$2€ C2: =FORECAST($A$2,OFFSET(INDIRECT($B$2),1,0,4,1),OFFS ET(INDIRECT($B$2),1,0,4,1))produces a value of 1. All good so far. D2: =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(ROW(),COLUM N($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),C OLUMN($A$1))),1,0,4,1)) produces an error #NUM! This is unexpected, since the formula in C2 works fine. Interestingly enough, if you use the same INDIRECT references in cell D2 as arguments for functions like SUM(), PRODUCT(), MIN(), MAX(), etc. it works fine. Try putting the formula below on E2. However, functions like CORREL(), COVAR(), etc. do not work. E2: =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))), 1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1 ))),1,0,4,1)) produces a value of 28 as expected Is this odd or what? Any body knows why using the OFFSET(INDIRECT(ADDRESS())€¦) combination does not work for some statistical functions? Hopefully we can all learn something new. Thanks in advance |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Indirect reference causes statistical function to throw up a #
I now see Luke got there first on the sum fix, glad to be of help anyway.
"JoCa" wrote: Lori, Thanks for your contribution. Identifying this problem properly was very helpful. I like your suggestion and I really appreciate it! "Lori" wrote: This is quite subtle behavior due to ROW/COLUMN returning (single element) arrays. You could try SUM() around each one to make them ordinary values. Normally if you enter OFFSET/INDIRECT in a formula, the reference is changed to a value automatically. But if arrays are included in the arguments, Excel needs the help of another function such as N() or T() to do the conversion. This can be useful in some situations eg for doing operations on more than one sheet. For a deeper understanding you need to delve into how values are represented internally, see http://msdn.microsoft.com/en-us/library/bb687869.aspx (i believe an R type pointer gets passed to a function that accepts xltypeRef arguments.) I think it was MVP Laurent Longre who discovered this. "JoCa" wrote: Folks, I ran into an odd behavior when trying to use an indirect reference to a range used as an argument in a statistical function. A: 1: Data 2: 1 3: 2 4: 3 5: 4 6: 5 B2: =ADDRESS(ROW(),COLUMN($A$1)) produces €œ$A$2€ C2: =FORECAST($A$2,OFFSET(INDIRECT($B$2),1,0,4,1),OFFS ET(INDIRECT($B$2),1,0,4,1))produces a value of 1. All good so far. D2: =FORECAST($A$2,OFFSET(INDIRECT(ADDRESS(ROW(),COLUM N($A$1))),1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),C OLUMN($A$1))),1,0,4,1)) produces an error #NUM! This is unexpected, since the formula in C2 works fine. Interestingly enough, if you use the same INDIRECT references in cell D2 as arguments for functions like SUM(), PRODUCT(), MIN(), MAX(), etc. it works fine. Try putting the formula below on E2. However, functions like CORREL(), COVAR(), etc. do not work. E2: =SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1))), 1,0,4,1),OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN($A$1 ))),1,0,4,1)) produces a value of 28 as expected Is this odd or what? Any body knows why using the OFFSET(INDIRECT(ADDRESS())€¦) combination does not work for some statistical functions? Hopefully we can all learn something new. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDIRECT function to reference values in another worksheet | Excel Worksheet Functions | |||
Cell reference OR INDIRECT function | Excel Worksheet Functions | |||
indirect function to reference cell on different sheet | Excel Worksheet Functions | |||
Use INDIRECT function to reference a value in closed file | Excel Worksheet Functions | |||
How do I throw in an ISERROR function on a complicated VVLOOKUP? | Excel Discussion (Misc queries) |