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.
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.
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!
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!
