Skip Blanks when Graphing in Microsoft Excel

in MES Science2 years ago

Recently I tried graphing in Microsoft Excel but the data that I was graphing had some blank cells and thus the chart was not graphing it well. In this video I go over how Excel deals with graphing as well as calculating cells that include blanks and error values. I also show how you can use the #N/A error value to skip blanks when graphing as well as using the IF() and the ISERROR() functions to deal with calculating functions that include the #N/A (or NA() function) values. This is very important when you have to present data in charts using Excel so make sure to watch this video!


Watch video on:

Download video notes:


View Video Notes Below!


Download these notes: Link is in video description.
View these notes as an article: https://peakd.com/@mes
Subscribe via email: http://mes.fm/subscribe
Donate! :) https://mes.fm/donate
Buy MES merchandise! https://mes.fm/store

Reuse of my videos:

  • Feel free to make use of / re-upload / monetize my videos as long as you provide a link to the original video.

Fight back against censorship:

  • Bookmark sites/channels/accounts and check periodically
  • Remember to always archive website pages in case they get deleted/changed.

Buy "Where Did The Towers Go?" by Dr. Judy Wood: https://mes.fm/judywoodbook
Subscribe to MES Truth: https://mes.fm/truth

Join my forums!

Follow along my epic video series:


NOTE #1: If you don't have time to watch this whole video:

Browser extension recommendations:


Skip Blanks when Graphing in Microsoft Excel

Skip Blanks in Excel Chart.jpg

“Empty” Cell

  • Charts skip this BUT…
    • The data points can’t be connected by a line (using appropriate Chart Type)
  • Functions consider it as 0

Any non number (including space) or non function

  • Charts consider this as 0

0

  • Charts and functions consider it as 0

#N/A or NA()

  • An Error Value: “Value Not Available”
  • Charts skip this when graphing
    • They don’t consider it as 0
  • Applying +/-/=, etc to cells containing #N/A gives an error
    • Thus need to be careful when using IF() function
    • Use ISERROR() function

ISERROR() Function

  • Checks whether a value is an error (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!) and returns TRUE or FALSE.
    • Value is the value you want to test
      • Can refer to a cell, a formula, or a name that refers to a cell, formula, or value

IF() Function

  • Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
  • IF( Logical test , Value if true , Value if false)
    • Logical Test is any value or expression that can be evaluated to TRUE (1) or FALSE(0).
    • Value can refer to a cell, a formula, or a name that refers to a cell, formula, or value

Thus when dealing with cells having the value #N/A:

  • IF(B1 = #N/A, 0, B1) is WRONG
  • IF(ISERROR(B1), 0, B1) is CORRECT
Sort: