Learn to handle #N/A error values to make charts look more presentable
Example below has data of production units (column C) and number rejects (column B) per day. Reject % = B2/C2.
| Day | Rejects | Output | Reject Parentage |
| 1 | 15 | 250 | 6.00% |
| 2 | 25 | 500 | 5.00% |
| 3 | 40 | 200 | 20.00% |
| 4 | 56 | 410 | 13.66% |
| 5 | 0 | 0 | #N/A |
| 6 | 35 | 220 | 15.91% |
| 7 | 96 | 350 | 27.43% |
| 8 | 15 | 300 | 5.00% |
| 9 | 23 | 370 | 6.22% |
| 10 | 45 | 290 | 15.52% |
On the fifth day production was zero so the rejects are zero. We get an error in the reject percentage column for day five. If we plot the reject percentage, We will get,
On day five there is no production and so no rejects.
The plot is not accurate as it displays reject percentage as zero.
Ideally the plot should ignore day 5.
To achieve this we need to change the formula in column D.
=IF(ISERROR(B2/C2)=TRUE,#N/A,B2/C2)
Now the table looks like,
| Day | Rejects | Output | Reject Parentage |
| 1 | 15 | 250 | 6.00% |
| 2 | 25 | 500 | 5.00% |
| 3 | 40 | 200 | 20.00% |
| 4 | 56 | 410 | 13.66% |
| 5 | 0 | 0 | #N/A |
| 6 | 35 | 220 | 15.91% |
| 7 | 96 | 350 | 27.43% |
| 8 | 15 | 300 | 5.00% |
| 9 | 23 | 370 | 6.22% |
| 10 | 45 | 290 | 15.52% |
Now plot the reject percentage again.
Now the plot ignores day 5 and it shows no data point on day 5.
As always love to have your comments.
Also read