# 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

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.

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?

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?

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.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1