Information
NA function: output #N/A intentionally so line charts skip missing periods
Mark missing readings with NA(), distinguish them from zero, and understand chart-specific handling before plotting.
Missing data is not a measured zero
Use #N/A to distinguish an unavailable reading from a measured zero. NA() returns that error marker, and formulas that depend on it can propagate the missing-data state.
Excel chart behavior depends on chart type and empty-cell settings; #N/A can create a gap or connect adjacent points. A formula returning an empty string is not a universal way to hide a point.
| Month (A) | RawReading (B) | SensorState (C) | PlotSeries IF(C="Offline", NA(), B) |
|---|---|---|---|
| Jan | 42 | Online | 42 |
| Feb | 0 | Offline | #N/A |
| Mar | 46 | Online | 46 |
Emit NA() for offline or future periods
IF(C3="Offline", NA(), B3) outputs #N/A for February while passing January's 42 untouched.
=IF(C3="Offline", NA(), B3)Summarize series containing #N/A with IFNA
Handle each missing scalar explicitly before aggregation. Decide whether missing observations should be excluded or should make the total unavailable; replacing them with zero is a modeling decision, not a neutral correction.
=ISNA(IF(C3="Offline", NA(), B3))Try the example
Use the original values or change them to test your understanding.