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.
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,
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.
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.
Wow. Thanks for sharing this. Really useful stuff
ReplyDeleteAdvanced microsoft excel training in chennai, nungambakkam