Dynamic Range with unused formula messing up x axis on dynamic graph

[cabybake]

Hi everyone,
You've all been able to help me a ton in the past and I'm hoping you
can do it now as well.
I have a chart that utilizes dynamic name ranges (OFFSET formulas) and
a dynamic graph that uses those ranges to automatically update the
graph using SERIES. This used to work great until I "tweaked" it.
Here's the problem: I added a new formula to the bottom of a column
with data in it that will add data to this column if another column
gets data added first.
e.g.
Currently Column D has the #28 in it. If the user types 29 in the cell
below it, using an IFstatement, Column F applies the formula in it.
Here is the formula in cells F38:F1000-
=IF(D39="","",$F$8+($A$12*D39))

Now that I have formulas in the "empty" cells of column F, the graph
thinks there is data there and puts placeholder 0s there.

How can I tell my graph to ignore the formulas and only add data if the
range includes numbers only? Thank you, cabybake

 

[ScottO]

If you replace the "" for the True result with NA(), then the chart
will show the unused rows as blank rather than zero. But this will
still extend the axis values beyond the 'used' range.
If you want to restrict the axis length to the 'used' range, then
you'll need to modify the Offset formula. One way would be to refer
to column F and use something like CountIf <>"".
hth
ScottO


| Hi everyone,
| You've all been able to help me a ton in the past and I'm hoping
you
| can do it now as well.
| I have a chart that utilizes dynamic name ranges (OFFSET formulas)
and
| a dynamic graph that uses those ranges to automatically update the
| graph using SERIES. This used to work great until I "tweaked" it.
| Here's the problem: I added a new formula to the bottom of a column
| with data in it that will add data to this column if another column
| gets data added first.
| e.g.
| Currently Column D has the #28 in it. If the user types 29 in the
cell
| below it, using an IFstatement, Column F applies the formula in it.
| Here is the formula in cells F38:F1000-
| =IF(D39="","",$F$8+($A$12*D39))
|
| Now that I have formulas in the "empty" cells of column F, the
graph
| thinks there is data there and puts placeholder 0s there.
|
| How can I tell my graph to ignore the formulas and only add data if
the
| range includes numbers only? Thank you, cabybake
|

 

[cabybake]

I tried a few things with the COUNTIF and SUMIF functions, but I can't
seem to get it to work. Could you be specific in how it would work in
an OFFSET formula? Thanks, caby

 

[Peo Sjoblom]

Assume the offset looks something like

=OFFSET($A$1,,,COUNTA(A:A),)

instead of COUNTA you can use

=OFFSET($A$1,,,SUMPRODUCT(--(A1:A65535<>"")),)

 

[cabybake]

This didn't work yet. Here is my OFFSET formula as it works with the
graph (but not with the hidden formulas)
"F" refers to the line and Dynamic Range called "Aimline"

=OFFSET('Generic Template'!$F$7,1,0,COUNTA('Generic
Template'!$F:$F)-1,1)

I tried what you suggested by putting in this:

=OFFSET('Generic Template'!$F$7,1,0,SUMPRODUCT(--'Generic
Template'!$F8:$F1000<>""))

Can you see what I did wrong? Using this formula, the Aimline did not
show up on the graph at all and the Dates associated with this also
did not show up. Only one point showed and it was in the middle of the
graph. Thank you, caby

 

[cabybake]

bump

 

[Debra Dalgleish]

You may get a response if you post your question in the Charting
newsgroup, and include some detail on the formula, and the chart.