Altering the Series Formula in a Chart

[Cyberdude]

I?m using the standard formulas with a defined name to implement the Series formula in a chart. They typically look like this:
=OFFSET(Main!$A$3,0,0,COUNTA(Main!$A:$A))
and
=OFFSET(Main!$B$3,0,0,COUNTA(Main!$B:$B))

I would like to control the value of the COUNT sections of these formulas in such a way that the chart plots all values up to, but NOT including, the bottom values in columns ?A? and ?B?. The obvious change to make would be to subtract 1 from the count like this:
=OFFSET(Main!$A$3,0,0,COUNTA(Main!$A:$A) - 1)
and
=OFFSET(Main!$B$3,0,0,COUNTA(Main!$B:$B) - 1)
but these changes seem to be ignored ? nothing happens.

I?ve determined that I can use a different expression than COUNTA such as:
=OFFSET(Main!$B$3,0,0, Main!$L$12 - 1)
where ?L12? contains the number of data points, and it works just fine.

My question is, why doesn?t it work when I subtract a 1 from the value of the COUNTA expression. This would be my preferred solution if I could do it.

[Andy Pope]

Hi,

Your offset is anchored to A3. Is there any information in A1:A2?
If so you will need to take that into account.

[Cyberdude]

Hi, Andy!
Ahhhh, the Main!$A:$A) notation starts in row 1, right? I didn't think of that. Still, it seems that the count (even if incorrect) should be decremented by 1, and it doesn't change, judging from what I see plotted.
Hmmmm.
Thanks, Andy.

[Andy Pope]

If you have something in A1 or A2 you will need

=OFFSET(Main!$A$3,0,0,COUNTA(Main!$A:$A) - 1)

if you have something in both A1 and A2 you will need

=OFFSET(Main!$A$3,0,0,COUNTA(Main!$A:$A) - 2)