Prev: formula for visible cells
Next: Latest Purchase Price
From: suprsonic on 4 May 2010 15:01
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.
From: Niek Otten on 4 May 2010 15:11
=(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" <suprsonic(a)discussions.microsoft.com> wrote in message
news:7D867D7F-1A94-4F26-ADA7-1A4F97500AF3(a)microsoft.com...
> 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.

From: Russell on 4 May 2010 16:38
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.
From: Fred Smith on 4 May 2010 16:46
If you have Excel 2007, use the Averageifs function, as in:
=AVERAGEIFS(C7:C11,C7:C11,">5",C7:C11,"<15")

Regards,
Fred

"suprsonic" <suprsonic(a)discussions.microsoft.com> wrote in message
news:7D867D7F-1A94-4F26-ADA7-1A4F97500AF3(a)microsoft.com...
> 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.

From: Ashish Mathur on 5 May 2010 20:49
Hi,

Try this

=sumproduct((A2:A10>5)*(A2:A10<15)*(A2:A10))/sumproduct((A2:A10>5)*(A2:A10<15))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"suprsonic" <suprsonic(a)discussions.microsoft.com> wrote in message
news:7D867D7F-1A94-4F26-ADA7-1A4F97500AF3(a)microsoft.com...
> 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.

 | 
Pages: 1
Prev: formula for visible cells
Next: Latest Purchase Price