excel question
Bayhawks22
Week1ExcelAssignment3.xlsxInstructions
| INSTRUCTIONS | ||
| COFFEE SALES | ||
| Insert a column in between Month and Product and label it "Year" | ||
| Enter "2019" in Year column for all months March-December, enter "2020" for January and February | ||
| Change the format of the Monthly Sales $ to Currency without "$" and 2 decimals | ||
| Make the Title Row BOLD | ||
| Right justify all columns and adjust widths to show full cell contents | ||
| Make nine new tabs making copies of this data set and label Coffee Sales 1, Coffee Sales 2, Coffee Sales 3….Coffee Sales 9 | ||
| Coffee Sales 1 | ||
| Add Total Row to bottom of data set and use SUM function to find Total Sales $ | ||
| Sort the data by State and then by Month | ||
| Coffee Sales 2 | ||
| Create a new Column after Monthly Sales $ called "Profit" | ||
| Create a formula in the "Profit" column that assumes Profit=50% of Sales | ||
| Copy and paste the formula down the data set | ||
| Coffee Sales 3 | ||
| Convert data to Table | ||
| Add Total Row to Table | ||
| Use the Filter Button to filter by State and include just California | ||
| Rename Table to "Coffee_Sales_California" | ||
| Coffee Sales 4 | ||
| Calculate the following: | Total Sales for Oregon | |
| Use the Sort or Filter Method | ||
| Use the Sumif Formula | ||
| Use the Sumif Formula with an INPUT cell | ||
| Coffee Sales 5 | ||
| Calculate the following: | Total Sales for Kona Koast | |
| Use the Sort or Filter Method | ||
| Use the Sumif Formula | ||
| Use the Sumif Formula with an INPUT cell | ||
| Coffee Sales 6 | ||
| Calculate the following: | Number of times sales were over $500 | |
| Use the Sort or Filter Method | ||
| Use the Countif Formula | ||
| Use theCountif Formula with an INPUT cell | ||
| Coffee Sales 7 | ||
| Calculate the following: | Total Sales of Volcano Blend in California | |
| Use Sumif Formula with two input cells | ||
| Tip: Use filter to check your answer | ||
| Coffee Sales 8 | ||
| Add another Column titled "Commissions" | ||
| Use If/then formula to calculate Commissions, assuming each sale above $100 has a 10% commission | ||
| Format Commission column to Currency with 0 decimals | ||
| Add Total Row to determine Total Commissions | ||
| Coffee Sales 9 | ||
| Add another Column titled "Commissions" | ||
| Calculate the Commissions based on the following commission schedule: | ||
| Sales | Commission | |
| $0-99 | 0% | |
| $100-499 | 10% | |
| $500 + | 20% | |
| Tip: Remember that the order of the conditions is KEY | ||
| Format Commission column to Currency with 0 decimals | ||
| Add Total Row to determine Total Commissions | ||
| Save File as "Last Name, First Name HW#1" and upload to Blackboard |
Coffee Sales
| Month | Product | State | Monthly Sales $ |
| August | Best Blend of Arabia | California | 4.3697147834 |
| June | Best Blend of Arabia | Oregon | 40.8575517136 |
| April | Best Blend of Arabia | Oregon | 42.515052054 |
| January | Best Blend of Arabia | Oregon | 64.5027180953 |
| March | Best Blend of Arabia | California | 146.1272049129 |
| March | Best Blend of Arabia | Oregon | 227.0842937591 |
| July | Best Blend of Arabia | Washington | 276.5120188534 |
| March | Best Blend of Arabia | Washington | 320.7277321484 |
| March | Best Blend of Arabia | Oregon | 324.504618245 |
| September | Best Blend of Arabia | California | 360.4272275997 |
| March | Best Blend of Arabia | Washington | 426.4487626972 |
| May | Best Blend of Arabia | Washington | 428.5482850836 |
| April | Best Blend of Arabia | Washington | 450.0324134249 |
| March | Best Blend of Arabia | California | 457.0765385572 |
| March | Best Blend of Arabia | California | 462.0826339101 |
| August | Best Blend of Arabia | Washington | 532.9133588326 |
| February | Best Blend of Arabia | Washington | 541.3377662265 |
| April | Best Blend of Arabia | California | 567.3847293162 |
| March | Best Blend of Arabia | Washington | 602.0356351734 |
| August | Best Blend of Arabia | Oregon | 674.287064452 |
| July | Best Blend of Arabia | California | 676.7355755664 |
| March | Best Blend of Arabia | Washington | 680.7120178879 |
| May | Best Blend of Arabia | Oregon | 685.8418290935 |
| January | Best Blend of Arabia | California | 709.8202183686 |
| September | Best Blend of Arabia | Washington | 743.1324919188 |
| March | Best Blend of Arabia | Oregon | 744.9669343105 |
| June | Best Blend of Arabia | California | 763.2230001421 |
| June | Best Blend of Arabia | Washington | 810.140676048 |
| May | Best Blend of Arabia | California | 816.8391454699 |
| March | Best Blend of Arabia | Washington | 832.2841626876 |
| March | Best Blend of Arabia | California | 841.9905908856 |
| March | Best Blend of Arabia | Oregon | 864.9003148848 |
| September | Best Blend of Arabia | Oregon | 869.2545755396 |
| March | Best Blend of Arabia | California | 883.4350407212 |
| July | Best Blend of Arabia | Oregon | 920.6084807286 |
| February | Best Blend of Arabia | California | 962.6324033683 |
| February | Best Blend of Arabia | Oregon | 962.9114933097 |
| March | Best Blend of Arabia | Oregon | 997.6639271595 |
| March | Breakfast Blastoff | California | 20.4022015987 |
| February | Breakfast Blastoff | Washington | 99.9946253972 |
| September | Breakfast Blastoff | California | 120.7311623327 |
| March | Breakfast Blastoff | California | 150.332761579 |
| March | Breakfast Blastoff | California | 171.2718988131 |
| April | Breakfast Blastoff | Oregon | 182.1466470711 |
| May | Breakfast Blastoff | California | 191.4476455718 |
| April | Breakfast Blastoff | California | 200.1425104158 |
| May | Breakfast Blastoff | Washington | 222.3426947137 |
| March | Breakfast Blastoff | Oregon | 238.3433726798 |
| March | Breakfast Blastoff | Oregon | 262.0642041831 |
| March | Breakfast Blastoff | Washington | 272.5509030079 |
| August | Breakfast Blastoff | Washington | 285.8465805721 |
| July | Breakfast Blastoff | Oregon | 311.8537917573 |
| January | Breakfast Blastoff | Oregon | 314.9429085629 |
| June | Breakfast Blastoff | Oregon | 368.2515321078 |
| July | Breakfast Blastoff | Washington | 403.9534673376 |
| April | Breakfast Blastoff | Washington | 409.1449789053 |
| March | Breakfast Blastoff | Oregon | 415.5438203884 |
| May | Breakfast Blastoff | Oregon | 448.229420745 |
| September | Breakfast Blastoff | Washington | 454.8676103506 |
| March | Breakfast Blastoff | Washington | 456.8565914401 |
| June | Breakfast Blastoff | California | 517.7701030772 |
| June | Breakfast Blastoff | Washington | 523.5640131848 |
| July | Breakfast Blastoff | California | 556.0830370871 |
| March | Breakfast Blastoff | Washington | 588.2881042541 |
| March | Breakfast Blastoff | California | 598.7342811495 |
| February | Breakfast Blastoff | Oregon | 603.6417931766 |
| March | Breakfast Blastoff | Oregon | 640.3219076494 |
| September | Breakfast Blastoff | Oregon | 681.5284735187 |
| March | Breakfast Blastoff | Washington | 755.6681051817 |
| March | Breakfast Blastoff | Washington | 827.7346808527 |
| March | Breakfast Blastoff | Oregon | 869.8767321535 |
| February | Breakfast Blastoff | Washington | 918.0810295558 |
| August | Breakfast Blastoff | California | 936.1409090264 |
| August | Breakfast Blastoff | Oregon | 956.1829421443 |
| March | Breakfast Blastoff | California | 984.3878827514 |
| May | Breakfast Blastoff Decaf | Washington | 22.881465623 |
| May | Breakfast Blastoff Decaf | Oregon | 51.2154760006 |
| July | Breakfast Blastoff Decaf | California | 96.3206644652 |
| February | Breakfast Blastoff Decaf | Oregon | 128.7191072785 |
| June | Breakfast Blastoff Decaf | Oregon | 164.9081064785 |
| March | Breakfast Blastoff Decaf | Oregon | 187.8402434 |
| August | Breakfast Blastoff Decaf | Oregon | 207.6375987522 |
| August | Breakfast Blastoff Decaf | California | 286.2816240038 |
| March | Breakfast Blastoff Decaf | Washington | 391.6430291389 |
| April | Breakfast Blastoff Decaf | Oregon | 445.5661773099 |
| July | Breakfast Blastoff Decaf | Oregon | 450.1186101261 |
| January | Breakfast Blastoff Decaf | Oregon | 451.5056376245 |
| September | Breakfast Blastoff Decaf | Washington | 457.1274544033 |
| March | Breakfast Blastoff Decaf | Washington | 467.6670985216 |
| February | Breakfast Blastoff Decaf | Washington | 472.5384665188 |
| March | Breakfast Blastoff Decaf | Oregon | 496.3650588613 |
| March | Breakfast Blastoff Decaf | Washington | 550.1832035302 |
| March | Breakfast Blastoff Decaf | California | 580.9733225852 |
| September | Breakfast Blastoff Decaf | California | 596.589144845 |
| June | Breakfast Blastoff Decaf | Washington | 622.8074492313 |
| March | Breakfast Blastoff Decaf | California | 623.3258947944 |
| April | Breakfast Blastoff Decaf | California | 631.1434316131 |
| August | Breakfast Blastoff Decaf | Washington | 666.7488562088 |
| September | Breakfast Blastoff Decaf | Oregon | 668.9701817784 |
| June | Breakfast Blastoff Decaf | California | 709.9092846866 |
| March | Breakfast Blastoff Decaf | California | 784.8338200002 |
| April | Breakfast Blastoff Decaf | Washington | 805.9552699986 |
| March | Breakfast Blastoff Decaf | Oregon | 810.1474810874 |
| March | Breakfast Blastoff Decaf | California | 820.2142309245 |
| March | Breakfast Blastoff Decaf | Oregon | 840.0620399489 |
| February | Breakfast Blastoff Decaf | Washington | 844.9262109452 |
| March | Breakfast Blastoff Decaf | Oregon | 879.5337104039 |
| March | Breakfast Blastoff Decaf | Washington | 884.6780735882 |
| July | Breakfast Blastoff Decaf | Washington | 892.3636660908 |
| March | Breakfast Blastoff Decaf | Washington | 935.7097891628 |
| March | Breakfast Blastoff Decaf | California | 967.7030517192 |
| May | Breakfast Blastoff Decaf | California | 979.2122970533 |
| September | Kona Koast | Oregon | 37.0241235035 |
| May | Kona Koast | Oregon | 58.844813239 |
| March | Kona Koast | Oregon | 79.7825747327 |
| March | Kona Koast | Washington | 149.0957786773 |
| August | Kona Koast | Washington | 194.3963305612 |
| March | Kona Koast | Washington | 197.7529887007 |
| September | Kona Koast | California | 263.2927293063 |
| May | Kona Koast | California | 299.5187201936 |
| March | Kona Koast | Washington | 305.394502874 |
| July | Kona Koast | Washington | 320.188340378 |
| June | Kona Koast | Washington | 336.835481717 |
| March | Kona Koast | California | 419.9077521261 |
| September | Kona Koast | Washington | 427.8322626475 |
| May | Kona Koast | Washington | 473.7029942296 |
| August | Kona Koast | California | 484.7732529618 |
| March | Kona Koast | Washington | 506.5323433766 |
| June | Kona Koast | Oregon | 512.2336113721 |
| January | Kona Koast | California | 512.9099166015 |
| March | Kona Koast | Oregon | 521.451537829 |
| February | Kona Koast | Washington | 584.5921218122 |
| April | Kona Koast | Oregon | 611.7401336831 |
| June | Kona Koast | California | 639.8894356983 |
| March | Kona Koast | California | 677.5164619669 |
| March | Kona Koast | Oregon | 701.4846584913 |
| March | Kona Koast | Washington | 706.568262696 |
| March | Kona Koast | California | 713.5598566231 |
| February | Kona Koast | California | 715.9429408231 |
| April | Kona Koast | California | 739.0388799973 |
| August | Kona Koast | Oregon | 774.5906149511 |
| April | Kona Koast | Washington | 795.2385805357 |
| March | Kona Koast | Oregon | 801.3791282234 |
| February | Kona Koast | Oregon | 852.4314715968 |
| March | Kona Koast | California | 883.0351999747 |
| March | Kona Koast | Oregon | 946.8314156504 |
| July | Kona Koast | Oregon | 958.5277499154 |
| July | Kona Koast | California | 962.4977959357 |
| March | Kona Koast | California | 991.3611496375 |
| June | Turkish Delight Roast | California | 13.0513722243 |
| March | Turkish Delight Roast | Washington | 14.848169069 |
| May | Turkish Delight Roast | Oregon | 15.062009907 |
| June | Turkish Delight Roast | Washington | 52.8652793468 |
| September | Turkish Delight Roast | California | 70.0361150473 |
| March | Turkish Delight Roast | Washington | 88.5107612596 |
| March | Turkish Delight Roast | Washington | 90.3767056373 |
| March | Turkish Delight Roast | California | 99.3727914226 |
| August | Turkish Delight Roast | Oregon | 99.5117763797 |
| March | Turkish Delight Roast | Oregon | 175.7135804281 |
| June | Turkish Delight Roast | Oregon | 190.5784140841 |
| January | Turkish Delight Roast | Oregon | 242.9348471356 |
| July | Turkish Delight Roast | Washington | 310.0014821806 |
| March | Turkish Delight Roast | Oregon | 344.6013288148 |
| May | Turkish Delight Roast | California | 345.6944882422 |
| January | Turkish Delight Roast | California | 352.3410667458 |
| March | Turkish Delight Roast | California | 361.3362322858 |
| March | Turkish Delight Roast | Oregon | 401.5418985187 |
| September | Turkish Delight Roast | Oregon | 456.4042440141 |
| February | Turkish Delight Roast | Oregon | 468.8323891817 |
| March | Turkish Delight Roast | Oregon | 551.4726565501 |
| March | Turkish Delight Roast | Washington | 569.8586149496 |
| February | Turkish Delight Roast | California | 598.9710512042 |
| February | Turkish Delight Roast | Washington | 621.6470466322 |
| April | Turkish Delight Roast | California | 626.8267917505 |
| September | Turkish Delight Roast | Washington | 644.2435724058 |
| July | Turkish Delight Roast | California | 649.988070853 |
| March | Turkish Delight Roast | California | 681.1981303006 |
| March | Turkish Delight Roast | Oregon | 696.0089795083 |
| March | Turkish Delight Roast | California | 741.9587131721 |
| May | Turkish Delight Roast | Washington | 786.4182911021 |
| March | Turkish Delight Roast | Washington | 787.4837689744 |
| August | Turkish Delight Roast | Washington | 812.5261214835 |
| April | Turkish Delight Roast | Washington | 880.2788100479 |
| April | Turkish Delight Roast | Oregon | 920.0433695227 |
| March | Turkish Delight Roast | California | 942.2516023643 |
| July | Turkish Delight Roast | Oregon | 951.4455588082 |
| August | Turkish Delight Roast | California | 962.1953623874 |
| March | Volcano Blend | Oregon | 20.8015217545 |
| May | Volcano Blend | Oregon | 23.9241355971 |
| March | Volcano Blend | Washington | 43.4548506877 |
| August | Volcano Blend | California | 63.2824066389 |
| March | Volcano Blend | California | 116.3117077372 |
| July | Volcano Blend | Oregon | 174.7979259766 |
| March | Volcano Blend | California | 200.4342586218 |
| July | Volcano Blend | California | 205.5419654492 |
| May | Volcano Blend | Washington | 211.7796074893 |
| February | Volcano Blend | Washington | 256.706564284 |
| March | Volcano Blend | California | 300.4909396111 |
| February | Volcano Blend | California | 352.9666484461 |
| August | Volcano Blend | Washington | 466.7115749809 |
| March | Volcano Blend | Washington | 491.4245139483 |
| April | Volcano Blend | Oregon | 520.4027526012 |
| March | Volcano Blend | Washington | 573.7675915219 |
| May | Volcano Blend | California | 599.25649266 |
| March | Volcano Blend | Oregon | 695.8131400643 |
| March | Volcano Blend | Oregon | 704.54322819 |
| February | Volcano Blend | Oregon | 733.1482051436 |
| April | Volcano Blend | Washington | 764.5823101395 |
| July | Volcano Blend | Washington | 785.1801219646 |
| June | Volcano Blend | California | 807.2980033738 |
| March | Volcano Blend | Washington | 815.6744944639 |
| August | Volcano Blend | Oregon | 829.8903024726 |
| March | Volcano Blend | California | 830.3051286026 |
| March | Volcano Blend | Oregon | 849.7916650743 |
| September | Volcano Blend | Oregon | 857.2578476787 |
| September | Volcano Blend | California | 862.4547010752 |
| September | Volcano Blend | Washington | 880.2659670206 |
| January | Volcano Blend | California | 894.3408599117 |
| April | Volcano Blend | California | 919.119001777 |
| March | Volcano Blend | Washington | 941.8687442798 |
| June | Volcano Blend | Oregon | 948.2711954667 |
| June | Volcano Blend | Washington | 966.4267165924 |
| March | Volcano Blend | Oregon | 993.0007594867 |
| March | Volcano Blend | California | 995.1754806544 |
