excel fix value for chart

0
votes

i have some data in excel 2007 and draw a line chart with them, so i have two series: one for data and a series for average to compare data with average. now i want to add a fix value for series 2(average) to draw average line in chart.

series 2(average) value ={Sheet3!$I$18,Sheet3!$I$18,Sheet3!$I$18}
the average value is in i18 cell

but it has error about "the formula you typed contains an Error" any body to help me to draw average line?

my data are in d24:d26 cells
and average is in i18

if i use const number it's working like below:

series 2(average) value ={5003,5003,5003}

but i want read the series value from cells, which on change the average number(i18 cell) the charge series(average line) change too.

enter image description here

1
excelcharts

1 Answers

0
votes

In cell G24 enter

=$I$18

Copy to cells G25 and G26.

Now select cells G24 to G26 and copy. Select the chart and use Paste Special. Accept the defaults. The chart now has the average line.

By definition, a line needs at least two data points, beginning and end. You could leave cell G25 empty, but then you must tell Excel to connect the line between empty cells. That setting is in the Select Data dialog behind the button "Hidden and empty cells". With that approach you can avoid duplicating the average value for each data point.

Here is a screenshot with such a setup.

enter image description here

Edit: It can be done with just one cell but it's a lot more preparation.

  • Add the single cell to the chart as a new series.
  • Change that series to XY Scatter
  • Edit the series values, set X to 1, set Y to the cell value
  • add horizontal error bars to the average series, with settings being Plus, No Cap, Fixed value (count the number of data points in the line and enter that as the fixed value)
  • format the error bar line as desired and format the data marker to be invisible. Here is how this can look:

enter image description here