Hi all,

I have several colums I am using with numbers in them. These columms
scretch for a few hundred rows. Below are two colums going 100 rows.

I only want to work with certain range, so in B1 and B2 cell I indicate the
starting cell and the ending cell. The example belwo shows start at A2 and
finish at A80

I find the average with the following
C1=average(indirect(B1):indirect(b2)


I am having touble with the next formula. I would now like to count the
number of cells that are between a certain % of the average.

Thanks for you time

A B C D E
1 11 A2 result of average # of cells between 0% & 10%
of average
2 22 A80 # of cells between 11%
& 20% of average
3 25 # of cells between
21% & 30%
4 37 # of cells between
31% & 40%
5 55 ...
6 56
7 61
8
9
10
..
..
..100

The formula for getting the count meeting the condition C1 and < C2 in the
range A1:A100 is
=SUMPRODUCT(--(A1:A100C1),--(A1:A100<C2))

if you have A1 in B1, A100 in B2
then all you have to do is replace A1 by INDIRECT(B1) and A100 by INDIRECT(B2)

--
If you find this post helpful pl. choose "Yes"...


"on_lunch" wrote:

Hi all,

I have several colums I am using with numbers in them. These columms
scretch for a few hundred rows. Below are two colums going 100 rows.

I only want to work with certain range, so in B1 and B2 cell I indicate the
starting cell and the ending cell. The example belwo shows start at A2 and
finish at A80

I find the average with the following
C1=average(indirect(B1):indirect(b2)


I am having touble with the next formula. I would now like to count the
number of cells that are between a certain % of the average.

Thanks for you time

A B C D E
1 11 A2 result of average # of cells between 0% & 10%
of average
2 22 A80 # of cells between 11%
& 20% of average
3 25 # of cells between
21% & 30%
4 37 # of cells between
31% & 40%
5 55 ...
6 56
7 61
8
9
10
.
.
.100