use address function to indicate range in calculation

I want to use the address function to indicate the range in the calculation of a slope:

cell A2 contains the value 20

=ADDRESS(A2,2) returns $B$20
=ADDRESS(A2,3) returns $C$20

Cell B20 and C20 are the starting cells for calculating the slope:
=slope(B20:B25,C20:C25)

But putting the address function in the slope calculation, like
=slope(ADDRESS(A2,2):B25,ADDRESS(A2,3):C25) does not work!

Why can't I combine these functions?
Is there another (simple) way?


Mayo

Thanx to my boyfriend I found the solution myself

The indirect function should be used, it converts text to a cell or range reference

My example would become:
=slope(indirect("B" & A2 & ":B25"),indirect("C" & A2 & ":C25"))

=slope(Indirect("B"&A2&":B25"),Indirect("C"&A2&":C25"))

--
Regards,
Tom Ogilvy

"Mayo" <Mayo.22tjgm_1139247602.4734@excelforum-nospam.com> wrote in message
news:Mayo.22tjgm_1139247602.4734@excelforum-nospam.com...
>
> I want to use the address function to indicate the range in the
> calculation of a slope:
>
> cell A2 contains the value 20
>
> =ADDRESS(A2,2) returns $B$20
> =ADDRESS(A2,3) returns $C$20
>
> Cell B20 and C20 are the starting cells for calculating the slope:
> =slope(B20:B25,C20:C25)
>
> But putting the address function in the slope calculation, like
> =slope(ADDRESS(A2,2):B25,ADDRESS(A2,3):C25) does not work!
>
> Why can't I combine these functions?
> Is there another (simple) way?
>
>
> Mayo
>
>
> --
> Mayo
> ------------------------------------------------------------------------
> Mayo's Profile:
http://www.excelforum.com/member.php...o&userid=26371
> View this thread: http://www.excelforum.com/showthread...hreadid=508986
>

Try

=SLOPE(INDIRECT("B"&A2&":B25"),INDIRECT("C"&A2&":C25"))

Hi,

The formula given by Mayo and others works very well and is just what I needed to know. The one problem I have with it is, when cutting and pasting the formula the first two letter references in speech marks don't change to the corresponding column. Please could you tell me if there is a way round this?

Cheers

That initial post was from 19 years ago !!

Try it this way:

=SLOPE(INDEX(B:B,A2):B25,INDEX(C:C,A2):C25)

Hope this helps.

Pete

Hi,

Thanks for the rapid reply, I'll give a go.

Cheers

Hi Pete_UK

Your suggestion worked a treat.
Apparently I have hijacked someone else's thread, apologies for the error. I will create my own thread in future

Cheers