Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANK with 0 values | Excel Worksheet Functions | |||
average values in non-contiguous cells, ignoring 0 values | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
RANK bug: same values get different ranks | Excel Worksheet Functions |