how to find 80% value using formulas?

[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

 

[Keith R]

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!

 

[Vasant Nanavati]

Hi Keith:

Perhaps:

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

where List is the range containing the values.

Regards,

Vasant.

 

[Ron Rosenfeld]

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

Very nice. I had come up with a complicated array formula.


--ron

 

[Vasant Nanavati]

Thanks, Ron ... I probably had one of those rare moments of clarity ...

Regards,

Vasant.

 

[Keith R]

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!!