# Excel 2007 : How to auto calculate a percentile

1. ## How to auto calculate a percentile

As the attached data sheet shows, I have some market data for revenues. I'm trying to write a formula that automatically compares my company's revenue to the market data and returns a percentile of the market data. I'm stuck on this but I'm pretty sure excel can do it.

Any suggestions are greatly appreciated.

Thanks,
Steve  Register To Reply

2. ## Re: How to auto calculate a percentile

Hello

I'm not really sure if I can help you on this but are you in fact looking for the Inverse Percentile in cell G4? If so, is this quote the correct description of what you want?

"The inverse percentile of x for a set of measurements is the percentage of all values <=x."

If that's right, would the following formula in cell G4 return the correct value?

=COUNTIF(\$C\$4:\$C\$13,"<="&F4)/10

Hope this helps.  Register To Reply

3. ## Re: How to auto calculate a percentile Originally Posted by DBY Hello

I'm not really sure if I can help you on this but are you in fact looking for the Inverse Percentile in cell G4? If so, is this quote the correct description of what you want?

"The inverse percentile of x for a set of measurements is the percentage of all values <=x."

If that's right, would the following formula in cell G4 return the correct value?

=COUNTIF(\$C\$4:\$C\$13,"<="&F4)/10

Hope this helps.
Thanks for your reply. That doesn't work exactly but it's close. Any other suggestions?  Register To Reply

4. ## Re: How to auto calculate a percentile

Hello
When I do the percentile of your data at 0.5 it returns 2.5 which matches the inverse formula, so I don't know what else to suggest. What is the figure you're expecting returned in cell G4?  Register To Reply

5. ## Re: How to auto calculate a percentile Originally Posted by DBY Hello
When I do the percentile of your data at 0.5 it returns 2.5 which matches the inverse formula, so I don't know what else to suggest. What is the figure you're expecting returned in cell G4?
Yes - it works for 2.5 but when my revenue changes it doesn't change properly.  Register To Reply

6. ## Re: How to auto calculate a percentile

Hello
I see what you mean. At the moment I have no other suggestions. The percentile figure allows for up to a percentage, whereas the inverse formula works to a specific percentage figure equal to or below the revenue amount.  Register To Reply