Finding multiple values of a function
I am trying to find the Average of the lower 10% of a column of data. I
cannot figureo out how to do it without including the same values. Thank you in advance for any help you can provide |
Finding multiple values of a function
I think I might be able to help here.
Let's say your data is in A1:A100 Add a column B to determine what the rank of your entries. =RANK(A1,A$1:A$100,1) Determine the # of entries =COUNT(A$1:A$100) Determine the highest rank for the bottom 10% =ROUND(COUNT(A$1:A$100)*.1,0) Enter this: =AVERAGE(IF(B1:B100<=ROUND(0.1*COUNT(A1:A100),0),A 1:A100)) COMMIT with CTRL SHIFT ENTER "trainerbro" wrote: I am trying to find the Average of the lower 10% of a column of data. I cannot figureo out how to do it without including the same values. Thank you in advance for any help you can provide |
Finding multiple values of a function
How can I designate the rankings if I have a variable amount of cells each
time. Would it be easiest to make the entire column a ranking column and then just write the function to exclude the Errors? Thanks again "Barb Reinhardt" wrote: I think I might be able to help here. Let's say your data is in A1:A100 Add a column B to determine what the rank of your entries. =RANK(A1,A$1:A$100,1) Determine the # of entries =COUNT(A$1:A$100) Determine the highest rank for the bottom 10% =ROUND(COUNT(A$1:A$100)*.1,0) Enter this: =AVERAGE(IF(B1:B100<=ROUND(0.1*COUNT(A1:A100),0),A 1:A100)) COMMIT with CTRL SHIFT ENTER "trainerbro" wrote: I am trying to find the Average of the lower 10% of a column of data. I cannot figureo out how to do it without including the same values. Thank you in advance for any help you can provide |
Finding multiple values of a function
You could create a named dynamic range for column A and B (for example).
http://www.ozgrid.com/Excel/DynamicRanges.htm "trainerbro" wrote: How can I designate the rankings if I have a variable amount of cells each time. Would it be easiest to make the entire column a ranking column and then just write the function to exclude the Errors? Thanks again "Barb Reinhardt" wrote: I think I might be able to help here. Let's say your data is in A1:A100 Add a column B to determine what the rank of your entries. =RANK(A1,A$1:A$100,1) Determine the # of entries =COUNT(A$1:A$100) Determine the highest rank for the bottom 10% =ROUND(COUNT(A$1:A$100)*.1,0) Enter this: =AVERAGE(IF(B1:B100<=ROUND(0.1*COUNT(A1:A100),0),A 1:A100)) COMMIT with CTRL SHIFT ENTER "trainerbro" wrote: I am trying to find the Average of the lower 10% of a column of data. I cannot figureo out how to do it without including the same values. Thank you in advance for any help you can provide |
All times are GMT +1. The time now is 12:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com