I have an odd issue with custom error bars in MS Excel 2016 (from MS Office 365). The upper error bars are showing but not the lower ones. At least not all the time… only once in a while…
See the red curve on the below image:
The y-scale is logarithmic. And the error bars are set to the same value for upper an lower bar. So I would expect the lower bar to be displayed longer than the upper bar, which is the case for the blue curve.
Error bars are set to "Custom" error amount and to "both" directions. And for the "Custom" error amounts I have chosen the same cell range for both upper and lower error bars, as shown below (only showing the first 14 values):
The Avg row shows the plotted data points (calculated with =AVERAGE(J50:J60) over a cell range) and the STDev row shows the error bar values that are used in both direction as both upper and lower error bars (calculated with =STDEV.S(J50:J60) over the same cell range).
Of some reason the 6th and 22nd (and possible more if they are too small to be seen) are the only values with a lower error bar. I do not see anything unusual in the values that are used and I have not been able to reproduce this error on other curves (although I do not know for sure if the other colored curves show the error as well; it could be too small to be visible).
The use of a logarithmic y-scale could be an issue, according to a MS forum question, since Excel might refuse to display negative values on a log-scale (or values which it interprets as negative of whatever reason) but then why do two of the lower error bars work? And why does it work fine for other curves than the red one?
Best Answer
I don't have a solution, but suggestions for debugging.. What if you just draw the line for the errors so you can see where the bars should land? I would try delete and make a new curve, also maybe multiply the values by a factor to see if there is a change.
ps: the line is orange (to me) ;)