For the sake of simplifying what I'm doing, we'll just say that in one of my cells, I'm calculating the slope of a range. For reference, let's say that in cell C1 I have =SLOPE(A:A,B:B). Fairly simple - however, what I really want to do is have my slope only look at values up to a certain row, starting at row 2. For example, =SLOPE(A2:Ax,B2:Bx) where. x is a variable based on the value of a cell that I have already calculated.
Let's say x is located in cell G20, and the value of G20 is 59. If this is the case, I want my slope function to be =SLOPE(A2:A59,B2:B59). However, based on the formula I have in G20, its value will change based on user input. So G20 can change to 61, 62, or 63, etc., and when that happens, I want that to be reflected in my SLOPE formula. If G20 changes to 60, then C1 should become =SLOPE(A2:A60,B2:B60). If G20 becomes 61, then C1 should become =SLOPE(A2:61,B2:B61). And so on and so on....
I'm simplifying what I'm really doing for the sake of example, so try not to think about an easier way to do this. I just need to combine a constant column value with a variable row value, and stick that into a formula.
How can I go about doing this?
Please try:
=SLOPE(INDIRECT("A2:A"&G20),INDIRECT("B2:B"&G20))