Positive error value and negative error value là gì

I have several data sets, each of which includes several tests. Each test contains several temperature samples at different locations. For each data set, I have an average of the different tests for each location and some measurement of error (standard deviation, confidence interval, etc.).

I want to produce a bar graph where bars are grouped by sample location, so there will be as many bars per group as there are data sets, and as many groups as there are sample locations. The number of tests in each data set doesn't matter, as I am not using test data, I am using the averages of test data in each data set. This is, so far, fairly straightforward.

However, I also want to graph the error measurement as error bars (whiskers extending up and down from each vertical bar, or whiskers extending right and left from each horizontal bar, depending on whether I choose a column or bar graph type in Excel).

These error bars should not be calculated by Excel from the data series it is given. Instead, they should be taken from the error measurement series I have prepared along with the average from the test data in each data set. I'm not sure how to accomplish this.

To create a column chart of sample means having error bars based a statistical analysis, begin by following the instructions in Section 5.4.4 . To graph several sample means, place the data for each sample group in adjacent columns of the spreadsheet and select the block of columns during the Input Range selection step. When the descriptive statistics are generated, the labels for the statistical values will be repeated unnecessarily, so you can delete the labels that separate the columns of results.

Highlight the mean values in the row labeled "Mean". On the Insert ribbon, click on the Insert Column Chart. Select the Clustered Column option. Excel should create a column chart with a column for each sample mean. To label the columns, click on the funnel-shaped icon to the right of the chart. Click on the "Select data…" link at the bottom of the dialog box. Click on the Edit button under Horizontal (Category) Axis Labels. In the Axis Labels selection dialog box, click on the select button and select the column heading cells. Click OK, then OK again.

To further customize the chart, click on the + sign to the upper right of the chart. Deselect Chart Title. Select Axis Title and edit the labels appropriately. Check the Error Bars checkbox, then click on the triangle to the right of the option. Select "More Options…" Under "Error Amount", click the Custom radio button, then click on Specify Value. The Custom Error Bars dialog box will appear. The positive and negative error values are the amounts to be added to and subtracted from the means to specify the upper and lower limits. They are not the actual values of the limits themselves. Since these amounts are included under Confidence Level(95.0%) in the generated Summary Statistics table, you can simply select the cells in that row for both the Positive Error Value and Negative Error Value, then click OK to dismiss the dialog.

If you want the error bars to represent standard error of the mean or standard deviation, you can select cells in those rows rather than the 95% confidence levels.

Because the chart was created in a Microsoft Office product, you should be able to copy and paste it easily to either a Word or PowerPoint document. The default paste option is "Use Destination Theme & Link Data" which means that if you change the spreadsheet, the chart may change in your Word document. To prevent this, you can change the paste option to Picture. The chart will then be stable in your Word document, although it will also become uneditable.

Thanks the API office.js, I'm displaying a bar chart. Now I want to add custom error bar on each data bar.

The following image shows the result obtained using Excel vba:

But in the spec of the API office.js, I don't find any information about how to set the positive and negative values for an custom error bars.

Error bars on a chart/graph provide a visual representation of our uncertainty of knowledge about the values of the data points. These are helpful in understanding the accuracy of our data and have the benefit of being relatively easy to interpret. We are going to discuss how to add error bars to charts in Excel and some of the options related to the error bars. Note that we can have error bars for the y-values on our chart – this may be what we see most often, but we can also have errors bars for the x-values. We won’t discuss where these error bars came from or their meaning, just how to display them in Excel.

Excel has a variety of chart types and it is possible to display values that aren’t numbers along an axis, for example dates or names. Error bars only make sense for numeric values and we will be using scatter plots where both the x and y values are numbers. Error bars are possible on other chart types, but only for the axis with numeric data values.

Typically we will have error bars of one of the following types:

