excel question

profileBayhawks22
Week1ExcelAssignment3.xlsx

Instructions

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