| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Range for Offset
Dear Gurus,
Trying to change the Reference Cell of the Offset funtion, but unfortunately, can not succeed in doing so. For example, 1 2 A 100 30 B 105 43 C 106 45 D 107 21 E 120 45 Trying to find a way to do : Offset(a1,1,0,0,2) - range(100:105) and then offset(b1,1,0,0,4) - range(105:120) then if possible, correl(offset( .XXX. ,1,0,0,2);offset( .XXX. ,1,0,0,2)) Thanks and Regards 2 all Gurus. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Range for Offset
Try it like this...
=CORREL(A1:INDEX(A1:A5,n),B1:INDEX(B1:B5,n)) Where n = the number of rows you want to include in the calculation. For example, if n = 2 then this is what the formula will evaluate: =CORREL(A1:A2,B1:B2) Note, you might have to change the argument separator from a comma to a semicolon depending on your location. -- Biff Microsoft Excel MVP "VBApprentice :)" wrote in message ... Dear Gurus, Trying to change the Reference Cell of the Offset funtion, but unfortunately, can not succeed in doing so. For example, 1 2 A 100 30 B 105 43 C 106 45 D 107 21 E 120 45 Trying to find a way to do : Offset(a1,1,0,0,2) - range(100:105) and then offset(b1,1,0,0,4) - range(105:120) then if possible, correl(offset( .XXX. ,1,0,0,2);offset( .XXX. ,1,0,0,2)) Thanks and Regards 2 all Gurus. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Range for Offset
Dear MVP T. Valko,
Thank you very much for your comment and solution proposal. I wonder if I could change the "A1" & "B1" so that, different starting points for the series could de performed. Each analysis may start from a new Reference cell and I could not find how to handle this. Once again, thank you so much for your time and expertise. Regards, VBApprentice :) "T. Valko" wrote: Try it like this... =CORREL(A1:INDEX(A1:A5,n),B1:INDEX(B1:B5,n)) Where n = the number of rows you want to include in the calculation. For example, if n = 2 then this is what the formula will evaluate: =CORREL(A1:A2,B1:B2) Note, you might have to change the argument separator from a comma to a semicolon depending on your location. -- Biff Microsoft Excel MVP "VBApprentice :)" wrote in message ... Dear Gurus, Trying to change the Reference Cell of the Offset funtion, but unfortunately, can not succeed in doing so. For example, 1 2 A 100 30 B 105 43 C 106 45 D 107 21 E 120 45 Trying to find a way to do : Offset(a1,1,0,0,2) - range(100:105) and then offset(b1,1,0,0,4) - range(105:120) then if possible, correl(offset( .XXX. ,1,0,0,2);offset( .XXX. ,1,0,0,2)) Thanks and Regards 2 all Gurus. . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Range for Offset
Try
=CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n)) and so on HTH Bob "VBApprentice :)" wrote in message ... Dear MVP T. Valko, Thank you very much for your comment and solution proposal. I wonder if I could change the "A1" & "B1" so that, different starting points for the series could de performed. Each analysis may start from a new Reference cell and I could not find how to handle this. Once again, thank you so much for your time and expertise. Regards, VBApprentice :) "T. Valko" wrote: Try it like this... =CORREL(A1:INDEX(A1:A5,n),B1:INDEX(B1:B5,n)) Where n = the number of rows you want to include in the calculation. For example, if n = 2 then this is what the formula will evaluate: =CORREL(A1:A2,B1:B2) Note, you might have to change the argument separator from a comma to a semicolon depending on your location. -- Biff Microsoft Excel MVP "VBApprentice :)" wrote in message ... Dear Gurus, Trying to change the Reference Cell of the Offset funtion, but unfortunately, can not succeed in doing so. For example, 1 2 A 100 30 B 105 43 C 106 45 D 107 21 E 120 45 Trying to find a way to do : Offset(a1,1,0,0,2) - range(100:105) and then offset(b1,1,0,0,4) - range(105:120) then if possible, correl(offset( .XXX. ,1,0,0,2);offset( .XXX. ,1,0,0,2)) Thanks and Regards 2 all Gurus. . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Range for Offset
Dear Guru Bob Philips,
Thank you so much for your comment and solution proposal. In your proposing notation, " : =CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n))" writing the Reference cells in "B1" and "C1" notation, then the spirit of automation is gone and manual intervention is needed to change this. I wonder, if you had used a Worksheet function without using VBA, to define this "A1" "B1" "C1" Reference points parametrically ? I tried +Address but it didn't work. Thank you so much for your patience and time. Regards, VBApprentice :) "Bob Phillips" wrote: Try =CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n)) and so on HTH Bob "VBApprentice :)" wrote in message ... Dear MVP T. Valko, Thank you very much for your comment and solution proposal. I wonder if I could change the "A1" & "B1" so that, different starting points for the series could de performed. Each analysis may start from a new Reference cell and I could not find how to handle this. Once again, thank you so much for your time and expertise. Regards, VBApprentice :) "T. Valko" wrote: Try it like this... =CORREL(A1:INDEX(A1:A5,n),B1:INDEX(B1:B5,n)) Where n = the number of rows you want to include in the calculation. For example, if n = 2 then this is what the formula will evaluate: =CORREL(A1:A2,B1:B2) Note, you might have to change the argument separator from a comma to a semicolon depending on your location. -- Biff Microsoft Excel MVP "VBApprentice :)" wrote in message ... Dear Gurus, Trying to change the Reference Cell of the Offset funtion, but unfortunately, can not succeed in doing so. For example, 1 2 A 100 30 B 105 43 C 106 45 D 107 21 E 120 45 Trying to find a way to do : Offset(a1,1,0,0,2) - range(100:105) and then offset(b1,1,0,0,4) - range(105:120) then if possible, correl(offset( .XXX. ,1,0,0,2);offset( .XXX. ,1,0,0,2)) Thanks and Regards 2 all Gurus. . . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Range for Offset
You need to explain this bit ...
then the spirit of automation is gone and manual intervention is needed to change this. it makes no sense to me, and I am not seeing your problem. HTH Bob "VBApprentice :)" wrote in message ... Dear Guru Bob Philips, Thank you so much for your comment and solution proposal. In your proposing notation, " : =CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n))" writing the Reference cells in "B1" and "C1" notation, then the spirit of automation is gone and manual intervention is needed to change this. I wonder, if you had used a Worksheet function without using VBA, to define this "A1" "B1" "C1" Reference points parametrically ? I tried +Address but it didn't work. Thank you so much for your patience and time. Regards, VBApprentice :) "Bob Phillips" wrote: Try =CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n)) and so on HTH Bob "VBApprentice :)" wrote in message ... Dear MVP T. Valko, Thank you very much for your comment and solution proposal. I wonder if I could change the "A1" & "B1" so that, different starting points for the series could de performed. Each analysis may start from a new Reference cell and I could not find how to handle this. Once again, thank you so much for your time and expertise. Regards, VBApprentice :) "T. Valko" wrote: Try it like this... =CORREL(A1:INDEX(A1:A5,n),B1:INDEX(B1:B5,n)) Where n = the number of rows you want to include in the calculation. For example, if n = 2 then this is what the formula will evaluate: =CORREL(A1:A2,B1:B2) Note, you might have to change the argument separator from a comma to a semicolon depending on your location. -- Biff Microsoft Excel MVP "VBApprentice :)" wrote in message ... Dear Gurus, Trying to change the Reference Cell of the Offset funtion, but unfortunately, can not succeed in doing so. For example, 1 2 A 100 30 B 105 43 C 106 45 D 107 21 E 120 45 Trying to find a way to do : Offset(a1,1,0,0,2) - range(100:105) and then offset(b1,1,0,0,4) - range(105:120) then if possible, correl(offset( .XXX. ,1,0,0,2);offset( .XXX. ,1,0,0,2)) Thanks and Regards 2 all Gurus. . . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Range for Offset
I think they mean something like this...
Data in the range A1:B10 C1 = 2 = start position D1 = 5 = end position =CORREL(INDIRECT("A"&C1&":A"&D1),INDIRECT("B"&C1&" :B"&D1)) Or, this non-volatile version: =CORREL(INDEX(A1:A10,C1):INDEX(A1:A10,D1),INDEX(B1 :B10,C1):INDEX(B1:B10,D1)) Both of which evaluate to: =CORREL(A2:A5,B2:B5) -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... You need to explain this bit ... then the spirit of automation is gone and manual intervention is needed to change this. it makes no sense to me, and I am not seeing your problem. HTH Bob "VBApprentice :)" wrote in message ... Dear Guru Bob Philips, Thank you so much for your comment and solution proposal. In your proposing notation, " : =CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n))" writing the Reference cells in "B1" and "C1" notation, then the spirit of automation is gone and manual intervention is needed to change this. I wonder, if you had used a Worksheet function without using VBA, to define this "A1" "B1" "C1" Reference points parametrically ? I tried +Address but it didn't work. Thank you so much for your patience and time. Regards, VBApprentice :) "Bob Phillips" wrote: Try =CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n)) and so on HTH Bob "VBApprentice :)" wrote in message ... Dear MVP T. Valko, Thank you very much for your comment and solution proposal. I wonder if I could change the "A1" & "B1" so that, different starting points for the series could de performed. Each analysis may start from a new Reference cell and I could not find how to handle this. Once again, thank you so much for your time and expertise. Regards, VBApprentice :) "T. Valko" wrote: Try it like this... =CORREL(A1:INDEX(A1:A5,n),B1:INDEX(B1:B5,n)) Where n = the number of rows you want to include in the calculation. For example, if n = 2 then this is what the formula will evaluate: =CORREL(A1:A2,B1:B2) Note, you might have to change the argument separator from a comma to a semicolon depending on your location. -- Biff Microsoft Excel MVP "VBApprentice :)" wrote in message ... Dear Gurus, Trying to change the Reference Cell of the Offset funtion, but unfortunately, can not succeed in doing so. For example, 1 2 A 100 30 B 105 43 C 106 45 D 107 21 E 120 45 Trying to find a way to do : Offset(a1,1,0,0,2) - range(100:105) and then offset(b1,1,0,0,4) - range(105:120) then if possible, correl(offset( .XXX. ,1,0,0,2);offset( .XXX. ,1,0,0,2)) Thanks and Regards 2 all Gurus. . . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Range for Offset
Dear MVPs and Gurus,
T. Valko, Bob Philips, Thank you very much for your time and solution proposals. T. Valko's last proposal may solve the trick. Once again, I thank you for your time and expertise, and wish you a jolly good 2010. VBApprentice :) "T. Valko" wrote: I think they mean something like this... Data in the range A1:B10 C1 = 2 = start position D1 = 5 = end position =CORREL(INDIRECT("A"&C1&":A"&D1),INDIRECT("B"&C1&" :B"&D1)) Or, this non-volatile version: =CORREL(INDEX(A1:A10,C1):INDEX(A1:A10,D1),INDEX(B1 :B10,C1):INDEX(B1:B10,D1)) Both of which evaluate to: =CORREL(A2:A5,B2:B5) -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... You need to explain this bit ... then the spirit of automation is gone and manual intervention is needed to change this. it makes no sense to me, and I am not seeing your problem. HTH Bob "VBApprentice :)" wrote in message ... Dear Guru Bob Philips, Thank you so much for your comment and solution proposal. In your proposing notation, " : =CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n))" writing the Reference cells in "B1" and "C1" notation, then the spirit of automation is gone and manual intervention is needed to change this. I wonder, if you had used a Worksheet function without using VBA, to define this "A1" "B1" "C1" Reference points parametrically ? I tried +Address but it didn't work. Thank you so much for your patience and time. Regards, VBApprentice :) "Bob Phillips" wrote: Try =CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n)) and so on HTH Bob "VBApprentice :)" wrote in message ... Dear MVP T. Valko, Thank you very much for your comment and solution proposal. I wonder if I could change the "A1" & "B1" so that, different starting points for the series could de performed. Each analysis may start from a new Reference cell and I could not find how to handle this. Once again, thank you so much for your time and expertise. Regards, VBApprentice :) "T. Valko" wrote: Try it like this... =CORREL(A1:INDEX(A1:A5,n),B1:INDEX(B1:B5,n)) Where n = the number of rows you want to include in the calculation. For example, if n = 2 then this is what the formula will evaluate: =CORREL(A1:A2,B1:B2) Note, you might have to change the argument separator from a comma to a semicolon depending on your location. -- Biff Microsoft Excel MVP "VBApprentice :)" wrote in message ... Dear Gurus, Trying to change the Reference Cell of the Offset funtion, but unfortunately, can not succeed in doing so. For example, 1 2 A 100 30 B 105 43 C 106 45 D 107 21 E 120 45 Trying to find a way to do : Offset(a1,1,0,0,2) - range(100:105) and then offset(b1,1,0,0,4) - range(105:120) then if possible, correl(offset( .XXX. ,1,0,0,2);offset( .XXX. ,1,0,0,2)) Thanks and Regards 2 all Gurus. . . . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Range for Offset
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "VBApprentice :)" wrote in message ... Dear MVPs and Gurus, T. Valko, Bob Philips, Thank you very much for your time and solution proposals. T. Valko's last proposal may solve the trick. Once again, I thank you for your time and expertise, and wish you a jolly good 2010. VBApprentice :) "T. Valko" wrote: I think they mean something like this... Data in the range A1:B10 C1 = 2 = start position D1 = 5 = end position =CORREL(INDIRECT("A"&C1&":A"&D1),INDIRECT("B"&C1&" :B"&D1)) Or, this non-volatile version: =CORREL(INDEX(A1:A10,C1):INDEX(A1:A10,D1),INDEX(B1 :B10,C1):INDEX(B1:B10,D1)) Both of which evaluate to: =CORREL(A2:A5,B2:B5) -- Biff Microsoft Excel MVP "Bob Phillips" wrote in message ... You need to explain this bit ... then the spirit of automation is gone and manual intervention is needed to change this. it makes no sense to me, and I am not seeing your problem. HTH Bob "VBApprentice :)" wrote in message ... Dear Guru Bob Philips, Thank you so much for your comment and solution proposal. In your proposing notation, " : =CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n))" writing the Reference cells in "B1" and "C1" notation, then the spirit of automation is gone and manual intervention is needed to change this. I wonder, if you had used a Worksheet function without using VBA, to define this "A1" "B1" "C1" Reference points parametrically ? I tried +Address but it didn't work. Thank you so much for your patience and time. Regards, VBApprentice :) "Bob Phillips" wrote: Try =CORREL(B1:INDEX(B1:B5,n),C1:INDEX(C1:C5,n)) and so on HTH Bob "VBApprentice :)" wrote in message ... Dear MVP T. Valko, Thank you very much for your comment and solution proposal. I wonder if I could change the "A1" & "B1" so that, different starting points for the series could de performed. Each analysis may start from a new Reference cell and I could not find how to handle this. Once again, thank you so much for your time and expertise. Regards, VBApprentice :) "T. Valko" wrote: Try it like this... =CORREL(A1:INDEX(A1:A5,n),B1:INDEX(B1:B5,n)) Where n = the number of rows you want to include in the calculation. For example, if n = 2 then this is what the formula will evaluate: =CORREL(A1:A2,B1:B2) Note, you might have to change the argument separator from a comma to a semicolon depending on your location. -- Biff Microsoft Excel MVP "VBApprentice :)" wrote in message ... Dear Gurus, Trying to change the Reference Cell of the Offset funtion, but unfortunately, can not succeed in doing so. For example, 1 2 A 100 30 B 105 43 C 106 45 D 107 21 E 120 45 Trying to find a way to do : Offset(a1,1,0,0,2) - range(100:105) and then offset(b1,1,0,0,4) - range(105:120) then if possible, correl(offset( .XXX. ,1,0,0,2);offset( .XXX. ,1,0,0,2)) Thanks and Regards 2 all Gurus. . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Range Using INDEX instead of Offset | Excel Discussion (Misc queries) | |||
| Offset function problem-Dynamic range | Excel Discussion (Misc queries) | |||
| Dynamic named range & Offset | Excel Discussion (Misc queries) | |||
| Offset, Dynamic range, Countif | Excel Discussion (Misc queries) | |||
| dynamic range / offset | Excel Worksheet Functions | |||
Linear Mode
