Calculating percentiles from bin/frequency data

**Bree Witteveen** · 04-30-2010, 06:45 PM

Is it possible to calculate percentiles from binned data?
For example, I want to find the 25t and 75th percentile for each column in the attached data set. The columns are frequencies within each increment.

Many thanks!

**daddylonglegs** · 04-30-2010, 07:04 PM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

**Bree Witteveen** · 04-30-2010, 07:07 PM

My apologies! I did not know about this rule.
I deleted the other post.

**daddylonglegs** · 04-30-2010, 07:10 PM

I was referring to your post at MrExcel. You don't have to delete it but as a courtesy you should link to it

**Bree Witteveen** · 04-30-2010, 07:40 PM

Apologies for cross posting...
http://www.mrexcel.com/forum/showthread.php?t=465388

**shg** · 04-30-2010, 08:19 PM

I think this is correct.

**shg** · 05-01-2010, 12:37 PM

On reflection, I think there should be a ROUND in there. In B2 and copied down and across,

=INDEX($A$7:$A$107, MATCH(TRUE, ROUND(0.5 + $A2 * SUM(B$7:B$107), 0) <= SUBTOTAL(9, OFFSET(B$7, 0, 0, ROW(B$7:B$107) - ROW(B$6) ) ), 0) )