CODEWITHSUNDEEP
excelpivot-table
boot-scootin
boot-scootin

Reputation: 12515

Pivot Table calculated field values are incorrect

I'm trying to create a calculated field in my pivot table and am having issues getting the calculation right.

Consider the following "raw" pivot table:

enter image description here

I tried to create a calculated field that showed the net availability for an application (general formula: (Availability-Downtime)/Availability). In the Calculated Field menu, I did the following:

enter image description here

where the Formula is =(AVERAGE(Availability)-SUM(Downtime))/AVERAGE(Availability). The result is here:

enter image description here

Clearly the uptime for Application A in 2012 should be (1000-35)/1000 = 96.5%, but it's not. Where am I going wrong, here?

Here is my raw data (Availability is the same for each value of Application... these values were merged from another table via VLOOKUP):

ID  Application Downtime    Year    Availability
1   A   15  2012    1000
2   A   20  2012    1000
3   A   12  2013    1000
4   A   0   2012    1000
5   B   12  2012    1200
6   B   14  2012    1200
7   B   23  2012    1200
8   B   90  2013    1200
9   C   23  2012    1100
10  C   32  2013    1100
11  C   34  2013    1100
12  C   12  2013    1100

Upvotes: 1

Views: 5808

Answers (1)

Sam Gilbert
Sam Gilbert

Reputation: 1702

I believe that it's the aggregation on top that is causing the unexpected result.

For example, where as you are expecting:

(AVERAGE(Availability)-SUM(Downtime))/AVERAGE(Availability)

What effectively is being calculated is:

(SUM(Availability)-SUM(Downtime))/SUM(Availability)

(or average depending how the field in the pivot table is aggregated)

To get the the calculation that you are after you could use a helper column populated with 1's to calculate the average availability, example below:

(Availability/helper-Downtime)/(Availability/helper)

(Then sum this in the pivot table)

Upvotes: 3

Related Questions