Calculating Ratio on Pivot Columns

[grifter]

Hi

In my data I am trying to calculate a ratio between an old and new value listed in my pivot table. My Data is like this;

old          new
==           ===
1245         7989
2371         6314
5889         3434

But originally my count of data in the pivot was the two values per row added up, this column old/new is calculated in the raw data and when I pull it into the pivot it splits the data into old new values, which is what I want. When I try and use GCD it asks me to put a field in but I would then need to use the same field (old/new) twice and i doubt it would work to allow me to calculate the ratio between them.

Ratio I am using is similar to this but hopefully in a calculated field:

=A1/GCD(A1,B1)&":"&B1/GCD(A1,B1)

It look like I could do this right now but I want to add a further column level, which will further split the old/new into 4 columns of data. Then I wonder how I can achieve both the ratio between the two sets of two column data and keep the ratio calculator as a calculated field?

I hope this makes sense and you can maybe provide me with a second opinion....

Thanks

G

 

[Christian1977]

Hi Grifter,

have you tried the command "calculated Items" in the pivottable tools/ Option, button Fields, Items & Sets?

 

[grifter]

Hi Grifter,

have you tried the command "calculated Items" in the pivottable tools/ Option, button Fields, Items & Sets?

Hi no hadn't tried that - is it specifically for calculations such as this? I eventually found out that there was a template formula for calculating the ratio in the company and got a hold of it. I am not in the office and so can't quite remember it but will try and post it up when I get back in.

G

 

[Christian1977]

OK,

I'll wait! Thanks, Christian