Learn Excel from MrExcel "Prevent Chart Drop to Zero": Podcast #1704

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on May 7, 2013.
Imagine This: You have a Sales chart displaying your particular transactions. Everything looks good until you are looking at unfilled, future dates - it looks as though your company has gone off the map! Today, in Episode #1704, Bill "MrExcel" Jelen shows us how to deal with the 'Drop to Zero' issue for future dates in our Chart.

For more information on Charts and Graphs in Excel 2013, see bill Jelen's new book "Excel 2013 Charts and Graphs". Use Excel 2013's radically revamped charting and graphing tools to communicate more clearly, powerfully, and quickly... so you drive your message home, and get the decisions and actions you're looking for! This book reveals data visualization techniques you won't find anywhere else and shows you how to use Excel 2013 to create designer-quality charts and graphs that stand out from the crowd. Charts and Graphs: Microsoft Excel 2013

and...

for more information on Charts and Graphs in Excel 2010, see the book, "Charts And Graphs: Microsoft Excel 2010", by Bill Jelen a.k.a. MrExcel. Gift Certificate

For all of your Microsoft Excel needs visit MrExcel.com-- Your One Stop for Excel Tips and Solutions.
maxresdefault.jpg


Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1704 - Prevent the Chart Drop to Zero!
Today's question sent in from a reader at CFO.
Has a chart here that, the future months are dropping to zero, makes it look like the company just went out of business, and this happens when your chart source is usually a formula.
It's like in this case, it's a SUMIF function that's summing up from a database.
And of course, the future months end up with zero.
Now in Excel 2010 and earlier, the solution here is to make your formula to generate an #N/A instead of that 0.
So I'm going to copy all this to the clipboard, Ctrl+C. I'll start the formula with =IF( , if that original formula is =0, then we want the NA function, NA(), comma, otherwise we want the original formula, Ctrl+V, ) , and we will double click to shoot that down.
You see right there, the future points that have #N/A do not get a point at all.
Alright now, we still have this partial month of May, I don't know how we want to deal with that, but at least you don't have those that drop to zero.
The bad news here is, in Excel 2013 this trick no longer works, the #N/A's continue to be plotted as points, very unfortunate set of steps there.
So hopefully, this is Excel 2010 or earlier, and we have a solution!
Alright hey, I want to thank everyone for stopping by, we'll see you next time for another netcast from MrExcel!
 

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top