C

#### cabybake

answer to my problem.

The chart can be found here:

http://www.dciu.org/cspd/Generic Prog Mon Template2.xls

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

Reply

2. ScottO

Jan 11, 10:18 pm show options

Newsgroups: microsoft.public.excel

From: "ScottO" <[email protected]> - Find

messages by this author

Date: Thu, 12 Jan 2006 14:18:37 +1100

Local: Wed, Jan 11 2006 10:18 pm

Subject: Re: Dynamic Range with unused formula messing up x axis on

dynamic graph

Reply | Reply to Author | Forward | Print | Individual Message | Show

original | Report Abuse

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

3. cabybake

Jan 12, 2:34 pm show options

Newsgroups: microsoft.public.excel

From: "cabybake" <[email protected]> - Find messages by this author

Date: 12 Jan 2006 11:34:09 -0800

Local: Thurs, Jan 12 2006 2:34 pm

Subject: Re: Dynamic Range with unused formula messing up x axis on

dynamic graph

Reply | Reply to Author | Forward | Print | Individual Message | Show

original | Remove | Report Abuse

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

Reply

4. Peo Sjoblom

Jan 12, 4:22 pm show options

Newsgroups: microsoft.public.excel

From: "Peo Sjoblom" <[email protected]> - Find messages by this author

Date: Thu, 12 Jan 2006 13:22:47 -0800

Local: Thurs, Jan 12 2006 4:22 pm

Subject: Re: Dynamic Range with unused formula messing up x axis on

dynamic graph

Reply | Reply to Author | Forward | Print | Individual Message | Show

original | Report Abuse

Assume the offset looks something like

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

instead of COUNTA you can use

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

--

Regards,

Peo Sjoblom

- Hide quoted text -

- Show quoted text -

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

Reply

5. cabybake

Jan 17, 10:43 am show options

Newsgroups: microsoft.public.excel

From: "cabybake" <[email protected]> - Find messages by this author

Date: 17 Jan 2006 07:43:20 -0800

Local: Tues, Jan 17 2006 10:43 am

Subject: Re: Dynamic Range with unused formula messing up x axis on

dynamic graph

Reply | Reply to Author | Forward | Print | Individual Message | Show

original | Remove | Report Abuse

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

Reply

6. cabybake

Jan 25, 8:02 am show options

Newsgroups: microsoft.public.excel

From: "cabybake" <[email protected]> - Find messages by this author

Date: 25 Jan 2006 05:02:20 -0800

Local: Wed, Jan 25 2006 8:02 am

Subject: Re: Dynamic Range with unused formula messing up x axis on

dynamic graph

Reply | Reply to Author | Forward | Print | Individual Message | Show

original | Remove | Report Abuse

bump

Reply

7. Debra Dalgleish

Jan 25, 1:40 pm show options

Newsgroups: microsoft.public.excel

From: Debra Dalgleish <[email protected]> - Find messages by

this author

Date: Wed, 25 Jan 2006 13:40:45 -0500

Local: Wed, Jan 25 2006 1:40 pm

Subject: Re: Dynamic Range with unused formula messing up x axis on

dynamic graph

Reply | Reply to Author | Forward | Print | Individual Message | Show

original | Report Abuse

You may get a response if you post your question in the Charting

newsgroup, and include some detail on the formula, and the chart.

bump

--

Debra Dalgleish

Reply

« Start of topic « Older Messages 1 - 7 of 7 Newer » End of

topic »