Formula (or a way) to calculate percentages between 2 cells?

Question

Say I have 2 cells in a pivot table:

A1: with value of 1 A2: with value of 2

Now I want to calculate the percent difference between them simultaneously.

I know I can do it one-at-a-time by creating a new value field, then going to Value Field Settings, Show Value As, and choose "% Difference From". However, this will calculate the value of A2, as compared to A1 (or vice versa).

I want a way to calculate BOTH at the same time, showing the % difference of A2 vs. A1 and A1 vs. A2.

Is there a way to do this with pivot tables, or do I have to resort to doing things manually using formulas?

I'm using the latest version of Excel.

Answer 1

I can't help you with pivot tables (I don't understand what you're attempting from your text above), but having a formula to use might be a help

This will put the two percentages in the same cell, fail safe and with one decimal:
=IFERROR(TEXT(A1/A2*100;"#.0");"-") & " / " & IFERROR(TEXT(A2/A1*100;"#.0");"-")&" %"

Seeing the formula might help you understand the problem.

Answer 2

You may find difference of percentage between two cells, using following method:

How it works:

• Formula in Cell D4 is.

=(New Val-Old Val)/Old Val

=(B4-A4)/A4

• To get the reverse of it, Formula in Cell G4.

=(Old Val-New Val)/New Val

=(A4-B4)/B4

N.B. Format Formula cells for Percentage.

In case you want single Formula then try this one.

=(B4-A4)/A4*100&"%"&"  and  "&(A4-B4)/B4*100&"%"

You get: