Average values for a given rank
 

Worksheet 1

Column A, rows 13:6000 contain Sector names
Column AR, rows 13:6000 contain percentiles 1-100%
Column L, rows 13:6000 contain corresponding values

In Worksheet 2
What formula can I use to find the average value for all items ranked
between any two percentiles, for a particular sector.

Percentile range inputs are located in worksheet 2, cells A1 and B1.
Sector inputs is located in worksheet 2, cella2

For example, Worksheet 1

Column A Column L Column AR
Pears 2 40%
Pears 3 60%
Pears 5 80%
Pears 1 20%
Pears 7 100%
Apples 3 33%
Apples 4 66%
Apples 9 100%

Worksheet 2
cell a1 = 30%
Cell b1 = 70%
Cell a2 = Pears

Out put in cell a4 = 2.5

formula in cell a4 gives me an average of all Pears values in the percentile
range between 30% and 70%.

"percentile" is not quite the right word, I might as well mean "value in %".

Thanks for any help!

SteveC

Average values for a given rank
 

=AVERAGE(IF((A13:A6000=A2)*(AR13:AR6000=A1)*(AR13 :AR6000<=B1),L1:L6000))

Enter with Ctrl+Shift+Enter

"SteveC" wrote:

Worksheet 1

Column A, rows 13:6000 contain Sector names
Column AR, rows 13:6000 contain percentiles 1-100%
Column L, rows 13:6000 contain corresponding values

In Worksheet 2
What formula can I use to find the average value for all items ranked
between any two percentiles, for a particular sector.

Percentile range inputs are located in worksheet 2, cells A1 and B1.
Sector inputs is located in worksheet 2, cella2

For example, Worksheet 1

Column A Column L Column AR
Pears 2 40%
Pears 3 60%
Pears 5 80%
Pears 1 20%
Pears 7 100%
Apples 3 33%
Apples 4 66%
Apples 9 100%

Worksheet 2
cell a1 = 30%
Cell b1 = 70%
Cell a2 = Pears

Out put in cell a4 = 2.5

formula in cell a4 gives me an average of all Pears values in the percentile
range between 30% and 70%.

"percentile" is not quite the right word, I might as well mean "value in %".

Thanks for any help!

SteveC

Average values for a given rank
 

Thanks, this is great!

"Toppers" wrote:



=AVERAGE(IF((A13:A6000=A2)*(AR13:AR6000=A1)*(AR13 :AR6000<=B1),L1:L6000))

Enter with Ctrl+Shift+Enter

"SteveC" wrote:

Worksheet 1

Column A, rows 13:6000 contain Sector names
Column AR, rows 13:6000 contain percentiles 1-100%
Column L, rows 13:6000 contain corresponding values

In Worksheet 2
What formula can I use to find the average value for all items ranked
between any two percentiles, for a particular sector.

Percentile range inputs are located in worksheet 2, cells A1 and B1.
Sector inputs is located in worksheet 2, cella2

For example, Worksheet 1

Column A Column L Column AR
Pears 2 40%
Pears 3 60%
Pears 5 80%
Pears 1 20%
Pears 7 100%
Apples 3 33%
Apples 4 66%
Apples 9 100%

Worksheet 2
cell a1 = 30%
Cell b1 = 70%
Cell a2 = Pears

Out put in cell a4 = 2.5

formula in cell a4 gives me an average of all Pears values in the percentile
range between 30% and 70%.

"percentile" is not quite the right word, I might as well mean "value in %".

Thanks for any help!

SteveC

Average values for a given rank
 

Toppers, i

s there a way to modify this formula so, if I'm using autofilter, it only
averages those numbers that appear in the filters? I think it's only working
for all of the rows, filtered and nonfiltered, as is. This is a great
formula by the way, thanks.

SteveC

"Toppers" wrote:



=AVERAGE(IF((A13:A6000=A2)*(AR13:AR6000=A1)*(AR13 :AR6000<=B1),L1:L6000))

Enter with Ctrl+Shift+Enter

"SteveC" wrote:

Worksheet 1

Column A, rows 13:6000 contain Sector names
Column AR, rows 13:6000 contain percentiles 1-100%
Column L, rows 13:6000 contain corresponding values

In Worksheet 2
What formula can I use to find the average value for all items ranked
between any two percentiles, for a particular sector.

Percentile range inputs are located in worksheet 2, cells A1 and B1.
Sector inputs is located in worksheet 2, cella2

For example, Worksheet 1

Column A Column L Column AR
Pears 2 40%
Pears 3 60%
Pears 5 80%
Pears 1 20%
Pears 7 100%
Apples 3 33%
Apples 4 66%
Apples 9 100%

Worksheet 2
cell a1 = 30%
Cell b1 = 70%
Cell a2 = Pears

Out put in cell a4 = 2.5

formula in cell a4 gives me an average of all Pears values in the percentile
range between 30% and 70%.

"percentile" is not quite the right word, I might as well mean "value in %".

Thanks for any help!

SteveC