Excel Calculate field within pivot

jamescora

New Member
Joined
Jan 23, 2018
Messages
1
Sir/Madam,
I have been racking my brains to try solve this issue.
Basically I have a table of data which I need to perform a calculation on. It is a summary calculation for the day based on 4 or 5 different entries that day.

This is an excerpt from my table.

Date Available Time Max Design Speed Total OEE pieces
22/01/2018 130 38 2340
22/01/2018 320 38 4050
22/01/2018 250 38 4150
22/01/2018 230 38 3740


For a particular machine (named Carrig) 4 orders were processed. I need to determine the overall performance of that machine for the full day using a calculation called "Overall Equipment Effectiveness - OEE". This calculation simply divides the total pieces manufactured by the "available time*max design speed". Therefore for the example shown this should be [14280/(38*930)] = 40.4%.

In the calculated field, the formula sums the #pieces manufactured and the available time, which is correct. However, I want it to average the max design speed but it seems to sum them also, giving a result which is a quarter of the correct answer (because there were 4 separate entries).
I have tried to use the following formula within the pivot as a calculate field but to no avail
='Total OEE Pieces' /('Available Time' *AVERAGE('Max Design Speed (PPM)' ))

Any assistance would be greatly appreciated as I am running out of hair to pull!!!

Regards,
jb
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Forum statistics

Threads
1,223,604
Messages
6,173,320
Members
452,510
Latest member
RCan29

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top