Changing Formula Range with Another Variable in Excel

Question

I'm trying to adjust how much of a data my formula looks at depending on another variable.

An example would be having 20 years worth of returns =IRR(A2:A22)

I'd like to add a variable that you change depending on how many years you would want to look at. Lets say we only wanted to look at the first 10 years how would I get my formula to automatically find the data for =IRR(A2:A12) ?

Excel Hero · Accepted Answer · 2015-11-10 20:40:05Z

2

Let's assume that the year "variable" is in cell A1. It could be any cell, but for this example, A1:

=IRR($A$2:INDEX($A:$A,$A$1+2))

Notice that $A$1 is in the formula. You change that to your year variable.

answered Nov 10, 2015 at 20:40

Excel Hero

14.8k4 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Parke690 Over a year ago

Perfect! It totally works, you really are my Excel Hero.

Collectives™ on Stack Overflow

Changing Formula Range with Another Variable in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related