Line graphs in Excel

[newk]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

Hi Jon,

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

Thanks Jon and Dave for all your effort thats great.