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
|