Dynamic Ranges - Ignoring Blank Cells with Underlying Formulas

**jmm722** · 02-01-2012, 01:32 PM

Hi,

At the end of a row with a dynamic range I'm using there are cells with underlying formulas that show up blank (contain "" through the formula), but they are showing up as 0 in the chart I have that uses the dynamic range. Below is the formula I'm using:

=OFFSET('Market Return Data'!$F$3,1,0,COUNTA('Market Return Data'!$F:$F)-0)

Does anyone know how to adjust this to skip the blank cells?

Thanks.

**dilipandey** · 02-01-2012, 01:49 PM

Hi Jmm722,

Zeros are dropping up the chart.. right?
To overcome this, you can include =NA() into the formula and this will smoothen the graph trend(s). Post a sample file if you face any challenge to apply this. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**bebo021999** · 02-01-2012, 02:11 PM

Try to peplace COUNTA(...)-0 with COUNTIF('Market Return Data'!F:F,"<>""")-COUNTIF('Market Return Data'!F:F,"")

**jmm722** · 02-01-2012, 02:39 PM

Originally Posted by bebo021999

Try to peplace COUNTA(...)-0 with COUNTIF('Market Return Data'!F:F,"<>""")-COUNTIF('Market Return Data'!F:F,"")

Thanks for the response. No luck, the chart still drops off at the end because of the 0s it's picking up within the blank cells.

**zg825** · 02-25-2014, 04:14 PM

I was having similar issues and came across this board. I know it's late for the OP, but for those after having similar issues. Try using COUNT instead of COUNTA. I was having my blank cells come up as a "", so a COUNTA was counting this cell as having data. When I used the COUNT, it worked perfectly.