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,418
    Thanks
    165
    Thanked 643 Times in 611 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,982
    Thanks
    157
    Thanked 774 Times in 706 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,418
    Thanks
    165
    Thanked 643 Times in 611 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,349
    Thanks
    4
    Thanked 228 Times in 210 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
  •