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.

Date	Share price	YOY (using offset)
1/31/1990	92.3	#N/A	=NA()
2/28/1990	92.5	#N/A
3/31/1990	91.9	#N/A
4/30/1990	91.7	#N/A
5/31/1990	93.1	#N/A
6/30/1990	93	#N/A
7/31/1990	92.6	#N/A
8/31/1990	92.3	#N/A
9/30/1990	91.1	#N/A
10/31/1990	90.9	#N/A
11/30/1990	90.3	#N/A
12/31/1990	90.1	#N/A
1/31/1991	90.2	-2.275189599	=(B14-B2)/B2 * 100
2/28/1991	89.7	-3.027027027
3/31/1991	89.4	-2.720348205
4/30/1991	89.3	-2.617230098

<tbody>
</tbody>

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)

#N/A

#N/A

#N/A

#N/A

#N/A

#N/A

#N/A

#N/A

#N/A

#N/A

#N/A

#N/A

-2.27519

-3.02703

-2.72035

-2.61723

<tbody>
</tbody>

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.