Results 1 to 5 of 5

Thread: Simple Fx question
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Simple Fx question

    If I have the following data set:

    .....A...
    1 2.9
    2 0.7
    3 -0.8
    4 -2.5

    In cell A6, I want to sum all of A1 thru A4 and average it out =SUM(A1:A4)/4

    but...when I add another row of figures at the top of the set and delete a line of figures at the bottom of the set,
    for example, now I have:

    .....A...
    1 -2
    2 2.9
    3 0.7
    4 -0.8


    I now have a Fx in A6 that reads =SUM(A2:A4)/4 because I added a row of figures in row 1 and deleted a row of figures in row 4.

    I want the Fx in A6 to stay the same when I add and delete rows of information so it always reads without me having to manually go back and change A2 to A1 each time.

    Thanks in advance for your help.

  2. #2
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Simple Fx question

    Try this.

    =AVERAGE(INDEX(A:A,1):INDEX(A:A, 4))

  3. #3
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,430
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Simple Fx question

    @brose99.... Assuming you have nothing (unrelated) below the column of numbers to be averaged, use =AVERAGE(A1:A1000), assuming you might have as many as 1000 numbers sometimes. The AVERAGE function ignores empty cells in ranges. Change 1000 to whatever you think is a reasonable upper limit. Avoid using A:A.

  4. #4
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Simple Fx question

    @joeu2004, I also considered a solution like yours but brose99 wrote that the formula was in A6.

  5. #5
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,430
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Simple Fx question

    Quote Originally Posted by thisoldman View Post
    @joeu2004, I also considered a solution like yours but brose99 wrote that the formula was in A6.
    Thanks. I looked for that, but I overlooked it.

    In that case, I would write =AVERAGE(A1:A5), assuming that A5 is an always-empty cell to separate the average from the data. As we insert between A1 and A5, the range A1:A5 is changed automagically.

    Alternatively, =AVERAGE(INDEX(A:A,1):INDEX(A:A,ROW()-1). That allows cells to be inserted above A1 as well as above A6.

    The range INDEX(A:A,1):INDEX(A:A,4) does not ease the maintenance burden. The number 4 might still need to be modified manually.
    Last edited by joeu2004; Jun 16th, 2018 at 11:01 PM. Reason: format

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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