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
    3,128
    Thanks
    149
    Thanked 573 Times in 545 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,754
    Thanks
    129
    Thanked 692 Times in 628 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
    3,128
    Thanks
    149
    Thanked 573 Times in 545 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,300
    Thanks
    3
    Thanked 204 Times in 188 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
  •