+ Reply to Thread
Results 1 to 6 of 6

Average that change with quantity of data

  1. #1
    Forum Contributor
    Join Date
    05-18-2007
    Posts
    153

    Average that change with quantity of data

    I have a column that never have the same quantity of data (the colums is sometime longer than other time) how can I adjust the average according to the quantity of cell into my colum, some sort of auto adjust average.
    Thanks

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887
    Can you not simply use

    =AVERAGE(your_range_here)

    ?

    For example, =AVERAGE(B1:B40)

    In this example, if only B1:B21 have values, it will take the average of those 21 values [ sum(b1:b40)/21, not sum(b1:b40)/40 ].

    If you add values into B28, B32 and B40, the average result will update appropriately [ sum(b1:b40)/24 ].

  3. #3
    Forum Contributor
    Join Date
    05-18-2007
    Posts
    153

    Wink

    Oh I found a way.
    I need to know the total of cell that have something in it
    a =IF =1 will do it beside each cell. And the total of those =A
    -Then I need the toal of the content of those cells. = B
    - Finaly I divide B by A
    Got it YÉ!!!!
    Last edited by feejo; 08-01-2007 at 02:04 PM.

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887
    You shouldn't. Just set the =AVERAGE(range) to a range of cells greater than what you'll expect to fill. Obviously, if you happen to add data beyond that range, you would just need to update the range in your formula.

    You could also use

    =AVERAGE(B:B), in which case it would average all values in column B. You wouldn't have to worry about updating the range then.

  5. #5
    Forum Contributor
    Join Date
    05-18-2007
    Posts
    153

    Thumbs down

    Quote Originally Posted by pjoaquin
    You shouldn't. Just set the =AVERAGE(range) to a range of cells greater than what you'll expect to fill. Obviously, if you happen to add data beyond that range, you would just need to update the range in your formula.

    You could also use

    =AVERAGE(B:B), in which case it would average all values in column B. You wouldn't have to worry about updating the range then.
    The problem is when there is no data he write 0 and concider 0 as a data and put it into the average. So the average goes down because of all those 0.

  6. #6
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887
    Zeros are easy enough to work around. In that case, use the array formula:

    =AVERAGE(IF(A1:A40>0,A1:A40))

    After typing this formula, you must press CTRL+SHIFT+ENTER, otherwise it won't return the correct result. Notice I didn't use A:A, either, I set a range (A1:A40). If you think you might ever use 500 rows, simply set your range to something like A1:A1000.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1