• constant error value for all data points – for example, the error for all y values is \Delta Y \= 0.4. Note that, for our purposes, the Greek letter \Delta denotes “the error in”, so \Delta Y is read as “the error in Y”.
• constant fractional error value for all data points – for example the fractional error is \frac{\Delta Y}{Y} \= 0.05. This means that data points with small Y values will have a small error bar, while data points with large Y values will have large error bars. In this example, the error in any data point is 5% of the value of the data point.
• specified error values for each data point
  • note that Excel allows us to specify separate lengths for the top and bottom halfs of the error bars.

Constant Error Bars

Lets move on to examples of how to get error bars into Excel. First, the easiest to understand is the constant error value. We can generate a chart using the following values:

Fig. 1: Y vs X with constant error values for both X and Y data

The table of X and Y values is on the left side of the figure and the resulting Excel chart is shown with the data. Every X data value has an associated error of \Delta X \= 0.3, and every Y data value has an associated error of \Delta Y \= 3. These error bars are straightforward to insert into your Excel chart.

To create the chart above, select the X and y values, with the header as shown in Fig. 2. Go to the Insert tab on the ribbon and select a Scatter Chart in the Charts group. This is used to plot pairs of data points.

Fig. 2: Insert a scatter chart

There are variety of scatter charts available and you can see previews of each type as you hold your mouse pointer over the different scatter chart buttons. We are going to choose the simplest which just plots the points, so click on the button indicated above in light green – this adds the chart to your worksheet.

Select the chart with a left mouse click on any part of the chart. The range of points used in the chart will be highlighted on the worksheet. You can select specific elements of the chart by clicking on that element, for example, select the data points by clicking on one of the points – all the the data points in that points data series will be highlighted. Note that if you have multiple data series, you will need to select the appropriate data series for adding error bars. We can add error bars using the Chart Elements button in the upper right corner of the chart, as shown in Fig. 3, or the Add Chart Element command on the Chart Design tab of the Ribbon, as shown in Fig. 4. Move the mouse pointer to the Error Bars selector and a preview of default error bars will appear. Click on the right pointing arrow next to “Error Bars”.

Fig. 3: Use the Chart Elements button to add error bars

Fig. 4: Use the Add Chart Element command from the Chart Design tab

Depending on the method you choose to add error bars, click on either “More Error Bars Options …” or “More Options …” to open the Format Error Bars dialog – the same dialog opens either way, as shown in Fig. 5.

Fig. 5: The Format Error Bars Dialog

Note that we use the Error Bar Options drop down to choose whether we are editing horizontal or vertical error bar properties.

Fig. 6: The Error Bar Options Chooser

We are going to add the Y error bars first, so select ‘Series “Y” Y Error Bars’. The specific name of the series you see will depend on the Y value header name of your data range. When you select “Y Error Bars”, the Format Error Bars dialog will indicate “Vertical Error Bar” as shown in Fig. 7.

Fig. 7: The Format Error Bars Dialog

We are interested in assigning a single error bar size to all the Y data values. The default value is “1.0”. In our case we want the value “3.0” for our Y errors so choose the “Fixed Value” radio button and make the change in the text box and hit Enter after you’ve filled in the value – the error bars on the chart will all be adjusted to the new value. In our case, we also have errors for the X values. To enter those values, click on the down arrow next to “Error Bar Options” and select “X Error Bars”. The panel will now indicate that the options are for the “Horizontal Error Bar”. We again want a Fixed Value in the Error Amount area – the default is “1.0” so change that to “0.3” and hit the Enter key.

Fig. 8: The chart with final constant error bars

Note in the Error Bar Options dialog, there are options that haven’t been mentioned yet:

• Direction – indicates whether the vertical error bar extends above the data point, below the data point, or both. We have chosen both for our error bars. The horizontal error bars work similarly.
• End Style – indicates whether we have a short line at the ends of the error bars, directed at right angles to the actual bar. We have the Cap selected

The chart of Fig. 8 shows all the data points, with the same horizontal and vertical error bars for each data point. Typically we would add a title to the chart, along with axis labels and perhaps some other chart elements, but our concern here is just the error bars.

