how to find 80% value using formulas?

  • Thread starter Thread starter Keith R
  • Start date Start date
K

Keith R

I am using an array formula to pull a range of values off of Sheet1.

The values represent a continuous measure, in this case, minutes.

I'd like to know what value is "closest" to the 80% mark, meaning, that 80%
of the cases took N minutes or less.

I know I can do this by pulling all values to a new worksheet, sorting
them, counting how many values exist, multiplying by .8, and then finding
the row that corresponds. What I'd _like_ to do is calculate it all in one
cell, without that manual copy/sorting work and without extra VBA.

Does anyone know how I can do this with formulas? Preferable with formulas
that will work under the full range of Office XP down to Office97?

tia,
Keith
 
Just found the percentile function, but that still isn't what I need-
instead of interpolating the 80th percentile value, I need the largest real
value in the list that is still less than whatever that 80th percentile
value is...

so I can say that "80% of all events occurred in x minutes or less".

I have a wide range of values, and if I used the interpolated value I might
say "in 20 minutes or less" when in fact the closest time (under 20
minutes) was 17, which could be a serious difference...

tia!
 
Hi Keith:

Perhaps:

=SMALL(List,COUNT(List)*0.8)

where List is the range containing the values.

Regards,

Vasant.
 
Thanks, Ron ... I probably had one of those rare moments of clarity ... :-)

Regards,

Vasant.
 
Vasant-

Wow! That looks awesome!
like Ron, I was getting buried in a very complex array formula that I was
trying to use to get the same results.

Many thanks!!
 
Back
Top