Dynamic range in excel

Andrea Vironda 816 Reputation points
2020-10-22T07:37:21.587+00:00

Hi,
I wish to change quickly the border range of my function
=GROWTH(C14:C20,A14:A20,F2)

As input I'd pass A14 in cell E1, and then into growth function I'll get a range starting from A14 and covering 6 cells on the same column. The same about A14:20

Is it possible to do this?

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,645 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emi Zhang-MSFT 22,011 Reputation points Microsoft Vendor
    2020-10-23T08:18:49.363+00:00

    Hi @Andrea Vironda ,
    Do you mean you want to fix the range of A14:A20 and C14:C20? And the dynamic parameter is F2?

    If yes try this function in E1:

    =GROWTH($C$14:$C$20,$A$14:$A$20,F2)

    If my understanding is incorrect, please provide a sample about this problem and I'm glad to help you.

    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
  2. Andrea Vironda 816 Reputation points
    2020-10-23T10:13:30.433+00:00

    34547-image.png
    Here it is!

    1 person found this answer helpful.
    0 comments
  3. Alex 6 Reputation points
    2020-10-31T13:57:09.093+00:00

    Hi Andrea,

    it seems to me that you need to use the INDIRECT function. If you put the range as a string in G1, and you want to pass it as the first parameter in the function GROWTH, your formula will be: "=GROWTH(INDIRECT(G1),A14:A20,F2)".
    Of course you can use every kind of string as INDIRECT parameter. The formula "=GROWTH(INDIRECT("C14:C20"),A14:A20,F2)" should work as well.

    I hope this is the answer you needed.

    Kind regards,
    Alex

    1 person found this answer helpful.
    0 comments