I have a weight spreadsheet which has been running for the past 3 years and have
just now set up a dynamic weight chart covering the last 100 days based on
information from a Jon Peltier paper on dynamic charts.

The data in the Weight workbook is set up as follows:-
Col A - date
Col B - AM weight
Col C - 7day AM weight average

The original formula for the X axis data was
ChrtCats=offset(Weight!$A$1,counta(Weight!$A:$A)-1,0,-ChrtLen,1)
where ChrtLen =100 and the Y axis data is given by
ChrtValA=offset(ChrtCats,0,1).

This formula had to be modified to
ChrtCats=offset(Weight!$A$13,counta(Weight!$B:$B)-1,0,-ChrtLen,1)
because
1. the end x axis date was wrong presumably because there are 10 blank entries
in Column B and changing to Weight!$A$13 fixed that, and
2. Column A has dates set up extending to end of 2006 hence the
Counta(Weight!$B:$B) change as Column B only has data to the current date.

What puzzles me is why the Weight!$A$1 had to be changed to Weight!$A$13 to make
the X axis dates end on the current date?

I could understand it if this was changed to weight!$A$11 - i.e. 1 + the number
of blank cells in column B and that was what I originally tried. By trial and
error I finally settled on $A$13.

Can anyone explain why the figure 13 works? Even better can anyone give me a
correction to the ChrtCats formula to allow for future blank entries in column B
so that I don't have to fiddle with the formula each time another blank cell
happens?

--

Cheers . . . JC

I have done some follow up testing to see if I could explain the "funnies" in my
report below.

The headers run to 7 rows and the result of countif($B1:$B7,"") is 2. There
are 3 headers that are created by merging a number of columns including column
B. Two of these are large in height - 76 pixels and it is these two rows that
produce the 2 blank cells - exactly why is a puzzle.

This helps explain the corrections I had to make to get the chart to work:-
i.e. from
ChrtCats=offset(Weight!$A$1,counta(Weight!$B:$B)-1,0,-ChrtLen,1)
to
ChrtCats=offset(Weight!$A$13,counta(Weight!$B:$B)-1,0,-ChrtLen,1)

The 13 comes from 1 + no. of blanks (10) + no. of blanks in headers (2)
= 1 + 10 + 2 =13.

To make things easier to understand, and fix, when new blank cells are created I
have changed the formula to
ChrtCats=offset(Weight!$A$2,counta(Weight!$B:$B)+1 0,0,-ChrtLen,1)
so that all I need to do is amend the +10 to equal the new number of blank cells
in column B.

This has been an interesting exercise.

JC

On Sun, 27 Mar 2005 21:29:52 +1000, JC wrote:

I have a weight spreadsheet which has been running for the past 3 years and have
just now set up a dynamic weight chart covering the last 100 days based on
information from a Jon Peltier paper on dynamic charts.

The data in the Weight workbook is set up as follows:-
Col A - date
Col B - AM weight
Col C - 7day AM weight average

The original formula for the X axis data was
ChrtCats=offset(Weight!$A$1,counta(Weight!$A:$A)-1,0,-ChrtLen,1)
where ChrtLen =100 and the Y axis data is given by
ChrtValA=offset(ChrtCats,0,1).

This formula had to be modified to
ChrtCats=offset(Weight!$A$13,counta(Weight!$B:$B)-1,0,-ChrtLen,1)
because
1. the end x axis date was wrong presumably because there are 10 blank entries
in Column B and changing to Weight!$A$13 fixed that, and
2. Column A has dates set up extending to end of 2006 hence the
Counta(Weight!$B:$B) change as Column B only has data to the current date.

What puzzles me is why the Weight!$A$1 had to be changed to Weight!$A$13 to make
the X axis dates end on the current date?

I could understand it if this was changed to weight!$A$11 - i.e. 1 + the number
of blank cells in column B and that was what I originally tried. By trial and
error I finally settled on $A$13.

Can anyone explain why the figure 13 works? Even better can anyone give me a
correction to the ChrtCats formula to allow for future blank entries in column B
so that I don't have to fiddle with the formula each time another blank cell
happens?