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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |