Excel 2007 : 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

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.

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?

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?

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.

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.