CODEWITHSUNDEEP
excelpivot-tablevba
natedogg
natedogg

Reputation: 95

Excel VBA: How to use pivot table calculated field utilizing "Count of..." field

The goal of my pivot table is to calculate MTBF (Mean Time Between Failures). It is a very simple calculation used in manufacturing to asses machine performance:

MTBF = machine_uptime / number_of_stops

Ex: If a machine runs for 840 minutes with 10 stops or breakdowns, the MTBF is 84 minutes.

This is my ideal pivot table layout:

| Date  |Sum of Uptime|Count of Stops|    MTBF    |
|-------|-------------|--------------|------------|
| 08/01 |   1101.4    |      6       |    184     |
| 08/02 |   1068.0    |      7       |    153     |

This is how I generate the pivot table programmatically. However, my question still stands even I was creating this table manually via the GUI.

Dim pTable as PivotTable
Set pTable_MTBF = Utilities.CreatePivotTable(ws, "pTable_MTBF", "data", ws.Cells(1, 1))
pTable_MTBF.PivotFields("Date").orientation = xlRowField
pTable_MTBF.AddDataField pTable_MTBF.PivotFields("Uptime"), "Sum of Uptime", xlSum
pTable_MTBF.AddDataField pTable_MTBF.PivotFields("Stops"), "Count of Stops", xlCount
' pTable_MTBF.CalculatedFields.Add "MTBF", "=Uptime/'Count of Faults' (something like this?)
' pTable_MTBF.AddDataField pTable_MTBF.PivotFields("MTBF"), "MTBF "

The 'uptime' field is a sum of uptime entered with each stop, and the 'stops' field is a count of the stops entered each day. My question is, how can I set up the MTBF calculated field to successfully calculate these value? What formula should I enter in order to achieve this?

Thanks.

Upvotes: 1

Views: 1870

Answers (1)

Hambone
Hambone

Reputation: 16377

When you do a calculate field, I don't know of any way to do anything other than a sum. The good news is that in your example this is easily overcome by adding another column to your source data -- call it "StopCount" and fill it with all 1's. If your data is in a table, you can use the formula =1 and it will automatically size as your data sizes.

Once that's done a standard calculated field should work:

pTable_MTBF.PivotFields("Date").Orientation = xlRowField
pTable_MTBF.AddDataField pTable_MTBF.PivotFields("Uptime"), "Sum of Uptime", xlSum
pTable_MTBF.AddDataField pTable_MTBF.PivotFields("Stops"), "Count of Stops", xlCount
pTable_MTBF.CalculatedFields.Add "MTBF", "=Uptime/StopCount", True
pTable_MTBF.PivotFields("MTBF").Orientation = xlDataField

Upvotes: 1

Related Questions