Trying to do percentile calculation with Pivot

**Roy3125** · 11-24-2012, 11:32 PM

Hi Excel Gurus, I need your help desperately. Person who can help will definitely get lots of good wishes, and amazon cheapest gift voucher. That is all I can afford.

PROBLEM:

I have in Pivot different rows

Country, Client ID, Tiers (Meaning different clusters), Goals, Meetings

Country Tier Client ID Goals

IN 1 14 06
1 17 11
1 18 09

2 25 12
2 14 13

3 10 05

US
UK
AU

This is how the pivot looks. Now what do I want.

I want to for every country and Tier find the top quartile.

Problem is with Pivot I cant pull a formula as the Range for quartile will end with every country and Tier. How can I do this?

Thanks,
Saurav

**dilipandey** · 11-25-2012, 01:20 AM

Hi Roy,

Welcome to the forum.

You can have a helper column on the left of the pivot which will populate the country / tier using formulas and then you continue with rest of the process... if this is ok.. upload a sample workbook to give it a try. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Roy3125** · 11-25-2012, 01:40 AM

Hi Dilip, Thanks for your reply. The file I uploaded on this doc here.

https://docs.google.com/file/d/0B-6p...1nVmlhNUU/edit

I am trying to match/Index etc.

Basically lets take Country AE and Tier 1 - I need to find 75% Percentile. Then I need to take all numbers in that Percentile and find the avergae.

THis should works for Tier 1,2,3,4 and each country.