PDA

View Full Version : [SOLVED] Line graphs in Excel



newk
03-07-2005, 04:19 AM
Hi guys,

I have a load of line graphs and they have been premade for the year. At the moment the graphs are showing the correct data (up to Feb) however when the dates go into the future (March +) the line drops to the floor as you'd expect. How do I get the graphs to register that the graph shouldn't plot a zero entry.

Thanks

Jacob Hilderbrand
03-07-2005, 04:26 AM
You can leave those cells blank instead of 0 and they will not be plotted or you can put a #N/A error in there if they are 0.

So for example if the data is based on a formula try this.


=If(MyFormula=0,NA(),Myformula)

newk
03-07-2005, 04:48 AM
I knew I would have to change the cell to N/a.
The problem I am having is that the sheet copies data over from another worksheet that I don't have access to therefore I can't change the original. My worksheet has a lookup field in it and whenever I put in your formula it comes up with an error. My formula looks like this:


=IF('G:\MonthEnd\monthly summary\services\[2005 summary.xls]2005'!$D$45)=0,NA(),('G:\MonthEnd\monthly summary\services\[2005 summary.xls]2005'!$D$45)

Does this look correct?

Jacob Hilderbrand
03-07-2005, 05:09 AM
Too many parentheses.



=IF('G:\MonthEnd\monthly summary\services\[2005 summary.xls]2005'!$D$45=0,NA(),'G:\MonthEnd\monthly summary\services\[2005 summary.xls]2005'!$D$45)

newk
03-23-2005, 08:55 AM
Hi everyone,

Happy Easter,

Please could someone help me. I am having the same problem again but on another spreadsheet. Whenever I use the formula below it outputs #DIV/0!


=IF(100-(((D28-D29)/D28)*100)=0,NA(),100-(((D28-D29)/D28)*100))

Why? What am I doing wrong? How do I fix it?

Ps. Thanks DRJ, I don't know why I didn't thank you before. Your formula worked fantastically.

JonPeltier
03-23-2005, 10:58 AM
What's in D28? If you're getting #DIV/0, you're dividing something by zero, and the only division in your formula is by D28.

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

Dave
03-23-2005, 11:07 AM
How about this...Dave

=IF(Iserror(100-(((D28-D29)/D28)*100)),NA(),100-(((D28-D29)/D28)*100))
edit: this is too simple on re-read. D28 is the problem. I'll re-post if I come up with anything. Dave

Dave
03-23-2005, 05:39 PM
Maybe this will worK? Dave

=if(Iserror(100-(((D28-D29)/D28)*100)),NA(),If(100-(((D28-D29)/D28)*100))=0,NA(),100-(((D28-D29)/D28)*100))

newk
03-24-2005, 03:45 AM
Hi Jon and Dave,

Thanks for your time guys but still no good.

Both D28 & D29 are empty cells.

Each column is a month so column D's data won't be entered until APRIL. Therefore I would like the cell to register NA so it doesn't show 0 on a premade graph.

Anyway thanks again everyone, if you have any more ideas I'd love to hear them.

JonPeltier
03-24-2005, 05:52 AM
Try this formula. When I pasted in Dave's I got an error. With all those parens, I got confused. Also, I think if the 100-(D28-D29)/D28*100 part equals zero, you want to show zero, not #N/A.


=IF(LEN(D29)=0,NA(),IF(D28=0,NA(),100-(D28-D29)/D28*100))

newk
03-24-2005, 06:58 AM
Hi Jon,

You done it! Fantastic work. That was bugging me so badly.

Thanks Jon and Dave for all your effort thats great.