Google Search

Custom Search

How to use #N/A to create line plots look more presentable

Let's look at the example below. The table showing rejects (column B), output (column C) and reject percentage (column D). Reject percentage = B2/C2.


Day Rejects Output Reject Parentage
1 5 300 1.67%
2 80 400 20.00%
3 30 350 8.57%
4 0 0 #DIV/0!
5 45 455 9.89%
6 67 340 19.71%
7 43 360 11.94%
8 89 400 22.25%
9 23 320 7.19%
10 56 330 16.97%


Note on the fourth day there was no production. So we get an error  in the reject percentage column. If we plot the reject percentage, We will get,





On day four there is no production and so no rejects. 
The plot is not accurate as it shows reject percentage as zero. 

Ideally the plot should ignore day 4. 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 5 300 1.67%
2 80 400 20.00%
3 30 350 8.57%
4 0 0 #N/A
5 45 455 9.89%
6 67 340 19.71%
7 43 360 11.94%
8 89 400 22.25%
9 23 320 7.19%
10 56 330 16.97%


Now plot the reject percentage again.



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

As always love to have your comments. 



 

blogger templates | Make Money Online

Google Analytics Alternative ExpiresDefault access plus 1 year