auto updating chart using offset function - problem with blank cells

[jpanchal]

I am using this formula
=OFFSET((General!$CY$5),COUNT(General!$CY$5:$CY$65536)-1,0,-MIN(Chartlength,COUNT(General!$CY:$CY)))
chartlength is 12, column CY has formula and sometimes it may give blank cells. The values in CY column are plotted on Y axis.
When column CY has any value, this dynamic function selects last 12 data point. But when any of the last 12 cells in CY column is blank (nul value due to calculations corresponding to other cells), the above function selects 12 cells but they are off by one cell.

I tried
=OFFSET((General!$CY$5),COUNTA(General!$CY$5:$CY$65536)-1,0,-MIN(Chartlength,COUNT(General!$CY:$CY))), but it select 12 datapoint from bottom of the column, and not the last 12.

For X axis, values are in column B. for it I am using =OFFSET(General!$B$5,COUNT(General!$B$5:$B$65536)-1,0,-MIN(Chartlength,COUNT(General!$B:$B)),1), and it work perfect because I am entering the data as I get. But data in CY column is calculated from values in other column.

What other function I should use in this case?

[jpanchal]

To make my question more clear, attached is an excel with the data, now the equation is
=OFFSET((General!$G$5),COUNT(General!$G$5:$G$65536)-1,0,-MIN(Chartlength,COUNT(General!$G:$G)),1)

For example, if I delete value in C45, the value in G45 become null due to equation.

This shifts the line chart for DeltaRate.
The line chart shows 0 value in G45 even though it is blank.

How can avoid this shift in data when column G has blank value?
And leave gap/connect data point with line in the line chart when there are blank values in column G?

I hope the question is little clear now.

[Andy Pope]

You can change the DeltaRate named range to run off of the DateLength named ranged, assuming there are no missing dates.

=OFFSET(Datelength,0,5)

same principle can be applied to other named ranges.

The missing data point will be treated as zero. you would need to change the formula in G to get an interpolated line.

=IF(C45="",NA(),-C45+E45)

[jpanchal]

Thank you Andy, it works. Great help.