Prev: Large list of numbers to concatenate
Next: Challenging Formula Issue
From: carl on 3 Jun 2010 13:28
Is it possible to compute an average given a criteria.

For example, I have some data like this:

30 Day High30 Day Vol
267.22 46.11
79.24 35.29
26.24 68.58
42.39 58.24
53.94 48.04
70.70 34.96
151.13 41.55
32.77 23.85
30.87 27.88

I wanted to compute an average of ColB if ColA was equal to or greater than
75.

In this case, the average would be 40.98

Thank you in advance.

From: Don Guillett on 3 Jun 2010 13:47
This is an ARRAY formula that must be entered using ctrl+shift+enter
=AVERAGE(IF((A2:A10>75),B2:B10))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"carl" <carl(a)discussions.microsoft.com> wrote in message
news:8AEBCA19-08FB-4A3E-B353-AD82D590E50B(a)microsoft.com...
> Is it possible to compute an average given a criteria.
>
> For example, I have some data like this:
>
> 30 Day High30 Day Vol
> 267.22 46.11
> 79.24 35.29
> 26.24 68.58
> 42.39 58.24
> 53.94 48.04
> 70.70 34.96
> 151.13 41.55
> 32.77 23.85
> 30.87 27.88
>
> I wanted to compute an average of ColB if ColA was equal to or greater
> than
> 75.
>
> In this case, the average would be 40.98
>
> Thank you in advance.
>

From: Gary''s Student on 3 Jun 2010 14:10
=AVERAGE(IF(A:A>75,B:B))
This is an array formula that must be entered with:
CNTRL-SHFT-ENTER
rather than just the ENTER key.
--
Gary''s Student - gsnu201003


"carl" wrote:

> Is it possible to compute an average given a criteria.
>
> For example, I have some data like this:
>
> 30 Day High30 Day Vol
> 267.22 46.11
> 79.24 35.29
> 26.24 68.58
> 42.39 58.24
> 53.94 48.04
> 70.70 34.96
> 151.13 41.55
> 32.77 23.85
> 30.87 27.88
>
> I wanted to compute an average of ColB if ColA was equal to or greater than
> 75.
>
> In this case, the average would be 40.98
>
> Thank you in advance.
>
From: Bernard Liengme on 4 Jun 2010 09:30
If you have Excel 2007+, then use non-array formula
=AVERAGEIF(A2:A10,">=75",B2:B10)
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
REMINDER: These newsgroups are about to die. We will all meet again at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel

"carl" <carl(a)discussions.microsoft.com> wrote in message
news:8AEBCA19-08FB-4A3E-B353-AD82D590E50B(a)microsoft.com...
> Is it possible to compute an average given a criteria.
>
> For example, I have some data like this:
>
> 30 Day High30 Day Vol
> 267.22 46.11
> 79.24 35.29
> 26.24 68.58
> 42.39 58.24
> 53.94 48.04
> 70.70 34.96
> 151.13 41.55
> 32.77 23.85
> 30.87 27.88
>
> I wanted to compute an average of ColB if ColA was equal to or greater
> than
> 75.
>
> In this case, the average would be 40.98
>
> Thank you in advance.
>
 | 
Pages: 1
Prev: Large list of numbers to concatenate
Next: Challenging Formula Issue