A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old February 2nd, 2006, 02:53 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Dynamic Range with unused formula messing up x axis on dynamic graph

I'm reposting this to the charting forum to see if anyone knows the
answer to my problem.
The chart can be found he

http://www.dciu.org/cspd/Generic%20P...0Template2.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" - Find
messages by this author
Date: Thu, 12 Jan 2006 14:18:37 +1100
Local: Wed, Jan 11 2006 10:18 pm
Subject: 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" - Find messages by this author
Date: 12 Jan 2006 11:34:09 -0800
Local: Thurs, Jan 12 2006 2:34 pm
Subject: 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" - Find messages by this author
Date: Thu, 12 Jan 2006 13:22:47 -0800
Local: Thurs, Jan 12 2006 4:22 pm
Subject: 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



"cabybake" wrote in message


oups.com...


- 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" - Find messages by this author
Date: 17 Jan 2006 07:43:20 -0800
Local: Tues, Jan 17 2006 10:43 am
Subject: 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" - Find messages by this author
Date: 25 Jan 2006 05:02:20 -0800
Local: Wed, Jan 25 2006 8:02 am
Subject: 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 - Find messages by
this author
Date: Wed, 25 Jan 2006 13:40:45 -0500
Local: Wed, Jan 25 2006 1:40 pm
Subject: 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.



cabybake wrote:
bump



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply





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

  #2  
Old February 2nd, 2006, 07:11 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Dynamic Range with unused formula messing up x axis on dynamic graph

There are lots of ways to count cells. You're probably using COUNTA, which
counts non-empty cells. In your case, you could use COUNT, which counts
cells containing a number. You can get fancier with array formulas if you
have more detailed requirements.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


wrote in message
ups.com...
I'm reposting this to the charting forum to see if anyone knows the
answer to my problem.
The chart can be found he

http://www.dciu.org/cspd/Generic%20P...0Template2.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" - Find
messages by this author
Date: Thu, 12 Jan 2006 14:18:37 +1100
Local: Wed, Jan 11 2006 10:18 pm
Subject: 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" - Find messages by this author
Date: 12 Jan 2006 11:34:09 -0800
Local: Thurs, Jan 12 2006 2:34 pm
Subject: 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" - Find messages by this author
Date: Thu, 12 Jan 2006 13:22:47 -0800
Local: Thurs, Jan 12 2006 4:22 pm
Subject: 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



"cabybake" wrote in message


oups.com...


- 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" - Find messages by this author
Date: 17 Jan 2006 07:43:20 -0800
Local: Tues, Jan 17 2006 10:43 am
Subject: 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" - Find messages by this author
Date: 25 Jan 2006 05:02:20 -0800
Local: Wed, Jan 25 2006 8:02 am
Subject: 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 - Find messages by
this author
Date: Wed, 25 Jan 2006 13:40:45 -0500
Local: Wed, Jan 25 2006 1:40 pm
Subject: 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.



cabybake wrote:
bump



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply





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


  #3  
Old February 2nd, 2006, 09:02 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Dynamic Range with unused formula messing up x axis on dynamic graph

Thank you John. I think this worked. I appreciate your assistance.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Problem - interrupted by #VALUE! in other cells!? Ted Worksheet Functions 17 November 25th, 2005 06:18 PM
formula to set up dynamic range in names Jeff Worksheet Functions 0 February 23rd, 2005 04:45 PM
Two Y axis in Graph Debra Dalgleish Charts and Charting 0 March 20th, 2004 10:39 PM
Statistics, #N/A and dynamic range problem Peo Sjoblom Worksheet Functions 1 December 7th, 2003 09:36 PM
Dynamic range in formula Mr. Smith Worksheet Functions 3 November 29th, 2003 05:38 PM


All times are GMT +1. The time now is 03:56 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.