Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2016
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change Cell Location Call out Quickly

    I'm trying to make a quick call out of what cell location to use so I can make 2 changes instead of 30.

    Basically I want to say =average(B2:B[number I want to call out with another cell])

    So I can enter, say a 9, in another cell and that 9 populates for the number to follow the 2nd B.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    Welcome to the Lounge as a new poster.

    The formula you need is:
    =AVERAGE(INDIRECT("b2:b"&F1))
    ..where cell [F1] contains the number you want to use for the end row number.
    (change F1 in the formula to the actual cell you want to use)

    zeddy

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Nice zeddy. It may be prudent to add some validation:

    =IFERROR(AVERAGE(INDIRECT("b2:b"&F1)),"Error: Enter a Number")

    Maud

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2016-03-31)

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    An improvement is always worth extra thanks.

    zeddy

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Purely as an alternative, since I like to avoid volatile functions as much as possible:

    =IFERROR(AVERAGE(b2:INDEX(b:B,F1)),"Error: Enter a Number")
    Regards,
    Rory

    Microsoft MVP - Excel

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •