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,930
    Thanks
    139
    Thanked 502 Times in 478 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,680
    Thanks
    121
    Thanked 664 Times in 605 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,930
    Thanks
    139
    Thanked 502 Times in 478 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,284
    Thanks
    3
    Thanked 192 Times in 178 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
  •