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  

Zero Values on Charts



 
 
Thread Tools Display Modes
  #1  
Old February 2nd, 2006, 09:21 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Zero Values on Charts


I am currently developing a spreadsheet which will allow the Actuals,
Forecast and Budgets of a financial year to be viewed.

The data will spread across financial periods i.e. P1 - P12 and by
their nature the forecast and budget data will be filled in for all
periods.

My problem is that I intend to add the actuals at the end of each
month, so that they can be viewed against budget and forecast. However,
I don't want to have the line returning to 0 when the actuals for
certain periods haven't been entered.

Does anyone know how you can have a line graph that looks at a range
for actuals (the cells contain formulas to automatically update)
without showing the 0 values? I haven't found a way to stop the graph
returning the 0 values.

Any help would be greatly appreciated.


--
SkyBluesFan
------------------------------------------------------------------------
SkyBluesFan's Profile: http://www.excelforum.com/member.php...o&userid=28215
View this thread: http://www.excelforum.com/showthread...hreadid=507863

  #2  
Old February 2nd, 2006, 10:37 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Zero Values on Charts

Apparently you're using formulas to pull in the data. Use something like
this:

=IF(link or calculation=0,NA(),link or calculation)

The NA() produces an ugly #N/A error in the table, which you can hide using
conditional formatting. No point is plotted for #N/A, but a line will
connect the points on either side of it. There is no line for any
consecutive #N/A values at the start or end of a range.

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


"SkyBluesFan"
wrote in message
...

I am currently developing a spreadsheet which will allow the Actuals,
Forecast and Budgets of a financial year to be viewed.

The data will spread across financial periods i.e. P1 - P12 and by
their nature the forecast and budget data will be filled in for all
periods.

My problem is that I intend to add the actuals at the end of each
month, so that they can be viewed against budget and forecast. However,
I don't want to have the line returning to 0 when the actuals for
certain periods haven't been entered.

Does anyone know how you can have a line graph that looks at a range
for actuals (the cells contain formulas to automatically update)
without showing the 0 values? I haven't found a way to stop the graph
returning the 0 values.

Any help would be greatly appreciated.


--
SkyBluesFan



  #3  
Old February 2nd, 2006, 10:54 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Zero Values on Charts


Thank you for your swift and helpful response.

The solution was just what I needed.


--
SkyBluesFan
------------------------------------------------------------------------
SkyBluesFan's Profile: http://www.excelforum.com/member.php...o&userid=28215
View this thread: http://www.excelforum.com/showthread...hreadid=507863

  #4  
Old February 2nd, 2006, 10:55 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Zero Values on Charts


Thank you for your swift and helpful response.

The solution was just what I needed.


--
SkyBluesFan
------------------------------------------------------------------------
SkyBluesFan's Profile: http://www.excelforum.com/member.php...o&userid=28215
View this thread: http://www.excelforum.com/showthread...hreadid=507863

 




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
Null values in charts and how to override the goal seek functionality Sarge Charts and Charting 1 January 27th, 2006 04:57 AM
Auto Display of X and Y axis values for multiple charts in WKBK Barb Reinhardt Charts and Charting 0 January 11th, 2006 03:46 PM
hiding zero values in charts fascal Charts and Charting 4 December 19th, 2005 03:17 PM
Displaying null values in charts Iain ASLD Charts and Charting 2 October 26th, 2005 12:24 PM
Font Size for Values in Charts Jaci Charts and Charting 1 May 27th, 2005 08:24 PM


All times are GMT +1. The time now is 10:37 AM.


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