Problem regarding custom error bars

[Laura Valleylake]

Hello

I have a problem regarding the custom error bars in excel. I intent to add the highest and lowest values in the form of error bars for each point on my graph. However, every time I click:

add chart element > error bars > more error bars options > custom > specify value >

- then I mark the positive error values and the negative values, but when they show up on the graph, the bars are wrong and don't match the numbers I marked.

I don't know what to do about the problems, so I'm really hoping someone can help me. I have attached the excel document where the working is, as this may help explain what the exact problem is.

Best regards Laura

[MrShorty]

File did not attach (shortcuts don't work on this site. Click Edit post or go advanced to bring up the full featured post editor, then click on manage attachments to bring up the main file uploader). The process you described should have worked, so I think we will need to see exactly what you have in order to comment further.

[Laura Valleylake]

Ahh okay - I attached it now.

[MrShorty]

It looks mostly right to me. One of the things that has long irritated me about the custom error bar values is that Excel expects positive values in the negative error bar fields. Your error bar values are linked to an external workbook rather than the sample workbook. Assuming the values in the linked workbook are the same values in K6:Q6, you should simply need to reenter those values as positive values instead of negative values.

[Laura Valleylake]

Thanks for replying. I'm not entirely sure that I know what you mean, but am I not supposed to enter any negative values in either of the columns? The thing is, the line itself represents an average of 4 replicates, and I wanted to show the highest and lowest replicate of the average as error bars, for each point. And the data that was obtained included both negative and positive values. And when I look at the graphs with error bars, most of them only show the 'high values', and these don't seem to add up with the actual points.

[MrShorty]

Your sample file suggests that the negative error bar field is using K6:Q6 (0,-2.5,-.6,-.9,-.5,-.6,-.7) for the values. Excel expects positive values in these cells, so when it sees -2.5, it draws the error bar extending from the data point up 2.5 units (instead of the expected down 2.5 units).

Unless I am misunderstanding what these numbers mean. If you want the end of the error bar to be at the value of -2.5, you need your error bar field to calculate the difference between the average (the actual length of the error bar, not the end value of the error bar). So, L6 needs to be 1.75 [-0.75-(-2.5)] and L5 needs to be 1.05 [0.3-(-0.75)] in order to get the ends of the error bars in the desired position.

[Laura Valleylake]

Okay thank you, you've been a big help!