743

# 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,

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.