Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Non Blank Cells | Excel Discussion (Misc queries) | |||
Counting Non Blank Cells | New Users to Excel | |||
Counting non-blank cells | Excel Discussion (Misc queries) | |||
Counting blank cells | Excel Discussion (Misc queries) | |||
counting blank cells | Excel Discussion (Misc queries) |