Constant Fractional Error Bars

We went through the details of adding constant error bars in the previous section so we won’t go through all the details of inserting a chart and finding our way to the Format Error Bars dialog. Constant fractional error bars are called “Percentage” in Excel. Working with the same data values we used in the previous section, we are going to have errors of 7% for both the X and Y values. The resulting graph will look like Fig. 9.

Fig. 9: Y vs X With Constant Factional Error (Percentage)Select the data range and add the chart as described in the previous section. Use the Chart Elements button in the upper right of the chart to open the Format Error Bars dialog as shown in Fig. 10.

Fig. 10: Format Error Bars Dialog for Fractional Error (Percentage)

Use the Error Bar Options drop-down to select the Y axis error bars. Check that the Percentage radio button is selected in the Error Amount group, and add the desired fractional error into the textbox – this is 7% in our case. Hit the Enter key when done and the error bars will update in the chart.

Now use the Error Bar Options drop-down to select the X axis error bars, enter 7% into the Percentage text box and hit Enter. The final error bars are shown in Fig. 11.

Fig. 11: Y vs X With Constant Factional Error (Percentage)

Note how the fractional error bars become larger as the value of the data point gets larger. In the y direction, the change in length of the error bars is quite small because the data values are roughly between 25 and 35. In the X direction, the data values are from 1 to 12 so we see a big change in the length of the error bars along that axis. Compare the error bars of Fig. 11 to the fixed length error bars of Fig. 8.

Error Bars That Vary With Each Data Point

The error bars we have looked at so far are either constant error values for each data point, or constant fractional error values for each data point. Depending on the particular analysis we are doing, we may wind up with a set of data values that each have a different error bar. Fortunately, Excel allows us to add error values for each data point if we need to. Fig. 12 shows data and error values for both X and Y – the data values are in columns A and B with headers X and Y, and the errors for each point are in columns C and D, with headers {\Delta X} and {\Delta Y}.

Fig. 12: Y vs X With Variable Error Values

Insert a scatter chart with the X and Y data from columns A and B, as described above. I will use a scatter chart with only the points shown. Click on the chart and open the Format Error Bars dialog. In the Error Bar Options drop-down select the Y Error Bars, and under Error Amount, select the Custom radio button.

Fig. 13: Format Error Bars Dialog for Variable Error Values

We need to tell Excel where the error values are for the Y data values so click on the Specify Value button. This brings up the Custom Error Bars dialog box. There are two text fields in this box, one for the error values that go above the data value and one for error values that go below the data value. This provides us with great flexibility in creating error bars but if we want to have the same size error bar above the data points as below we have to explicitly tell Excel that is what we want.

Fig. 14: Custom Error Bars Dialog Box – Default

Note that the default error bar size is 1 up and 1 down from the data point. To select the range containing the Y error bars, delete the default entry from the text box “={1}”, and use the mouse to drag over the range of values to be used as errors. When the left mouse button is released, the range will be added to the text box. Do the same for both the Positive and Negative Error Value text boxes. In this case we are using the same error values so the Custom Error Bars dialog will look similar to Fig. 15.

Fig. 15: Custom Error Bars Dialog Box – Filled

Note that the name of the worksheet used for this example is ‘Given (2)’ and the cell references are absolute so have dollar signs ($). Click OK when done and the Y errors bars will be drawn on the chart.

Now select X Error Bars in the Error Bar Options and repeat the process for the X error bars. When you are done the chart will look similar to Fig. 16 except for some changes to line thickness intended to make lines easier to see.

Fig. 16: Y vs X With Variable Error Values

We have other options for adding error bars which will be discussed in future posts. These involve some statistics and so need more discussion. In this post we have described three types of error bars that can be easily added to Excel charts. In these cases the error values are given to us with the data and we don’t need to do any additional analysis – simply include the errors.