MGCR 331- XlookUp Exercise

Q1 - Return the price of the product. Product ID Price Price Product ID A134 $3.50 $3.50 A134 B242 $4.20 $4.20 B242 X212 $4.80 $4.80 X212 C413 $5.00 $5.00 C413 B2211 $5.20 $5.20 B2211 Product ID Price Product ID Price B2211 $ 5.20 B242 $ 4.20 A134 $ 3.50 A134 $ 3.50

Q2 - You are thinking of advertising your products on a sports telecast. As you buy more ads, the pri Return the price per ad for the number of ads bought following these rules: If you buy 1 to 5 ads, the price is $12,000 for each ad If you buy between 6 and 10 ads, the price is $11,000 If you buy between 11 and 20, the price is $10,000 If you buy more than 20, the price is $9,000 Lower bound Upper bound Price Ads bought Price per ad 1 5 12000 3 $ 12,000.00 6 10 11000 7 #NAME? 11 20 10000 13 #NAME? 20 100 9000 22 #NAME?

Q3 - You have a list with the number of computer sold by each salesperson and their store. 1. Return the values for the person identified in the table below. Display this message if the name is not fo Units Sold Name Store 79 John Laval 78 Jack Montreal 93 Jill Longueuil 97 Erica Montreal 76 Tom Montreal 97 Mary Laval 92 Erica Montreal 66 Tammy Longueuil 89 Riggins Laval Person Units Sold Store Erica 97 Montreal 2. Return the value of the last sale for Erica Person Last # Units Sold Erica 92

ound: "Name not found".

1. What is the overall revenue? 2. What is the revenue for each group ? 3. What is the average revenues for each product in each store? 4. What is the total of units for the milk group? Use a slicer to answer this question 5. What are the total units and revenues for each store for each year? Show the most recent year first

6. What is the sum of units for product starting with the letter S for each year? 7. What is the sum of units for each group within each store? Show the sum of units as a percentage of the store 8. What is the average price for each product? Don't show the grand total and rename the field "Avg Price"

Sum of Revenue $2,068,334 Group Sum of Revenue cereal $515,712 fruit $529,623 ice cream $510,917 milk $512,083 Total Result $2,068,334 Average of RevenStore Product east north south west Total Result apples $2,354 $2,264 $2,243 $2,325 $2,295 Ben and Jerry's $2,160 $2,123 $2,308 $2,022 $2,159 Breyers $2,421 $2,368 $2,298 $2,051 $2,278 Cheerios $2,019 $2,216 $2,269 $2,306 $2,190 cherries $2,218 $2,271 $2,559 $2,045 $2,289 chocolate $2,214 $2,337 $2,208 $2,010 $2,171 Edies $2,282 $2,253 $2,312 $2,139 $2,253 grapes $1,831 $2,326 $2,306 $2,203 $2,200 low fat $2,265 $2,355 $2,099 $2,260 $2,260 plums $2,499 $2,198 $2,211 $2,314 $2,308 Raisin Bran $2,165 $2,217 $2,281 $2,183 $2,211 skim $2,212 $1,889 $2,118 $2,275 $2,130 Special K $2,205 $2,797 $2,242 $2,378 $2,390 whole $2,269 $2,348 $2,386 $2,006 $2,247 Total Result $2,234 $2,281 $2,275 $2,187 $2,243 Group Sum of Units milk 174072 Total Result 174072 Data Store Sum of Units Sum of Revenue Year east north south west east north 2017 55341 57687 55364 64769 $169,202 $175,084 This shape represents a slicer. Slicers are supported in Excel 2010 or later. If the shape was modified in an earlier version of Excel, or if the workbook was saved in Excel 2003 or earlier, the slicer cannot be used.

2016 51976 43562 68069 53131 $151,211 $129,084 2015 52271 55044 68722 67191 $155,402 $170,265 Total Result 159588 156293 192155 185091 $475,814 $474,433 Product Sum of Units apples 39881 Ben and Jerry's 38124 Breyers 70086 Cheerios 40957 cherries 41919 chocolate 36549 Edies 60223 Sum of Units grapes 44721 159588 low fat 55692 39179 plums 49252 38699 Raisin Bran 87568 31371 skim 38546 50339 Special K 46324 156293 whole 43285 43733 Total Result 693127 35631 ice cream 38545 milk 38384 south 192155 cereal 45714 fruit 48602 ice cream 59207 milk 38632 west 185091 cereal 46223 fruit 52841 ice cream 39310 milk 46717 Total Result 693127 Product (empty) apples Ben and Jerry's Breyers

