dynamic chart data includes one row too many

**jrtaylor** · 12-11-2008, 10:26 PM

I have a chart which uses data from an array which increases in size as I add data daily.

I'm having problems with the final row or two of data. Note how the attached chart line "drops off".

the horizontal axis is the date:

=OFFSET(results!$AE$23,0,0,COUNTA(results!$A$23:$A$64000),1)

the vertical axis is a set of data (beginning at about 0 and rising to about 16,000):

=OFFSET(results!$AF$23,0,0,COUNTA(results!$AF$23:$AF$64000),1)

the chart data begins at row 23 and should go to row 908, and stop there. But it seems like it keeps going. Any ideas (aside from cropping the pic?) Thanks!

**Andy Pope** · 12-12-2008, 09:13 AM

Just do a COUNTA on your range as see what value it returns. It's probably counting something that it's not suppose to. Normal when you use COUNTA you subtract a value that is the number of rows that meet the formula test but are not required in the chart.

Also a single series will only do 32k points so the current range is much to large.

**jrtaylor** · 12-12-2008, 02:16 PM

Andy, thanks.

[edited]
I thought the offset formula itself would count the number of rows, since it already contains a "CountA" function.

I changed the named ranges to refer to the actual array itself rather than cells which merely pointed to the array data, but that didn't help. And I reduced the number of rows to 4,000.

=OFFSET(results!$V$23,0,0,COUNTA(results!$V$23:$V$4000),1)

ultimately i made so many changes to the spreadsheet I am not sure what fixed this particular problem, but still there remain some issues. I'm attaching it with notes if anyone has the time to help. (and should this thread be moved to a new one)?

thanks

**Andy Pope** · 12-13-2008, 11:42 AM

The #NUM! in column N are due to MATCH formula missing end item GBP/JPY

=IF(ROWS($A$1:$A2)>$L$2,"",INDEX(A$23:A$4001,SMALL(IF(ISNUMBER(MATCH($D$23:$D$4001,$L$1:$S$1,0)),ROW($A$23:$A$4001)-ROW($A$23)+1),ROWS($A$1:$A2))))

83 if the correct value. You can confirm this by doing variaous COUNTIF formula of the INPUTs and then summing them.

Once you sort all the formula out you should use COUNT instead of COUNTA otherwise the formula will be counted.

**jrtaylor** · 12-13-2008, 07:14 PM

I'm still having trouble with this. The array wants to include items which are not in the "MATCH" range.

These are text items (such as "EUR/GPB")

Is it possible that I should be using "ISTEXT" rather than "ISNUMBER"? (however I tried this without success).

Or.. I wonder if I should start with a blank worksheet in event something is corrupted in the one I'm using now?

thanks.

**Andy Pope** · 12-15-2008, 04:50 AM

Did you mean EUR/GBP instead of EUR/GPB?

The named range will only determine the start and length of the values included in the data series. If you want certain rows to be excluded from the data you will need to filter the data or create a new set of data with those points removed.