How to handle #N/A error values to make charts look more presentable

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,

NA_Charts01

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.

NA_Charts02

Now the plot ignores day 5 and it shows no data point on day 5.

As always love to have your comments.

Also read


No Responses

Show all responses

Leave a Reply