+ Reply to Thread
Results 1 to 14 of 14

Average of open-ended column, within given intervals

  1. #1
    Registered User
    Join Date
    02-15-2006
    Posts
    15

    Average of open-ended column, within given intervals

    Hello

    I wonder if anybody can help me out on this one.

    I need to average a column, without a fixed number of rows, and within an interval (and excluding zeros).

    Example: Average of all numbers in the bracket 28-35, in column B2-B??

    By this I mean that the total of rows in column B will vary from time to time, so in order to not having to manually change the number of rows each time, I need an "open-ended" formula, that also incorporates say 4 intervals (for instance 1-27, 28-35, 36-70, 71-100).

    I would be grateful for any feedback!

    Regards,
    Christian

  2. #2
    Valued Forum Contributor
    Join Date
    03-25-2004
    Location
    Boston, MA US
    Posts
    1,094
    Christian,

    You're not very clear as to what determines your intervals but this might get you started.


    =AVERAGE(IF(B1:B60000>=28,IF(B1:B60000<=35,IF(B1:B60000<>0,B1:B60000))))

    This is an array formula so commit with Ctrl+Shift+Enter not just Enter. Your formula will have {} around it if you have entered correctly. By making the range to 60000 rows that should solve your issue of new data being entered.



    HTH
    Steve

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,252
    If I had to do this in a single cell, I'd probably use sumproduct:
    =sumproduct(b2:b222,--(b2:b222>=28),--(b2:b222<=32))/sumproduct(--(b2:b222>=28),--(b2:b222<=32))
    set the "222" to some number greater than the possible number of rows you ever expect to use.

  4. #4
    Registered User
    Join Date
    02-15-2006
    Posts
    15
    Thanks guys

    SteveG, your function returns a slightly different result than a usual Average function, if I check with the exact same figures. Any possible explanation to that?

    Otherwise it seems to work fine.

    Christian

  5. #5
    Registered User
    Join Date
    02-15-2006
    Posts
    15
    MrShorty,

    What do you mean by ",--" ?

    Regards
    Christian

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,252
    I think that's called the unary operator. Basically it takes the "TRUE" and "FALSE"s generated by the boolean expression and converts them to 1's and 0's.

  7. #7
    Registered User
    Join Date
    02-15-2006
    Posts
    15
    Ehh, okay :-) And thanks!

    But same question to you, this function returns a slightly different solution than using standard average function to the interval, ie. the average of the range from 28 to 32.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,252
    What solution did it give you and what solution did you expect?

    I'm understanding "standard average function" to mean [sum[i=1 to n](xi)]/n. The formula I gave simply adds up all the values within the specified interval (end points included), then divides by how many entries that were within the interval.

  9. #9
    Registered User
    Join Date
    02-15-2006
    Posts
    15
    To take an example:

    I use this function: =AVERAGE(P50:P100),

    where P50:P100 corresponds to an interval between 36 and 59, in a sorted, ascending range. In other words, I find and average the exact range from 36 to 59.

    The result is 42.1601

    Then I take your function:

    =SUMPRODUCT(P2:P60000;--(P2:P60000>=36);--(P2:P60000<=59))/SUMPRODUCT(--(P2:P60000>=36);--(P2:P60000<=59))

    This, in theory, should yield the same result (I guess), but the result with your function is 43.6728


    However, if I do the same calculation beginning with the first number in the range (P2) to 35 (P2:P49), ie. from the beginning of the range to the number before the one in the functions above, I get the same result with all three functions...

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,252
    Without seeing exaclty how you're applying it, I can't comment on the difference.

    I went and created a list of 10 random integers between 1 and 10
    8
    3
    9
    9
    8
    7
    1
    6
    5
    9
    6
    using the sumproduct function, I get an average of 5.4 for all the values 3<=x<=7. Using the average function on the sorted list (so I'm averaging 3,5,6,6,7), I also get 5.4.
    I'm not sure why you don't get the same answer. Are you dealing with integers or reals? Are you intending to include the endpoints of the interval in the average (lo<=x<=hi) or not (low<x<hi)? Could you possibly post a sample of your data with the result you get from the average function and the sumproduct function so we can see exactly how you're applying this?

  11. #11
    Registered User
    Join Date
    02-15-2006
    Posts
    15
    Ok, here's the set of data that works:

    (P2:P49)
    2
    4
    6
    11
    12
    12
    12
    12
    12
    12
    15
    15
    15
    20
    20
    20
    21
    22
    23
    23
    24
    24
    24
    24
    24
    24
    24
    24
    25
    26
    27
    27
    27
    27
    27
    27
    28
    28
    30
    32
    32
    32
    34
    35
    35
    35
    35
    35

    Both functions return the same answer:

    =SUMPRODUCT(P1:P60000;--(P1:P60000>=1);--(P1:P60000<=35))/SUMPRODUCT(--(P1:P60000>=1);--(P1:P60000<=35))

    returns: 22.5674

    and

    =AVERAGE(P2:P49)

    returns: 22.5674

    Now here's the set of data that does not return the same results:

    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    36
    39
    39
    41
    43
    46
    48
    48
    48
    48
    48
    51
    54
    56
    56
    57
    57
    57
    58
    58
    59
    59


    Now, the functions return different results:

    =SUMPRODUCT(P2:P60000;--(P2:P60000>=36);--(P2:P60000<=59))/SUMPRODUCT(--(P2:P60000>=36);--(P2:P60000<=59))

    returns: 43.6728

    and

    =AVERAGE(P50:P100)

    returns: 42.1601


    In other words, I do include the endpoints in the interval, averaging in this case the sorted range from the number 36 throughout to the final instance of 59.

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,252
    There must still be more to this, because I can't replicate any of your calculations.

    I copied your data from here into Excel. For the first set (1-35), I get (from both functions) 22.164. For the second set (36-59), I get (again both functions give the same result) 42.157.

    If I take your averages as calculated by the AVERAGE function and multiply by the number of observations, I get 2150.165 from the first set, and 1083.235 from the second set. I expect the difference is because these values you posted are displayed to the nearest whole number, but the underlying values have an unspecified number of digits past the decimal point. With this possibility, one of those 59's, for example, might actually be 59.2. This does indeed round to 59, and, when you manually select the range for the average function, you include it. When sumproduct (or even SteveG's AVERAGE(IF...)) makes the comparison, it sees 59.2 is NOT <=59 and excludes that value from the average. If this is the case, then you'll need to more carefully define the range (35.5<=x<=59.4999999 maybe??).

  13. #13
    Registered User
    Join Date
    02-15-2006
    Posts
    15
    Spot on! You are absolutely right.

    Thank you so much for taking your time helping me out!

    Regards
    Christian

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,252
    You're welcome.

+ 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