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 :( -- Mayo ------------------------------------------------------------------------ Mayo's Profile: http://www.excelforum.com/member.php...o&userid=26371 View this thread: http://www.excelforum.com/showthread...hreadid=508986 |
use address function to indicate range in calculation
=slope(Indirect("B"&A2&":B25"),Indirect("C"&A2&":C 25"))
-- Regards, Tom Ogilvy "Mayo" wrote in message ... 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 |
use address function to indicate range in calculation
Thanx to my boyfriend I found the solution myself :rolleyes: 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")) -- Mayo ------------------------------------------------------------------------ Mayo's Profile: http://www.excelforum.com/member.php...o&userid=26371 View this thread: http://www.excelforum.com/showthread...hreadid=508986 |
use address function to indicate range in calculation
Try
=SLOPE(INDIRECT("B"&A2&":B25"),INDIRECT("C"&A2&":C 25")) |
All times are GMT +1. The time now is 09:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com