Custom Error Bars

Not sure what you have in mind or what you have tried already.

If you want the standard deviation as the error bars, there is a built in setting for that. In the screenshot below where it says "Use:", choose "standard deviation instead of "custom value".

The screenshot below is showing totally custom error bars. I used made-up numbers, not something calculated.

A few other thoughts:

Do you mean to use standard error vs standard deviation? It makes error bars centered on the data points. I believe the error will be the standard deviation divided by the square root of the number of data points in the series.

It is possible to make a table that draws data from a pivot table but i think the structure of the pivot table needs to stay the same. If rows/columns get added to the pivot table when your data changes or if you change things up as you are using the pivot table, it could mess up the table that references it. I have not used pivot tables enough to know how robust this set up would be. if you post a screenshot of your pivot table (with it selected so we can see the column letters and row numbers), we might be able to help you create this table.

If you want to try making a table for the chart vs going through the pivot table, post screenshots of your table and your pivot table and, if not not obvious, tell us what it is doing s owe can recreate it with formulas (if possible/practical).

Your error bars are off by one row. You are plotting data for Table 1:C which is C2:C11 (rows 2 through 11) but using error bars for rows 3 through 11. Click on the box that has your positive error bars so that the "Table 2::G3:G11" is highlighted then click on the column letter G so it becomes Table 2::G. Same idea for the negative bars. Then the ranges will all align.

Interestingly, after it ran out of error bar data to use, it used the last set again. I would have thought it would use zeros. That would have been more easily detected.

That was why I wasn't sure what you wanted. You chart is showing whether each data point is within one standard deviation of the mean. The center of the error bars is the mean and they go up/down one standard deviation from there. The standard deviation and the mean are calculated for the series set so every point has the same mean and standard deviation and thus the same error bars. But if each data point in your series is made up of a bunch of other numbers so each data point is the mean of its data set and has its own standard deviation, that will take custom error bars.

I have not used pivot tables enough to give much advice on them. I do see that the "custom" option is missing when the data is from a pivot table, though. You might be able to create a regular table that gets its data from the pivot table then make your chart from that table. Or you might have to gather your data into a regular table using formulas versus using the pivot table.

Ah I see, thanks much! It now appears to be correct after following your steps. After posting my reply I thought maybe this was my mistake and tried to fix it by selecting “Table 2::G2:G11” instead of “Table 2::G3:G11”, but it wasn’t right either. Selecting the columns did the trick. I did it for the Table 2:B plot as well..

Hi Badunit,

I am trying to copy what you did there with the custom error bars, but I'm failing; I must be doing something wrong, the error bars appear to be incorrect (attached):