Cyberdude
01-15-2007, 11:06 AM
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.
=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.