Average of a >,

Average of a >, < range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Deb Pingel - 21 Dec 2005 21:54 GMT

Does anyone know how to average a range of numbers based on a criteria
where the numbers have to fall within a range of data? I have trie
combining a number of statements and nothing is working. I want t
average a column of numbers that will have data added to it every day
but throw out anything over 4 and under 14

Reply to this Message

Peo Sjoblom - 21 Dec 2005 22:09 GMT

=SUMPRODUCT((A2:A100<4)+(A2:A100>14),A2:A100)/SUMPRODUCT(((A2:A100<4)+(A2:A1
00>14))*(ISNUMBER(A2:A100)))

or

=AVERAGE(IF(((A2:A100<4)+(A2:A100>14))*(ISNUMBER(A2:A100)),A2:A100))

the latter entered with ctrl + shift & enter

replace A2:A100 with your range (do not use the whole range A:A since the
formula won't work with that, use a range size you pretty much know won't be
larger like maybe A2:A1000 or something)

Signature

Regards,

Peo Sjoblom

> Does anyone know how to average a range of numbers based on a criteria,
> where the numbers have to fall within a range of data? I have tried
> combining a number of statements and nothing is working. I want to
> average a column of numbers that will have data added to it every day,
> but throw out anything over 4 and under 14.

Reply to this Message

Ashish Mathur - 22 Dec 2005 00:06 GMT

Hi,

You may try the following array formula (Ctrl+Shift+Enter)

=average(if((range>A)*(range<B)),range))

Hope this helps.

Regards,

Ashish Mathur

Reply to this Message

Peo Sjoblom - 22 Dec 2005 00:32 GMT

She wanted to exclude values between 4 and 14, that means she wants the
average of values less than or equal to 4 (or less than 4 depending on her
thinking) and values greater than or equal to 14 (or greater than 14), the
multiplication you use won't work since it works as AND and how can an AND
function be both <=4 AND >=14? This would need the equivalent of the OR
function which in an array would be the plus sign

Signature

Regards,

Peo Sjoblom

> Hi,
>
[quoted text clipped - 13 lines]
> > average a column of numbers that will have data added to it every day,
> > but throw out anything over 4 and under 14.

Reply to this Message

Ron Rosenfeld - 22 Dec 2005 00:37 GMT

>Does anyone know how to average a range of numbers based on a criteria,
>where the numbers have to fall within a range of data? I have tried
>combining a number of statements and nothing is working. I want to
>average a column of numbers that will have data added to it every day,
>but throw out anything over 4 and under 14.

If I understand what you write, you want to AVERAGE everything that is less
than or equal to 4; as well as everything that is greater than or equal to 14.

So try:

=(SUMIF(RNG,"<=4") + SUMIF(RNG,">=14")) /
(COUNTIF(RNG,"<=4")+COUNTIF(RNG,">=14"))

--ron

Reply to this Message