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.)
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.)
