I have a simple pivot table displaying data from load testing. I easily constructed a table comprised of my dependent variable & added aggregations for test duration for avg, min, & max. Now I want to construct error bars by adding two columns:
For a given set of runs (basically grouped by the independent variable):
- Subtract the min from the avg duration
- Subtract the avg from the max duration
Two things:
- I tried to simply use conventional formulas to the right of the pivot table, but I can't "fill copy" the column down the side of the pivot table because, well, apparently you can't do that.
- When I tried the calculated field route, all of my efforts fail for various reasons. What I'd like to do is simply subtract the aggregated values from each other.
As it is, I'm copying from the pivot to a new table the dependent variable & avg/min/max aggregates, then manually calculating the min & max deltas for the error chart. When I add raw data, I have to refresh the pivot table, then manually copy-&-past updated values to the table I use for my chart with error bars. I'm sure there's a better way. Here's sample data:
Sample Raw Data
MaxDOP,Time
16,80
16,85
16,75
0,100
0,90
0,120
What I want to end up with in the pivot table:
MaxDOP,avgTime,minTime,maxTime
16,85,75
0,103,90
Then I copy-&-paste this data & manually calculate the minDelta & maxDelta
MaxDOP,avgTime,minTime,maxTime,minDelta,maxDelta
16,85,75,85,5,5
0,103,90,120,13,17
I'd be happy to share the raw workbook to anyone who can help. Thanks!