Need help with OFFSET formula

[pazuuu]

Hi all,

I am unsure of my mistake with this formula in the file attached. The graph stops charting when % is zero, and did not continue with the following month when there is a value.

Any help is appreciated.

Thank you.


Sample.xls

[martindwilson]

well count doesnt count na()
why do you need a dynamic range? you have already limited it with COUNT('Overall '!$F$4:$F$11),1) as it can never be more than f4:f11
if you want the range to be dynamic as you add new rows in above the total row you may be better using
MATCH("total",'Overall '!$A$4:$A1000,0)-1
eg
=OFFSET('Overall '!$F$4,0,0,MATCH("total",'Overall '!$A$4:A1000,0)-1,1) in all the named ranges
or counting
one of the columns the doesn't have na() so if its really only going to be from f4:f11
=OFFSET('Overall '!$F$4,0,0,count(c4:c11),1)

[pazuuu]

Hello,

Thank you for the reply.

I need a dynamic range as the chart is supposed to update with monthly values, as in Sample 2 file. If the monthly value is zero (Jun), it's supposed to join up to the following month (July).

I will try to understand and apply your recommendation.

Sample 2.xls