custom error bars in pivot chart based on calculated field

Peter1971

New Member
Joined
Mar 10, 2019
Messages
1
Is it possible to create custom error bars in a pivot chart that are based on a calculated field?



I would like to create a bar chart representing PROPORTIONS with error bars that represent the CONFIDENCE INTERVALS. My data contains N items that either can be "failed" (0) or "passed" (1). Assume that X is the amount of items that passed, then the proportion of passed items is p=X / N.



In a pivot table, the Proportion p can be easily obtained in a calculated field, using the "sum of passed items" (X) and the "count of items" (N). The confidence interval of a proportion is also a function of X and N (I will not bother you with the formulas). Therefore, these confidence intervals can be obtained with a calculated field as well.



The pivot table works nicely, even when filters are applied; since the "sum of X" and the "count of N" are updated correctly, the formulas for the confidence intervals will be calculated correctly as well.



How nice it would be to make error bars based on this calculated field that represents the confidence interval...



(I use Excel 2013.)
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

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