Creating a range from two address() functions

[dpuccio]

I'd like to create a range from two separate address functions. I'd then like to use this range in the trend function. I'm doing this so that I can automate the process of breaking up a curve to interpolate points. The address functions are working good, as is the trend function - it all falls apart when I try to combine them like:
trend(address((w14-1),12):address((w14+1),12), another range, value)

What am I doing wrong?

 

[PaddyD]

Hi - welcome to the board.

As I understand it, the address() worksheet function returns a value (i.e. a cell address), not a reference (i.e. a reference to a cell). Take a look at the help on the indirect() function, which you should be able to use to change values into references.

paddy

 

[dpuccio]

Thanks for the fast reply!

I've actually tried the indirect function, and it wants to return the values from the cells, not the actual addresses. I'd be surprised if there wasn't a way to do this, but I haven't found it yet!

 

[Aladin Akyurek]

On 2002-11-04 15:39, dpuccio wrote:
Thanks for the fast reply!

I've actually tried the indirect function, and it wants to return the values from the cells, not the actual addresses. I'd be surprised if there wasn't a way to do this, but I haven't found it yet!

Now I get confused...

The syntax of TREND is:

TREND(known_y's,known_x's,new_x's,const)

that is, it expects numbsers, not actual addresses.

I have the impression that you want to apply TREND to selected portions of your data. If that's the case, you should look for other means like INDEX or OFFSET.

 

[dpuccio]

The arguments to TREND can be ranges (for example $a$1:$a$20) I'm looking for a way to dynamically calculate those ranges and provide them to TREND.

 

[Aladin Akyurek]

On 2002-11-04 16:23, dpuccio wrote:
The arguments to TREND can be ranges (for example $a$1:$a$20) I'm looking for a way to dynamically calculate those ranges and provide them to TREND.

As Paddy explained, if you use ADDRESS function, an address is what you get - undeferenced. It is just text.

$A$1:$A$20 is directly derefencible when fed to a function, while

ADDRESS(1,1)&":"&ADDRESS(20,1)

is not. Hence...

INDIRECT(ADDRESS(1,1)&":"&ADDRESS(20,1))

INDIRECT derefences (fetches the values, does not "sum" them) ADDRESS(1,1)&":"&ADDRESS(20,1) refers to.
This message was edited by Aladin Akyurek on 2002-11-04 16:58

 

[dpuccio]

Thanks for the help, everyone.

Index was the trick. The function looks like:

=TREND((INDEX($L$2:$L$31,(W8-1)))INDEX($L$2:$L$31,(W8+1))),(INDEX($A$2:$A$31,(W8-1)))INDEX($A$2:$A$31,(W8+1))),U8)

It may be sloppy, but it works!

 

[Aladin Akyurek]

On 2002-11-04 17:19, dpuccio wrote:
Thanks for the help, everyone.

Index was the trick. The function looks like:

=TREND((INDEX($L$2:$L$31,(W8-1)))INDEX($L$2:$L$31,(W8+1))),(INDEX($A$2:$A$31,(W8-1)))INDEX($A$2:$A$31,(W8+1))),U8)

It may be sloppy, but it works!

No, it's not sloppy at all. That's exactly what I wanted you to do... it's fast and nice.
This message was edited by Aladin Akyurek on 2002-11-04 17:56

 

[dpuccio]

Thanks!

 

[pingpong777]

Hi. I see this is a very old thread, but my question is somewhat related. I'm trying to use the Address function to dynamically define the range for an XIRR function being calculated using dates and values on another tab. The tab with the dates and values is a monthly data tab, and I want to perform the XIRR function on a quarterly tab (I'll be copying this XIRR across a long quarterly date range).

I'm trying to use:

=XIRR(Return!ADDRESS(86, 9)&" : "&ADDRESS(86, 9 + SUMIF(Return!$J$14:$RU$14, M$268, RETURN!$J$12:$RU$12)), ADDRESS(14, 9)&" : "&ADDRESS(14, 9 + SUMIF(Return!$J$14:$RU$14, M$268, RETURN!$J$12:$RU$12)))


"Return" is the tab with the monthly data on it. I'm trying to define the fixed beginning and the dynamic ending using the ADDRESS function. The SUMIFs return a number in a column counting row. How can I correct my use of the ADDRESS command to make this work?


Thanks,

pingpong777

No, it's not sloppy at all. That's exactly what I wanted you to do... it's fast and nice.


[SIZE=-1][ This Message was edited by: Aladin Akyurek on 2002-11-04 17:56 ][/SIZE]