averaging a range
 

How do I average a range of numbers between two criteria? For instance, I'd
want to average only numbers greater than 5 and less than 15.

averaging a range
 

=(SUMIF(A1:A5,"5")-SUMIF(A1:A5,"=15"))/(COUNTIF(A1:A5,"5")-COUNTIF(A1:A5,"=15"))

In Excel 2007 and later you can use the AVERAGEIFS function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"suprsonic" wrote in message
...
How do I average a range of numbers between two criteria? For instance,
I'd
want to average only numbers greater than 5 and less than 15.

averaging a range
 

If you are using Excel 2007 you can use the SUMIFS function. It isn't in 2003.

"suprsonic" wrote:

How do I average a range of numbers between two criteria? For instance, I'd
want to average only numbers greater than 5 and less than 15.

averaging a range
 

If you have Excel 2007, use the Averageifs function, as in:
=AVERAGEIFS(C7:C11,C7:C11,"5",C7:C11,"<15")

Regards,
Fred

"suprsonic" wrote in message
...
How do I average a range of numbers between two criteria? For instance,
I'd
want to average only numbers greater than 5 and less than 15.

averaging a range
 

Hi,

Try this

=sumproduct((A2:A105)*(A2:A10<15)*(A2:A10))/sumproduct((A2:A105)*(A2:A10<15))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"suprsonic" wrote in message
...
How do I average a range of numbers between two criteria? For instance,
I'd
want to average only numbers greater than 5 and less than 15.

Guys,
yes it is possible to do it by combination any of the formulae Sumif/countif, averageif, sumproduct.

any idea on the performance of the formulae, which runs in shorter period. I know it doest time long time btw any of the function to excute. But imagine if you have lacs of cells in mutiple spreadsheet, even single milli second is a matter in performance.