I have one column of data (A:A) in b1 I want to have an average of the value in the top 80% of all the data in (A:A). I can have a conditional format highlight the top 80% but I want a formula to sum and count all cells in the top 80%.
Total:15
Average:2
Total*.8 = 12
Example
.5
.5
1
5
5
.5
2
.5
So... 5,5,2 are the largest numbers =12 but the average is 4 I can do this by hand for this little data but I have huge rows of data are not in order. I'm thinking there needs to be an array formula to do this but not sure what it is.
Total:15
Average:2
Total*.8 = 12
Example
.5
.5
1
5
5
.5
2
.5
So... 5,5,2 are the largest numbers =12 but the average is 4 I can do this by hand for this little data but I have huge rows of data are not in order. I'm thinking there needs to be an array formula to do this but not sure what it is.