excel

profilegysgtclarke
Datacleanandsort.xlsx
Home>Mathematics homework help>Numerical analysis homework help>excel

Order History

Order History Data - Jan 1 - May 30, 2019 Data removed as invalid (if possible, fix data and replace)
Step 1: Cleanse data for .. (save this data on the right for later inspection and/or correciton) (insert data here with errors highlighted)
a. Invalid Invoice # (must start with A followed by 7 digits)
b. Invalid Part # (only the 4 listed in Reference Table are valid)
c. Other invalid data (e.g. text in numerical fields, dates out of range)
c. Zeros -- replace with blanks
Step 2: Compute the Days Late (positive values) or Days early (negative values)
Step 3: Remove outliers (save data for indivudual analysis of cause)
Step 4: Insert the Item name of each order
Step 5: Filter data by Part # showing only data for "12-23451" Outliers (for later analysis of cause)
Step 6: Sort displayed data by "Days Late" (highest to lowest) Q1 0 IQR 0 Ubound 0
Q3 0 Lbound ERROR:#VALUE!
Order date Invoice # Part # Qty Promised delivery date Actual Delivery Date Days Late (early) Outlier Item Name
01/20/19 A2454402 12-23451 4 02/15/19 2/5/2019 10 ERROR:#VALUE! Seat, driver, power, leather, black
04/27/19 A2454475 12-23451 10 05/23/19 5/13/2019 10 ERROR:#VALUE! Seat, driver, power, leather, black
01/23/19 A2454404 12-23451 2 02/18/19 2/9/2019 9 ERROR:#VALUE! Seat, driver, power, leather, black
02/05/19 A2454415 12-23451 4 03/02/19 2/21/2019 9 ERROR:#VALUE! Seat, driver, power, leather, black
03/21/19 A2454446 12-23451 7 04/16/19 4/7/2019 9 ERROR:#VALUE! Seat, driver, power, leather, black
05/31/19 A2454487 12-23451 10 06/26/19 6/17/2019 9 Seat, driver, power, leather, black 03/21/19 A2454446 12-23451 7 04/16/19 4/7/2019 9 ERROR:#VALUE! Seat, driver, power, leather, black
02/21/19 A2454427 12-23451 3 03/18/19 3/10/2019 8 ERROR:#VALUE! Seat, driver, power, leather, black
01/17/19 A2454399 12-23451 1 02/12/19 2/6/2019 6 ERROR:#VALUE! Seat, driver, power, leather, black
02/17/19 A2454424 12-23451 3 03/14/19 3/8/2019 6 ERROR:#VALUE! Seat, driver, power, leather, black
03/14/19 A2454441 12-23451 6 04/09/19 4/4/2019 5 ERROR:#VALUE! Seat, driver, power, leather, black
03/18/19 A2454443 12-23451 4 04/13/19 4/9/2019 4 ERROR:#VALUE! Seat, driver, power, leather, black
03/28/19 A2454453 12-23451 1 04/23/19 4/20/2019 3 ERROR:#VALUE! Seat, driver, power, leather, black
02/23/19 A2454429 10-432 4 03/04/19 3/3/2019 1 283
01/02/19 A2454387 A34321 5 01/12/19 1/11/2019 1 0.03
01/02/19 A2454389 A34321 8 01/12/19 1/11/2019 1 0.03
01/17/19 A2454398 A34321 2 01/27/15 1/26/2015 1 0.03
02/02/19 A2454411 A34321 5 02/12/19 2/11/2019 1 0.03
02/15/19 A2454422 A34321 1 02/25/19 2/24/2019 1 0.03
02/23/19 A2454428 A34321 10 03/04/19 3/3/2019 1 0.03
03/09/19 A2454437 A34321 2 03/19/19 3/18/2019 1 0.03
03/20/19 A2454445 A34321 10 03/30/19 3/29/2019 1 0.03
03/31/19 A2454457 A34321 3 04/10/19 4/9/2019 1 0.03
04/22/19 A2454472 A34321 8 05/02/19 5/1/2019 1 0.03
04/24/19 A2454474 A34321 10 05/04/19 5/3/2019 1 0.03
05/04/19 A2454480 A34321 7 05/14/19 5/13/2019 1 0.03 01/02/19 A2454386 10234 83 01/07/19 1/7/2019 0 0.02
01/02/19 A2454386 10234 83 01/07/19 1/7/2019 0 0.02
01/12/19 B245439 10234 81 01/17/19 1/17/2019 0 0.02
01/18/19 A2454401 10234 13 01/23/19 1/23/2019 0 0.02
02/01/19 A2454410 10234 48 02/06/19 2/6/2019 0 0.02
02/08/19 A2454416 10234 1 02/13/19 2/13/2019 0 0.02
02/15/19 A2454421 10234 57 02/20/19 2/20/2019 0 0.02
02/18/19 A2454425 10234 67 02/23/19 2/23/2019 0 0.02
03/01/19 A2454433 10234 31 03/06/19 3/6/2019 0 0.02
03/12/19 A2454439 10234 69 03/17/19 3/17/2019 0 0.02
03/24/19 A2454450 10234 67 03/29/19 3/29/2019 0 0.02
03/31/19 A2454456 10234 96 04/05/19 4/5/2019 0 0.02
04/11/19 A2454465 10234 41 04/16/19 4/16/2019 0 0.02
04/17/19 A2454470 10234 61 04/22/19 4/22/2019 0 0.02
04/23/19 A2454473 10234 64 04/28/19 4/28/2019 0 0.02
05/12/19 A2454482 10234 45 05/17/19 5/17/2019 0 0.02 01/02/19 A2454388 10-432 2 01/08/19 1/8/2019 0 283
05/24/19 A2454485 10234 68 05/29/19 5/29/2019 0 0.02
01/02/19 A2454388 10-432 2 01/08/19 1/8/2019 0 283
01/08/19 A2454392 10-432 4 01/14/19 1/14/2019 0 283
01/13/19 A2454396 10-432 T 01/19/19 1/19/2019 0 283
01/17/19 A2454400 10-432 01/23/19 1/23/2019 0 283
01/22/19 A2454403 10-432 5 01/28/19 1/28/2019 0 283
01/25/19 54406 10-432 1 01/31/19 1/31/2019 0 283
01/31/19 A2454409 10-432 4 02/06/19 2/6/2019 0 283
02/03/19 A2454412 10-432 02/09/19 2/9/2019 0 283
02/12/19 A2454420 10-432 4 02/18/19 2/18/2019 0 283
02/15/19 A2454423 10-432 5 02/21/19 2/21/2019 0 283
02/20/19 A2454426 10-432 5 02/26/19 2/26/2019 0 283
03/01/19 A2454432 10-432 1 03/06/19 3/6/2019 0 283
03/06/19 A2454435 10-432 1 03/12/19 3/12/2019 0 283
03/09/19 A2454438 10-432 3 03/15/19 3/15/2019 0 283
03/13/19 A2454440 10-432 4 03/19/19 3/19/2019 0 283
03/19/19 A2454444 10-432 3 03/25/19 3/25/2019 0 283
03/21/19 A2454447 10-432 5 03/27/19 3/27/2019 0 283
03/27/19 A2454452 10-432 5 04/02/19 4/2/2019 0 283
04/02/19 A2454458 10-432 5 04/08/19 4/8/2019 0 283
04/04/19 A2454460 10-432 4 04/10/19 4/10/2019 0 283
04/05/19 A2454462 10-432 3 04/11/19 4/11/2019 0 283
04/07/19 A2454464 10-432 4 04/13/19 4/13/2019 0 283
04/13/19 A2454469 10-432 3 04/19/19 4/19/2019 0 283
01/02/19 A2454385 12-23451 8 01/28/19 1/28/2019 0 ERROR:#VALUE! Seat, driver, power, leather, black
04/19/19 A2454471 12-23451 7 05/15/19 5/15/2019 0 ERROR:#VALUE! Seat, driver, power, leather, black
01/08/19 A2454393 A34321 2 01/18/19 1/18/2019 0 0.03
01/24/19 A2454405 A34321 2 02/03/19 2/3/2019 0 0.03
01/28/19 A2454407 A34321 9 02/07/19 2/7/2019 0 0.03
02/05/19 A2454414 A34321 5 02/15/19 2/15/2019 0 0.03
02/28/19 A2454430 A34321 4 03/09/19 3/9/2019 0 0.03
03/06/19 A2454434 A34321 4 03/16/19 3/16/2019 0 0.03
03/15/19 A2454442 A34321 4 03/25/19 3/25/2019 0 0.03
03/23/19 A2454448 A34321 9 04/02/19 4/2/2019 0 0.03
03/29/19 A2454455 A34321 7 04/08/19 4/8/2019 0 0.03
04/04/19 A2454459 A34321 2 04/14/19 4/14/2019 0 0.03
04/05/19 A2454461 A34321 4 04/15/19 4/15/2019 0 0.03
04/11/19 A2454466 A34321 3 04/21/19 4/21/2019 0 0.03
04/13/19 A2454468 A34321 8 04/23/19 4/23/2019 0 0.03
05/01/19 A2454478 A34321 3 05/11/19 5/11/2019 0 0.03 03/25/19 A2454451 10-432 5 03/31/19 4/1/2019 -1 283
04/28/19 A2454476 10234 51 05/03/19 5/4/2019 -1 0.02
05/15/19 A2454483 10234 78 05/20/19 5/21/2019 -1 0.02 04/11/19 A2454467 12-23451 1 05/07/19 5/8/2019 -1 ERROR:#VALUE! Seat, driver, power, leather, black
03/23/19 A2454449 10-432 5 03/29/19 3/30/2019 -1 283
03/25/19 A2454451 10-432 5 03/31/19 4/1/2019 -1 283
03/28/19 A2454454 10-432 2 04/03/19 4/4/2019 -1 283
04/11/19 A2454467 12-23451 1 05/07/19 5/8/2019 -1 ERROR:#VALUE! Seat, driver, power, leather, black
05/10/19 A2454481 12-23451 7 06/05/19 6/7/2019 -2 ERROR:#VALUE! Seat, driver, power, leather, black
04/06/19 A2454463 12-23451 1 05/02/19 5/5/2019 -3 ERROR:#VALUE! Seat, driver, power, leather, black
02/10/19 A2454419 12-23451 1 03/07/19 3/11/2019 -4 ERROR:#VALUE! Seat, driver, power, leather, black
05/24/19 A2454486 12-23451 3 06/19/19 6/24/2019 -5 ERROR:#VALUE! Seat, driver, power, leather, black
03/08/19 A2454436 12-23451 9 04/03/19 4/9/2019 -6 ERROR:#VALUE! Seat, driver, power, leather, black
01/07/19 A2454391 12-23451 6 02/02/19 2/9/2019 -7 ERROR:#VALUE! Seat, driver, power, leather, black
01/14/19 A2454397 12-23451 9 02/09/19 2/16/2019 -7 ERROR:#VALUE! Seat, driver, power, leather, black
02/28/19 A2454431 12-23451 10 03/25/19 4/1/2019 -7 ERROR:#VALUE! Seat, driver, power, leather, black
05/02/19 A2454479 12-23451 4 05/28/19 6/4/2019 -7 ERROR:#VALUE! Seat, driver, power, leather, black
01/04/19 A2454390 13-1241 2 01/30/19 2/6/2019 -7 194
04/29/19 A2454477 12-23451 4 05/25/19 6/2/2019 -8 ERROR:#VALUE! Seat, driver, power, leather, black
05/18/19 A2454484 12-23451 2 06/13/19 6/21/2019 -8 ERROR:#VALUE! Seat, driver, power, leather, black
01/10/19 A2454394 12-23451 3 02/05/19 2/14/2019 -9 ERROR:#VALUE! Seat, driver, power, leather, black
02/04/19 A2454413 12-23451 1 03/01/19 3/10/2019 -9 ERROR:#VALUE! Seat, driver, power, leather, black
02/09/19 A2454417 10-432 2 02/15/19 5/15/2019 -89 283
01/31/19 A2454408 12-23451 1 02/26/19 8/19/2019 -174 ERROR:#VALUE! Seat, driver, power, leather, black
02/10/19 A2454418 A34321 7 02/20/19 Not received 0.03

Reference Table

Part Numbers Item Name Supplier Unit Price
10234 Screw, #8x12, T34 Stainless Acme fittings $ 0.02
10-432 Display, Premium, 8" touch Samsung $ 283.00
12-23451 Seat, driver, power, leather, black Northwest $ 194.00
A34321 Bolt, 1/4", T34 Stainless Acme fittings $ 0.03