finding average within a range

  • Thread starter Thread starter nobody
  • Start date Start date
N

nobody

Is there a way to sum a group of numbers based on whether it falls within a
specific.
For example. Suppose I want to find the average of the numbers between 7/4
and 7/8 from the following data

A B
7/3 4
7/4 8
7/5 7
7/6 9
7/7 8
7/8 8
7/9 3

i want the average of the 8,7,9,8,8 and exclude the 4 and 3
 
Is there a way to sum a group of numbers based on whether it falls within a
specific.
For example. Suppose I want to find the average of the numbers between 7/4
and 7/8 from the following data

A B
7/3 4
7/4 8
7/5 7
7/6 9
7/7 8
7/8 8
7/9 3

i want the average of the 8,7,9,8,8 and exclude the 4 and 3
Click to expand...

=(SUMIF(A1:A7,">="&DATE(2003,7,4),B1:B7)-SUMIF(A1:A7,">"&DATE(2003,7,8),B1:B7))/
(COUNTIF(A1:A7,">="&DATE(2003,7,4))-COUNTIF(A1:A7,">"&DATE(2003,7,8)))

or

*array-enter*

=AVERAGE(IF((A1:A7>=DATE(2003,7,4))*(A1:A7<=DATE(2003,7,8)),B1:B7))

To array-enter, hold down <ctrl><shift> while hitting <enter>. XL will place
braces {...} around the formula.

You can use a cell reference in place of the DATE(...) formulas where the cells
contain the two dates you wish to count between.




--ron
 
Back
Top