Pivot table: Variable divided by another variable within the SAME year&quarter

**jasonleewkd** · 07-05-2012, 12:12 AM

Hi all, as above. Pivot table allows "Show values as... % of.. % of parent row total.. % of grand total" etc etc.

For my situation however, none of the excel pivot defined functions work. Anyone has any idea insight on whether pivot table has such a function or vba needs to be used?

I am trying to get B8/B7, B11/B10, B15/B14 etc.... within the pivot table itself. I.e. couple of mouse clicks and this will be completed. Save from manually entering a formula to divied 96/68 (=B8/B7), 1/33 (=B11/B10), I have no idea.

None of the pivot table "show values as.." functions work in this case.

The file link is here - https://docs.google.com/open?id=0BzQ...lNFMGprTFZham8

Thank you all you gurus!

**Ace_XL** · 07-05-2012, 01:26 AM

Wouldn't automatically populating these in an adjacent column work?

**jasonleewkd** · 07-05-2012, 01:44 AM

Originally Posted by Ace_XL

Wouldn't automatically populating these in an adjacent column work?

That would be a little too tedious for 4 quarters a year and 10 years, for 26+ districts in the country i am in.

Answer has been found.

1. Select any value field inside pivot
2. Click on from Home ribbon > Insert > Calculated field
3. Write the field name as Launch vs. Sold % (or whatever you fancy)
4. Write formula as ='Uncompleted units Launched in the Quarter'/'Uncompleted Units Sold in the Quarter'
5. click ok
6. Now add this new field to pivot values (remove the other 2 if you dont need them)
7. Done!

Credits: Chandoo.org

Thred solved

**Ace_XL** · 07-05-2012, 01:49 AM

That does work!
Please mark the thread as solved and enjoy the forum.

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save