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

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

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.

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

MrShorty,

What do you mean by ",--" ?

Regards
Christian

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.

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.

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.

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...

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?

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.

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??).

Spot on! You are absolutely right.

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

Regards
Christian

You're welcome.