Thread: Adding a horizontal line to a chart

Hi All,

I've recently read various articles on Adding a horizontal line to a chart. However, my chosen method (that avoids the charted threshold values appearing on the worksheet) provided in an article by Tushar Mehta MVP is proving somewhat problematic for me.

In Tushar's working example the X axis numerical data corresponded to the actual respective Row number - eg:

http://www.tushar-mehta.com/excel/ch...traight_lines/
X axis
Row4 4
Row5 5
Row6 6
Row7 7
Row8 8

However, My X-axis data (numbers) does not correspond to its exact same Row number as in Tushar's example data. Is there an alternative formula or a slight variation I can use to utilise the suggested method to avoid the charted threshold values appearing on the worksheet?

My X-axis and Y-axis data is in Columns A and B respectively and starts from ROW 432 to ROW 492.

X Y
ROW432 1 85
ROW433 2 52
ROW434 3 95
ROW435 4 69
ROW436 5 57
ROW437 6 71
ROW438 7 70
ROW439 8 60
ROW440 9 90
ROW441 10 101
ROW442 11 67 etc.

I've tried various syntax variations with INDIRECT and INDEX but to no avail.

Below is an extract of Tushar's formula:
aRng=sheet1!$C$4:$D$18
meanLine=AVERAGE(OFFSET(aRng,0,1))*ROW(aRng)/ROW(aRng)

How does the meanLine named formula work?
(a) OFFSET(aRng, 0,1) selects the 2nd column of data, i.e., the y-values.
(b) AVERAGE(...) gives the average of y-values.
(c) ROW(aRng)/ROW(aRng) creates an array of ones. The array has as many data points as in the original data.
Multiplying (b) by (d) replaces the ones in the array by the average value.

Is a variation of the formula possible to work with X axis numerical data that does not match/ correspond to its identical Row number?

Much appreciated.

Kind regards,
Tinä

Here is an alternate approach:

http://www.exceldashboardtemplates.com/?p=572


Steve=True