Total Sum of Units Total Sum of Revenue south west $169,291 $188,819 233161 $702,396

$202,355 $155,069 216738 $637,720 $204,000 $198,552 243228 $728,219 $575,647 $542,440 693127 $2,068,334

Year Month Store Group Product Units Revenue 2017 August south milk low fat 805 $3,188 2017 March south ice cream Edies 992 $3,412 2017 January east milk skim 712 $1,808 2016 March north ice cream Edies 904 $2,260 2016 January south ice cream Edies 647 $2,077 2015 September west fruit plums 739 $1,707 2016 March east milk low fat 974 $2,182 2017 June north fruit apples 615 $1,894 2017 July west fruit cherries 714 $1,856 2016 May south cereal Special K 703 $1,554 2015 June west ice cream Edies 528 $2,064 2016 October east cereal Raisin Bran 644 $1,810 2015 June south fruit grapes 919 $2,196 2017 May west milk skim 767 $1,933 2017 June west cereal Raisin Bran 984 $1,988 2015 March south cereal Raisin Bran 744 $2,217 2017 September east ice cream Edies 693 $2,190 2016 October east milk chocolate 658 $1,895 2015 November east ice cream Breyers 878 $3,275 2015 May south ice cream Breyers 848 $3,282 2017 August south milk low fat 547 $1,247 2015 June west ice cream Edies 664 $1,514 2017 April south ice cream Edies 882 $2,717 2017 February west fruit plums 930 $2,409 2015 November east cereal Special K 735 $2,595 2017 July north ice cream Edies 557 $1,543 2017 July west ice cream Breyers 790 $2,457 2017 August south ice cream Breyers 730 $2,665 2016 December south fruit plums 815 $2,901 2015 August west fruit plums 727 $2,007 2016 October north cereal Cheerios 644 $1,713 2016 January south cereal Raisin Bran 939 $2,319 2015 June north cereal Raisin Bran 739 $1,545 2016 July north cereal Raisin Bran 866 $2,866 2016 January south fruit grapes 513 $1,395 2017 July west ice cream Edies 534 $1,244 2017 September east cereal Cheerios 587 $1,591 2017 October west fruit apples 770 $1,817 2016 March north milk low fat 536 $1,972 2015 September east fruit grapes 787 $1,968 2015 January west cereal Special K 687 $2,398 2015 August west milk low fat 687 $1,450 2017 March west cereal Special K 943 $1,886 2016 May south milk low fat 731 $2,485

2017 October east fruit apples 993 $3,227 2017 March west fruit plums 686 $1,996 2015 March south fruit apples 675 $2,457 2015 May east milk chocolate 598 $1,477 2016 February south cereal Raisin Bran 604 $1,963 2016 February south ice cream Breyers 693 $1,518 2016 February north milk skim 766 $1,647 2017 April west cereal Special K 509 $1,812 2015 April south fruit cherries 901 $2,234 2016 June east cereal Special K 625 $1,369 2017 May east milk whole 914 $3,519 2016 March east cereal Cheerios 768 $2,028 2015 April west fruit apples 681 $1,696 2017 August west ice cream Ben and Je 630 $2,186 2017 January north fruit plums 704 $2,471 2016 March north fruit cherries 806 $1,781 2016 April west fruit plums 673 $2,591 2017 May west milk low fat 920 $3,294 2017 October west ice cream Edies 651 $2,181 2015 June west cereal Raisin Bran 775 $1,860 2017 May south cereal Special K 583 $1,953 2016 December east cereal Cheerios 542 $2,060 2015 May east cereal Raisin Bran 952 $3,303 2015 January east milk chocolate 618 $2,330 2015 August west milk chocolate 573 $1,839 2015 August east cereal Raisin Bran 738 $2,406 2017 August east ice cream Edies 506 $1,553 2017 January east cereal Raisin Bran 554 $1,640 2016 February east milk whole 843 $1,821 2016 October east milk chocolate 628 $1,620 2016 April east cereal Special K 897 $1,839 2015 November east cereal Cheerios 937 $2,436 2017 November north ice cream Edies 653 $1,972 2015 February south milk whole 640 $1,574 2015 February north ice cream Edies 555 $1,482 2017 March west ice cream Breyers 958 $3,171 2015 March north ice cream Breyers 935 $3,703 2015 March north milk low fat 727 $1,774 2015 December east cereal Special K 820 $1,870 2015 February east cereal Raisin Bran 977 $2,355 2017 July south ice cream Breyers 759 $2,004 2016 January west milk low fat 601 $1,515 2015 March west ice cream Edies 759 $2,474 2015 June west fruit grapes 934 $2,783 2016 July south ice cream Breyers 766 $1,655

