Year On Year Chart with dynamic ranges. (sorry long post)

[kedark78]

I am trying to create a chart with YOY growth on secondary axis. I will be having many charts in the workbook hence want to create a chart using dynamic named range which will return values of YOY growth as an array which I can use as the source for the secondary axis chart. The data looks like below and the formula for YOY values is. =(B14-B2)/B2 * 100 ie (current value - last year value)/(last year value) (First 12 values will be obviously #N/A as a year is not ellapsed) The data lables start at A1.
[TABLE="class: grid, width: 241"]
<tbody>[TR]
[TD]Date[/TD]
[TD] Share
price[/TD]
[TD]YOY (using offset)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/31/1990[/TD]
[TD="align: right"]92.3[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]=NA()[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/1990[/TD]
[TD="align: right"]92.5[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"]3/31/1990[/TD]
[TD="align: right"]91.9[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"]4/30/1990[/TD]
[TD="align: right"]91.7[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"]5/31/1990[/TD]
[TD="align: right"]93.1[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"]6/30/1990[/TD]
[TD="align: right"]93[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"]7/31/1990[/TD]
[TD="align: right"]92.6[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"]8/31/1990[/TD]
[TD="align: right"]92.3[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"]9/30/1990[/TD]
[TD="align: right"]91.1[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"]10/31/1990[/TD]
[TD="align: right"]90.9[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"]11/30/1990[/TD]
[TD="align: right"]90.3[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"]12/31/1990[/TD]
[TD="align: right"]90.1[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"]1/31/1991[/TD]
[TD="align: right"]90.2[/TD]
[TD="align: right"]-2.275189599[/TD]
[TD="align: right"]=(B14-B2)/B2 * 100[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/1991[/TD]
[TD="align: right"]89.7[/TD]
[TD="align: right"]-3.027027027[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]3/31/1991[/TD]
[TD="align: right"]89.4[/TD]
[TD="align: right"]-2.720348205[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]4/30/1991[/TD]
[TD="align: right"]89.3[/TD]
[TD="align: right"]-2.617230098[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

I have about 50 values for this example. I have setup following named ranges.
1. xValues = =Sheet1!$A$2:$A$51
2. YearOffset = =ROW(OFFSET(Sheet1!$A$1,0,0,COUNTA(xValues),1))-12
Year offset returns array -11, -10, -9....38 for 50 values
3. yValues = =Sheet1!$B$2:$B$51
4. yValues2 = =IF(YearOffset<=0,NA(),(INDEX(yValues,YearOffset+12)-INDEX(yValues,YearOffset))/INDEX(yValues,YearOffset)) * 100

Yvalues2 returns me array as below when I give = yvalues2 in an array formula (and this is what I want it to return)

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]-2.27519[/TD]
[/TR]
[TR]
[TD="align: right"]-3.02703[/TD]
[/TR]
[TR]
[TD="align: right"]-2.72035[/TD]
[/TR]
[TR]
[TD="align: right"]-2.61723[/TD]
[/TR]
</tbody>[/TABLE]

My problem is -
I can see these values in worksheet cells when i use =Yvalues2 as an array formula over 50 cells. However when I use the same for the chart series it shows me these values calculated as 0 and shows a straight graph starting 13th value. (as previous values are #N/A)
If I give =COUNTA(yValues2) then it returns me 50 however if I see that in Formula auditing - evaluate formula it shows me the values as #N/A for first 12 values and then #Value for remaining values which gives me the reason why the chart is shown flat on secondary axis.

I am wondering what I may be doing wrong or this is not how it is supposed work. I can certainly do this by adding a dummy column to have these values appear however I will be having 50 odd charts and will need to create those many additional series. I have to add few more tweaks later as to allow quarterly growth and 5 year growth in this one in a drop down. Any help pointers appreciated. The chart is inspired by PED example at http://oaltd.co.uk/DLCount/DLCount.asp?file=ProExcelDev15.pdf
Thanks for your time.

 

[AlphaFrog]

What are the scaling settings for your secondary axis?

If the secondary axis scale covered a range say 0 to -100 then the data would look flat on that scale as it only covers a range from -2.2 to -3.0

 

[kedark78]

I have checked it and it shows 0 when I hover the mouse over the points. I am not too sure how to add a file to message.

 

[kedark78]

I have checked it and it shows 0 when I hover the mouse over the points. I am not too sure how to add a file to message.

don't think I could add my example to this thread hence created a crossposting where I could attach a file. Year On Year Chart series created using named range with formula does not work.. I will post answer here if resolved. the example should be good to understand my issue.