H 1 J K L M N o P B C D E F G 1 Practice 3 2 Analyze product sales. For each order, customer name, contact info are given. 3 Product and order quantity as well as order date and day of week are given. 4 6 questions 5 6 #1: (Dfunction) How many customers placed a LARGE order (greater than average)? 7 #4: Write a VLOOKUP or HLOOKUP function to dynamically find the shipping cost from the report table given product name and order day Shipping Cost Product Order Day mug Friday 8 9 10 #2: (Dfunction) What was the total number of highlighters ordered on the weekend? 11 #5: Highlight the given product name col, order day row, and cost value (using three conditional formatting rules) 12 13 14 15 #3: (Dfunction) Who placed the largest order on a Friday? 16 17 #6: Use an OFFSET or INDEX function to find which product has the highest shipping cost on Wednesday? Product Shipping Cost tshirt Monday $11,21 Tuesday $13,85 Wednesday $14,41 Thursday $14,85 Friday $14,97 Saturday $17,21 Sunday $19,74 notebook $13,57 $ $14,50 $16,51 $16,84 $17,60 $19,62 $21,31 mug $11,64 $11,98 $12,14 $14,18 $16,73 $18,81 $20,72 pencils highlighters $13,72 $12,08 $15, 16 $13,28 $17,77 $14,55 $17,90 $16,33 $20,19 $18,11 $22.34 $20,11 $22,57 $22,09 mug 18 19 Customer Name Contact Info Product QuantityOrder Date Order Day 20 P. Qui 679-8420 highlighter 15 03.04.2019 Wednesday 21 L. Morris 365-2883 tshirt 6 04.04.2019 Thursday 22 V. Wu 150-4751 notebook 5 08.04.2019 Monday 23 L. Morris 365-2883 highlighter 4 08.04.2019 Monday 24 Y. Zeenith 171-9949 mug 3 09.04.2019 Tuesday 25 1. Jameston 889-8296 highlighter 9 09.04.2019 Tuesday 26 C. Dovestail 974-1735 mug 9 09.04.2019 Tuesday 27 V. Wu 150-4751 mug 2 10.04.2019 Wednesday 28 T. Unigo 311-4039 highlighter 8 11.04.2019 Thursday 29 S. Tavshan 347-6423 notebook 10 12.04.2019 Friday 30 M. Neilson 644-4430 tshirt 7 14.04.2019 Sunday 31 D. Everest 134-8405 1 15.04.2019 Monday 32 T. Unigo 311-4039 mug 3 16.04.2019 Tuesday 33 N. O'Donnal 890-2767 notebook 2 16.04.2019 Tuesday 34 H. Ignacio 500-2539 tshirt 8 17.04.2019 Wednesday 35 W.Yeeshoo 921-5243 pencils 3 19.04.2019 Friday 36 B. Chesterfield 898-1107 notebook 6 20.04.2019 Saturday 37 N. O'Donnal 890-2767 notebook 8 21.04.2019 Sunday 38 O. Peterson 183-7361 tshirt 7 22.04.2019 Monday 39 V. Wu 150-4751 mug 3 23.04.2019 Tuesday 40 V. Wu 150-4751 mug 1 23.04.2019 Tuesday 41 Q. Ronaldo 682-4210 tshirt 9 24.04.2019 Wednesday 42 L. Morris 365-2883 notebook 2 24.04.2019 Wednesday 43 G. Hernandez 893-5923 highlighter 10 27.04.2019 Saturday 44 R. Sawali 195-9356 mug 8 01.05.2019 Wednesday 45 P. Qui 679-8420 mug 1 01.05.2019 Wednesday 46 Q. Ronaldo 682-4210 mug 3 02.05.2019 Thursday 47 F. Gugenheim 806-4615 mug 3 02.05.2019 Thursday 48 E. Frankfort 456-1956 mug 7 03.05.2019 Friday 49 J. Kingston 737-9504 mug 7 04.05.2019 Saturday 50 P. Qui 679-8420 notebook 3 05.05.2019 Sunday 51 Y. Zeenith 171-9949 mug 8 05.05.2019 Sunday 52 H. Ignacio 500-2539 mug 8 05.05.2019 Sunday 53 F. Gugenheim 806-4615 tshirt 8 06.05.2019 Monday 54 V. Wu 150-4751 pencils 3 06.05.2019 Monday 55 T. Unigo 311-4039 highlighter 2 08.05.2019 Wednesday 56 K. Livingston 389-6876 pencils 4 09.05.2019 Thursday 57 Y. Zeenith 171-9949 9 806-4615 2 7 890-2767 500-2539 134-8405 2 5 5 7 682-4210 183-7361 150-4751 893-5923 679-8420 9 4 8 347-6423 3 150-4751 mug notebook notebook mug tshirt pencils pencils pencils mug mug notebook tshirt mug notebook highlighter pencils pencils highlighter pencils pencils notebook mug tshirt 58 F. Gugenheim 59 N. O'Donnal 60 H. Ignacio 61 D. Everest 62 Q. Ronaldo 63 O. Peterson 64 V. Wu 65 G. Hernandez 66 P. Qui 67 S. Tavshan 68 V. Wu 69 C. Dovestail 70 B. Chesterfield 71 L. Morris 72 S. Tavshan 73 A. Baker 74 R. Sawali 75 O. Peterson 76 A. Baker 77 R. Sawali 78 E. Frankfort 79 M. Neilson 5 09.05.2019 Thursday 09.05.2019 Thursday 11.05.2019 Saturday 12.05.2019 Sunday 12.05.2019 Sunday 13.05.2019 Monday 13.05.2019 Monday 14.05.2019 Tuesday 14.05.2019 Tuesday 16.05.2019 Thursday 16.05.2019 Thursday 17.05.2019 Friday 17.05.2019 Friday 18.05.2019 Saturday 18.05.2019 Saturday 20.05.2019 Monday 24.05.2019 Friday 25.05.2019 Saturday 25.05.2019 Saturday 28.05.2019 Tuesday 30.05.2019 Thursday 31.05.2019 Friday 31.05.2019 Friday 974-1735 6 1 1 898-1107 365-2883 347-6423 728-5051 195-9356 7 11 5 183-7361 7 3 2 728-5051 195-9356 456-1956 644-4430 2 7 80
