Counting blank cells
 

Hello,



I need to chart a series from a column. Problem is i need to include
blanks. My x axis has no blanks and the
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1) works like a charm to
ensure I catch all the dates. But when I use this formula for my data series
(with blanks) it will not count blanks and cannot keep the series data
linked to the correct date.

How could I get around this?

Thanks, Kevin Graham

Counting blank cells
 

Hi Kevin
to get the row number for the last entry use the following array
entered (CTRL+SHIFT+ENTER) formulas:
=MAX(ISNUMBER($B$1:$B$1000)*ROW($B$1:$B$1000)
if your range only contains numbers

If you want to get the last row inlcuding text values try (also array
entered):
=MAX(($B$1:$B$1000<"")*ROW($B$1:$B$1000)

So for your OFFSET formula change your formula to the array entered
=OFFSET(Sheet1!$B$2,0,0,MAX(ISNUMBER($B$1:$B$1000) *ROW($B$1:$B$1000)1,1
)

Frank

Kevin G wrote:
Hello,



I need to chart a series from a column. Problem is i need to include
blanks. My x axis has no blanks and the
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1) works like a charm
to ensure I catch all the dates. But when I use this formula for my
data series (with blanks) it will not count blanks and cannot keep
the series data linked to the correct date.

How could I get around this?

Thanks, Kevin Graham