Calculate Pivot Table Variance

[Ren123]

Does anyone know how I can calculate a variance when my raw data looks like this and my pivot table is structured like that? Based on what I've read, my 'years' have to be in columns. The file I am working with is combining two periods with the year embedded in the line item detail and the file is huge, so that is not realistic to break years into columns. I also tried to calculate outside of the PT.

Raw Data
loc 1 175 2016
loc 2 167 2016
loc 3 528 2016
loc 4 654 2016
loc 1 214 2015
loc 2 425 2015
loc 3 136 2015
loc 4 569 2015

Pivot Table
2015 2016
Loc 1 214 175
Loc 2 425 167
Loc 3 136 528
Loc 4 569 654

Thanks!

 

[sheetspread]

It will work if the years are in rows too

 

[Ren123]

It will work if the years are in rows too

Can you tell me how to do that? Thanks.

 

[sheetspread]

Drag year then loc into rows, number into values:

Excel 2010
	A	B	C	D	E	F	G	H
1	place	number	year
2	loc 1	175	2016
3	loc 2	167	2016
4	loc 3	528	2016
5	loc 4	654	2016				Row Labels	Var of number
6	loc 1	214	2015				2015
7	loc 2	425	2015				loc 1	480.5
8	loc 3	136	2015				loc 2	38642
9	loc 4	569	2015				loc 3	3362
10	loc 1	792	2016				loc 4	51520.5
11	loc 2	760	2016				2016
12	loc 3	758	2016				loc 1	190344.5
13	loc 4	666	2016				loc 2	175824.5
14	loc 1	245	2015				loc 3	26450
15	loc 2	147	2015				loc 4	72
16	loc 3	54	2015
17	loc 4	890	2015
Sheet37

 

[RandyMorris]

So the quick and dirty way:

PivotFields:
Columns: Year
Rows: Raw
Values: Data

Then on the side, =B3-C3 (in cell D3) and drag down. A3 = LOC 1, B3 = 214, C3 = 175.

You can also use a calculated field in the Pivot Table, but I would need to test before I would be comfortable posting.

Cheers.

 

[Ren123]

So the quick and dirty way:

PivotFields:
Columns: Year
Rows: Raw
Values: Data

Then on the side, =B3-C3 (in cell D3) and drag down. A3 = LOC 1, B3 = 214, C3 = 175.

You can also use a calculated field in the Pivot Table, but I would need to test before I would be comfortable posting.

Cheers.

Thank you! That did it! Appreciate the help!

 

[sheetspread]

Is this what you wanted?

(I thought you meant variance as the sum of squared differences from the mean divided by n-1 rather than merely the year over year difference, and that the years should NOT be in columns)