2017 May west cereal Raisin Bran 714 $2,135 2017 February west ice cream Ben and Je 689 $2,611 2015 August east milk whole 607 $1,876 2015 September west fruit apples 770 $2,972 2016 February south fruit cherries 928 $3,452 2016 March south fruit cherries 925 $2,424 2016 July south fruit plums 957 $2,326 2017 March north milk low fat 688 $1,699 2017 September north cereal Cheerios 560 $1,691 2015 August east milk whole 760 $1,900 2015 December south ice cream Edies 570 $2,006 2016 December north ice cream Ben and Je 633 $2,519 2016 July south milk low fat 527 $1,755 2017 January south milk whole 817 $2,737 2017 November south fruit grapes 718 $2,491 2015 April south ice cream Breyers 570 $1,773 2017 August west cereal Raisin Bran 672 $1,882 2016 March south fruit plums 701 $2,531 2017 June north ice cream Breyers 783 $2,177 2015 December south ice cream Edies 845 $3,143 2015 March south ice cream Breyers 646 $1,318 2015 November east fruit grapes 576 $1,912 2016 July north ice cream Breyers 583 $1,842 2015 October west fruit plums 692 $1,806 2017 May east fruit grapes 666 $1,399 2016 October east cereal Cheerios 642 $2,054 2016 April south cereal Raisin Bran 695 $2,168 2016 February south ice cream Edies 511 $1,732 2015 February east milk low fat 918 $2,469 2015 October west milk skim 716 $2,384 2015 March west fruit apples 783 $2,647 2017 November east ice cream Breyers 582 $1,938 2017 January north milk skim 649 $2,375 2015 July south cereal Raisin Bran 966 $3,574 2016 October west ice cream Ben and Je 771 $2,336 2017 January south cereal Cheerios 856 $2,174 2015 January west fruit apples 823 $2,074 2015 June south ice cream Ben and Je 877 $3,280 2017 January west ice cream Edies 666 $2,125 2015 September north fruit plums 560 $2,066 2015 December south milk chocolate 673 $2,302 2017 January west ice cream Edies 944 $2,926 2017 November east fruit apples 822 $3,132 2016 November south ice cream Breyers 872 $3,226 2017 September south ice cream Edies 711 $2,275

2015 April north milk whole 963 $3,110 2015 September west fruit apples 892 $3,176 2017 September north milk low fat 865 $2,707 2016 June west cereal Raisin Bran 959 $2,378 2016 January west cereal Raisin Bran 980 $3,146 2017 July west cereal Special K 876 $3,162 2017 April west milk skim 676 $1,406 2016 February east milk low fat 658 $1,750 2015 October north cereal Raisin Bran 647 $2,116 2015 October south fruit plums 717 $1,534 2016 July west ice cream Ben and Je 623 $1,620 2015 February north fruit grapes 903 $2,447 2017 August west fruit plums 885 $2,903 2017 February west fruit grapes 574 $1,739 2015 November south ice cream Breyers 509 $1,430 2015 March south fruit grapes 802 $2,647 2017 December north milk low fat 836 $2,667 2016 August north cereal Raisin Bran 737 $2,123 2015 March east ice cream Edies 864 $3,447 2016 December south ice cream Edies 896 $3,387 2015 December north cereal Special K 643 $2,508 2015 April west milk low fat 998 $2,116 2017 June south fruit plums 869 $3,015 2016 October south fruit apples 814 $2,947 2017 February east fruit apples 990 $3,366 2017 August west ice cream Breyers 647 $1,462 2015 March east fruit apples 610 $2,202 2015 January east cereal Raisin Bran 586 $1,905 2017 September south fruit apples 756 $2,276 2017 March west fruit grapes 817 $3,145 2017 March south cereal Raisin Bran 915 $3,221 2016 December east cereal Raisin Bran 912 $2,581 2017 March north milk low fat 655 $2,587 2015 December west cereal Raisin Bran 985 $3,507 2016 February south ice cream Ben and Je 896 $2,903 2017 October south cereal Special K 644 $1,668 2016 December east fruit plums 827 $2,390 2016 March west milk whole 678 $1,973 2016 September north cereal Cheerios 942 $3,759 2015 January east milk chocolate 918 $3,057 2017 December west cereal Raisin Bran 913 $2,547 2017 September north ice cream Edies 756 $2,094 2016 March south ice cream Breyers 927 $1,910 2016 January east fruit cherries 548 $2,110 2017 November west ice cream Breyers 793 $1,586