Order no.

Date Driver's name Item Number of items Transport

100001 2/1/2013 John May TV 25 truck 4
100002 2/1/2013 Peter White washing machine 30 truck 3
100003 2/2/2013 Carl Nowak washing machine 15 truck 3
100004 2/3/2013 Peter White TV 32 truck 4
100005 2/3/2013 George Ramsay refrigerator 25 truck 3
100006 2/3/2013 Carl Nowak washing machine 18 truck 1
100007 2/3/2013 John May refrigerator 15 truck 2
100008 2/4/2013 Carl Nowak refrigerator 25 truck 3
100009 2/4/2013 Peter White TV 30 truck 1
100010 2/4/2013 George Ramsay refrigerator 15 truck 2
100011 2/4/2013 Mertl Pavel microwave 25 truck 3
100012 2/4/2013 John May washing machine 14 truck 4
100013 2/5/2013 John May washing machine 25 airplane
100014 2/5/2013 Carl Nowak TV 30 truck 4
100015 2/5/2013 George Ramsay microwave 15 truck 3
100016 2/5/2013 Peter White TV 15 truck 1
100017 2/6/2013 John May microwave 25 truck 1
100018 2/7/2013 John May TV 30 truck 4
100019 2/8/2013 George Ramsay washing machine 13 truck 3
100020 2/8/2013 Peter White refrigerator 25 truck 2
100021 2/8/2013 Carl Nowak microwave 30 truck 1
100022 2/8/2013 Peter White washing machine 15 airplane
100023 2/8/2013 John May microwave 25 truck 4
100024 2/9/2013 George Ramsay washing machine 34 truck 3
 Destination Questions
Boston number of orders in Boston :
NY number of microwave orders :
Philadelphia number of journeys with truck 3:
NY number of Peter White journeys:
Boston how many times are no. of items less than 20:
Baltimore
Philadelphia
Baltimore sum of refrigerator items:
Pittsburgh sum of washing machine items:
NY sum of items transported by truck 4:
Philadelphia sum of items transported by trucks:
NY
Baltimore
Philadelphia number of microvawe orders in Boston :
Boston number of Peter White journeys with truck 1:
Pittsburgh number of orders in Boston after 2/3/2013:
NY number of orders between 2/3/2013 and 2/6/2013:
Philadelphia
Baltimore sum of microwaves transported to NY:
Philadelphia sum of items transported to Pittsburgh by truck 1:
Pittsburgh sum of items ordered between 2/3/2013 and 2/6/2013:
NY
Boston
Baltimore sum of items transported to NY, Baltimore and Philadelphia:
Result
 Service How many times total Total price How many times by cash
Shaving
Washing and combing
Dyeing
Meeting hairstyles

Stylist name How many times total Total price How many times Shaving
Jane
Martha
Alex

Summary May 2013

Date Service Stylist name Payment
5/1/2013 Shaving Jane cash
5/1/2013 Shaving Martha credit card
5/1/2013 Shaving Lucy cash
5/1/2013 Washing and combing Alex cash
5/1/2013 Dyeing Alex credit card
5/1/2013 All service Rachel cash
5/1/2013 Meeting hairstyles Lucy cash
5/1/2013 Shaving Ashley cash
5/2/2013 Shaving Ashley credit card
5/2/2013 Shaving Sandy credit card
5/2/2013 Kids Sandy cash
5/2/2013 Shaving Ashley cash
5/2/2013 Shaving Lucy cash
5/2/2013 Shaving Jane credit card
5/2/2013 Washing and combing Sandy cash
5/2/2013 Dyeing Rachel cash
5/2/2013 All service Alex credit card
5/2/2013 Meeting hairstyles Jane cash
5/2/2013 Washing and combing Martha cash
5/2/2013 Shaving Sandy cash
5/2/2013 Shaving Jane cash
5/2/2013 Kids Ashley cash
5/3/2013 Kids Sandy credit card
5/3/2013 Kids Lucy cash
5/3/2013 Washing and combing Martha cash
5/3/2013 Dyeing Lucy credit card
5/3/2013 All service Rachel cash
5/3/2013 Meeting hairstyles Martha cash
5/3/2013 Washing and combing Jane credit card
5/3/2013 Shaving Rachel cash
5/4/2013 Shaving Martha cash
5/4/2013 Kids Alex cash
5/4/2013 Kids Sandy credit card
5/4/2013 Meeting hairstyles Sandy credit card
5/4/2013 Kids Jane credit card
5/4/2013 Kids Martha cash
5/4/2013 Kids Rachel cash
5/5/2013 Meeting hairstyles Sandy cash
5/5/2013 Meeting hairstyles Alex credit card
5/5/2013 Shaving Martha cash
5/5/2013 Shaving Martha cash
5/5/2013 Washing and combing Rachel cash
5/5/2013 Washing and combing Rachel credit card
5/5/2013 Meeting hairstyles Rachel cash
5/5/2013 Washing and combing Lucy cash
5/5/2013 Dyeing Sandy cash
5/5/2013 All service Sandy cash
5/5/2013 Meeting hairstyles Martha cash
5/5/2013 Shaving Ashley cash
5/5/2013 Shaving Rachel cash
5/6/2013 Meeting hairstyles Rachel cash
5/6/2013 Shaving Sandy credit card
5/6/2013 Washing and combing Lucy cash
5/6/2013 Meeting hairstyles Alex cash
5/6/2013 Washing and combing Rachel cash
5/6/2013 Dyeing Ashley cash
5/6/2013 All service Rachel cash
5/6/2013 Meeting hairstyles Sandy cash
5/6/2013 Washing and combing Alex cash
5/7/2013 Meeting hairstyles Sandy credit card
5/7/2013 Shaving Rachel cash
5/7/2013 Washing and combing Sandy cash
5/7/2013 Washing and combing Martha cash
5/7/2013 Shaving Rachel cash
5/7/2013 Shaving Ashley cash
5/8/2013 Dyeing Lucy cash
5/8/2013 All service Rachel credit card
5/8/2013 Shaving Lucy credit card
5/8/2013 Shaving Rachel credit card
5/8/2013 Meeting hairstyles Ashley cash
5/8/2013 Shaving Alex cash
5/8/2013 Washing and combing Rachel cash
5/8/2013 Washing and combing Rachel cash
5/8/2013 Shaving Rachel cash
5/8/2013 Meeting hairstyles Alex credit card
5/8/2013 Shaving Sandy cash
5/8/2013 Meeting hairstyles Jane cash
5/8/2013 Shaving Ashley credit card
5/9/2013 Shaving Ashley credit card
5/9/2013 Shaving Rachel cash
5/9/2013 Shaving Rachel credit card
5/9/2013 Washing and combing Jane credit card
5/9/2013 Washing and combing Lucy cash
5/9/2013 Shaving Rachel cash
5/9/2013 Shaving Ashley cash
5/9/2013 Dyeing Rachel cash
5/9/2013 Shaving Martha credit card
5/9/2013 Dyeing Lucy cash
5/10/2013 Washing and combing Jane cash
5/10/2013 Shaving Rachel cash
5/10/2013 Shaving Rachel credit card
5/10/2013 Washing and combing Alex cash
5/10/2013 Meeting hairstyles Rachel credit card
5/10/2013 Meeting hairstyles Ashley cash
5/10/2013 Washing and combing Alex cash
5/10/2013 Dyeing Alex cash
5/10/2013 All service Ashley cash
5/10/2013 Meeting hairstyles Rachel credit card
5/10/2013 Washing and combing Jane credit card
5/11/2013 Shaving Sandy credit card
5/11/2013 Shaving Alex credit card
5/11/2013 Washing and combing Sandy credit card
5/11/2013 Shaving Martha cash
5/11/2013 Dyeing Ashley cash
5/11/2013 Dyeing Martha cash
5/11/2013 Dyeing Rachel cash
5/11/2013 Shaving Alex cash
5/11/2013 Meeting hairstyles Lucy cash
5/11/2013 Washing and combing Alex credit card
5/12/2013 Shaving Ashley credit card
5/12/2013 Shaving Ashley cash
5/12/2013 Dyeing Ashley cash
5/12/2013 Shaving Jane cash
5/12/2013 Shaving Ashley credit card
5/12/2013 Washing and combing Lucy cash
5/12/2013 Dyeing Jane credit card
5/12/2013 Shaving Jane credit card
5/13/2013 Shaving Lucy cash
5/13/2013 Dyeing Jane credit card
5/13/2013 Shaving Rachel cash
5/13/2013 Shaving Rachel cash
5/13/2013 Washing and combing Alex credit card
5/13/2013 Dyeing Martha cash
5/13/2013 Dyeing Rachel cash
5/13/2013 Dyeing Sandy cash
5/13/2013 Dyeing Lucy cash
5/13/2013 All service Sandy cash
5/13/2013 Dyeing Sandy cash
5/13/2013 All service Rachel credit card
5/13/2013 Shaving Alex credit card
5/13/2013 Shaving Martha cash
5/14/2013 Washing and combing Jane cash
5/14/2013 Dyeing Martha credit card
5/14/2013 Meeting hairstyles Martha credit card
5/14/2013 All service Lucy cash
5/14/2013 All service Lucy cash
5/14/2013 Dyeing Alex cash
5/14/2013 Dyeing Ashley cash
5/14/2013 All service Rachel cash
5/14/2013 Dyeing Martha cash
5/15/2013 Shaving Ashley credit card
5/15/2013 Washing and combing Alex credit card
5/15/2013 Washing and combing Rachel cash
5/15/2013 Meeting hairstyles Jane credit card
5/15/2013 Dyeing Alex cash
5/15/2013 Dyeing Rachel cash
5/15/2013 All service Martha cash
5/15/2013 All service Jane credit card
5/15/2013 Washing and combing Sandy credit card
5/16/2013 Shaving Sandy cash
5/16/2013 Shaving Ashley cash
5/16/2013 Dyeing Sandy credit card
5/16/2013 Dyeing Martha credit card
5/16/2013 Meeting hairstyles Ashley cash
5/16/2013 Washing and combing Sandy cash
5/16/2013 Dyeing Jane cash
5/16/2013 All service Ashley cash
5/16/2013 Meeting hairstyles Alex credit card
5/16/2013 Washing and combing Sandy cash
5/16/2013 Shaving Rachel cash
5/16/2013 Shaving Lucy credit card
5/16/2013 Washing and combing Rachel cash
5/17/2013 Dyeing Ashley cash
5/17/2013 Dyeing Sandy cash
5/17/2013 Washing and combing Rachel credit card
5/17/2013 Dyeing Sandy credit card
5/17/2013 All service Martha credit card
5/17/2013 Meeting hairstyles Rachel credit card
5/17/2013 Dyeing Rachel cash
5/17/2013 Shaving Sandy cash
5/18/2013 Shaving Lucy cash
5/18/2013 Shaving Ashley cash
5/18/2013 Shaving Jane credit card
5/18/2013 Dyeing Martha cash
5/18/2013 Shaving Sandy cash
5/18/2013 Shaving Lucy credit card
5/18/2013 Dyeing Martha credit card
5/19/2013 Shaving Sandy credit card
5/20/2013 Shaving Alex credit card
5/20/2013 Dyeing Sandy credit card
5/20/2013 Shaving Rachel credit card
5/21/2013 Shaving Ashley credit card
5/21/2013 Shaving Rachel cash
5/21/2013 Dyeing Rachel cash
5/22/2013 Meeting hairstyles Martha cash
5/23/2013 Dyeing Martha credit card
5/23/2013 Meeting hairstyles Alex cash
5/24/2013 Meeting hairstyles Rachel credit card
5/24/2013 Meeting hairstyles Rachel cash
5/24/2013 Meeting hairstyles Martha cash
5/24/2013 Washing and combing Lucy credit card
5/24/2013 Dyeing Jane cash
5/24/2013 Washing and combing Martha credit card
5/25/2013 Shaving Lucy credit card
5/25/2013 Dyeing Rachel credit card
5/25/2013 Washing and combing Sandy cash
5/25/2013 Washing and combing Lucy cash
5/26/2013 Shaving Rachel cash
5/26/2013 Shaving Martha cash
5/26/2013 Dyeing Sandy credit card
5/27/2013 All service Rachel cash
5/27/2013 Washing and combing Jane credit card
5/27/2013 Washing and combing Rachel cash
5/27/2013 All service Rachel cash
5/28/2013 Dyeing Sandy cash
5/28/2013 Meeting hairstyles Ashley cash
5/28/2013 Dyeing Rachel cash
5/28/2013 Meeting hairstyles Martha cash
5/28/2013 Meeting hairstyles Lucy cash
5/28/2013 Dyeing Jane cash
5/28/2013 Washing and combing Ashley cash
5/29/2013 Dyeing Jane cash
5/29/2013 Dyeing Lucy cash
5/30/2013 Washing and combing Lucy cash
5/30/2013 Dyeing Sandy credit card
5/30/2013 Dyeing Rachel credit card
5/30/2013 Shaving Jane credit card
5/30/2013 Dyeing Rachel cash
5/31/2013 Washing and combing Jane cash
5/31/2013 Washing and combing Lucy credit card
5/31/2013 Dyeing Martha cash
5/31/2013 Washing and combing Martha cash
5/31/2013 Shaving Sandy cash
5/31/2013 Shaving Rachel credit card
5/31/2013 Washing and combing Ashley cash
5/31/2013 Washing and combing Rachel credit card
How many times by credit card Total price by cash

How many times Kids Sum of price for Shaving between 5/10/2013 and 5/20/2013

Price
$ 7.00
$ 7.00
$ 7.00
$ 60.00
$ 33.00
$ 67.00
$ 33.00
$ 7.00
$ 7.00
$ 17.00
$ 3.00
$ 7.00
$ 7.00
$ 7.00
$ 60.00
$ 33.00
$ 67.00
$ 33.00
$ 23.00
$ 7.00
$ 17.00
$ 3.00
$ 3.00
$ 3.00
$ 60.00
$ 33.00
$ 67.00
$ 33.00
$ 23.00
$ 7.00
$ 17.00
$ 3.00
$ 3.00
$ 40.00
$ 3.00
$ 3.00
$ 3.00
$ 33.00
$ 33.00
$ 7.00
$ 17.00
$ 33.00
$ 40.00
$ 40.00
$ 60.00
$ 33.00
$ 67.00
$ 33.00
$ 7.00
$ 7.00
$ 33.00
$ 7.00
$ 40.00
$ 40.00
$ 60.00
$ 33.00
$ 67.00
$ 33.00
$ 23.00
$ 33.00
$ 17.00
$ 33.00
$ 40.00
$ 7.00
$ 7.00
$ 33.00
$ 67.00
$ 7.00
$ 7.00
$ 33.00
$ 17.00
$ 33.00
$ 40.00
$ 7.00
$ 33.00
$ 7.00
$ 33.00
$ 7.00
$ 7.00
$ 7.00
$ 17.00
$ 33.00
$ 40.00
$ 7.00
$ 7.00
$ 33.00
$ 7.00
$ 33.00
$ 23.00
$ 7.00
$ 17.00
$ 33.00
$ 33.00
$ 40.00
$ 60.00
$ 33.00
$ 67.00
$ 33.00
$ 23.00
$ 7.00
$ 17.00
$ 33.00
$ 7.00
$ 33.00
$ 33.00
$ 33.00
$ 7.00
$ 33.00
$ 23.00
$ 7.00
$ 17.00
$ 33.00
$ 7.00
$ 7.00
$ 60.00
$ 33.00
$ 7.00
$ 7.00
$ 33.00
$ 7.00
$ 7.00
$ 33.00
$ 33.00
$ 33.00
$ 33.00
$ 33.00
$ 67.00
$ 33.00
$ 67.00
$ 7.00
$ 17.00
$ 33.00
$ 33.00
$ 40.00
$ 67.00
$ 67.00
$ 33.00
$ 33.00
$ 67.00
$ 33.00
$ 17.00
$ 33.00
$ 40.00
$ 40.00
$ 33.00
$ 33.00
$ 67.00
$ 67.00
$ 23.00
$ 7.00
$ 17.00
$ 33.00
$ 33.00
$ 40.00
$ 60.00
$ 33.00
$ 67.00
$ 33.00
$ 23.00
$ 7.00
$ 17.00
$ 33.00
$ 33.00
$ 33.00
$ 60.00
$ 33.00
$ 67.00
$ 33.00
$ 33.00
$ 7.00
$ 17.00
$ 7.00
$ 17.00
$ 33.00
$ 7.00
$ 7.00
$ 33.00
$ 7.00
$ 7.00
$ 33.00
$ 7.00
$ 7.00
$ 7.00
$ 33.00
$ 40.00
$ 33.00
$ 40.00
$ 33.00
$ 33.00
$ 33.00
$ 60.00
$ 33.00
$ 60.00
$ 17.00
$ 33.00
$ 60.00
$ 60.00
$ 17.00
$ 17.00
$ 33.00
$ 67.00
$ 60.00
$ 60.00
$ 67.00
$ 33.00
$ 33.00
$ 33.00
$ 33.00
$ 33.00
$ 33.00
$ 40.00
$ 33.00
$ 33.00
$ 40.00
$ 33.00
$ 33.00
$ 17.00
$ 33.00
$ 40.00
$ 40.00
$ 33.00
$ 40.00
$ 17.00
$ 17.00
$ 40.00
$ 40.00
Question: Calculate the average region-wise units sold by quarter

Region Quarter Units

2 4 2357
4 1 2273
3 2 1780
3 4 2663
1 2 2216
4 4 3537
2 4 2487
3 2 4866
3 4 4115
1 2 4196
2 1 3851
4 4 4769
1 4 4485
3 4 1881
3 2 4592
3 2 1583
1 4 4802
2 4 4823
1 3 2596
3 1 3025
4 4 2811
3 3 3326
3 3 2408
2 3 3250
1 3 1579
3 1 2876
3 4 2409
1 4 3120
3 3 2167
1 1 2090
3 4 1700
2 4 3597
2 1 2644
1 3 4842
3 1 2761
3 3 3598
3 1 1870
1 2 4077
3 4 2542
4 1 3626
3 3 2911
3 4 3182
2 4 2836
2 3 3078
1 2 2934
2 3 3143
3 2 2118
4 3 3049
4 3 4542
3 4 4462
1 2 4885
1 1 4515
4 1 2978
3 3 3175
2 3 4574
1 1 1789
1 1 4268
1 2 2049
2 4 4074
1 4 4690
3 4 3006
1 3 4230
4 2 3288
2 4 4965
3 1 4769
4 4 4292
4 3 4629
3 1 2451
4 4 1790
2 1 4056
3 1 4170
3 4 2133
2 3 3579
3 1 4269
4 1 2240
1 4 2487
3 1 4442
1 2 4645
3 4 2658
2 3 3549
3 2 1618
1 1 3915
3 3 4279
4 4 3251
2 4 1829
3 1 3930
1 3 3874
3 1 2845
4 3 4787
1 1 4366
4 4 1969
4 1 4244
4 3 2638
2 4 2592
1 2 2574
2 1 3066
2 4 2500
4 1 3011
4 2 4630
1 3 2136
sales_executive Jan Feb Mar Apr May Jun Jul
A 0.22 0.45 0.35 0.15 0.06 0.53 0.48
B 0.97 0.93 0.92 0.42 0.42 0.4 0.03
C 0.32 0.85 0.67 0.68 0.66 0.92 0.24
D 0.06 0.52 0.65 0.51 0.61 0.62 0.93
E 0.8 0.85 0.72 0.71 0.07 0.81 0.1
F 0.31 0.28 0.9 0.79 0.52 0.78 0.71
G 0.87 0.33 0.67 0.33 0.69 0.22 0.91
H 0.78 0.58 0.38 0.5 0.66 0.14 0.37
I 0.34 0.6 0.35 0.11 0.35 0.47 0.54

Using offset, pull the rating of the employee for a particular month, as entered in the cells below:

sales_executive:
month:
rating:
 Aug Sep Oct Nov Dec average_rating
0.46 0.01 0.46 0.24 0.57 0.33
0.02 0.88 0.09 0.28 0.58 0.5
0.29 0.19 0.59 0.72 0.09 0.52
0.29 0.63 0.41 0.05 0.9 0.52
0.01 0.24 0.1 0.63 0.8 0.49
0.32 0.11 0.73 0.94 0.27 0.56
0.2 0.56 0.27 0.01 0.61 0.47
0.85 0.48 0.64 0.69 0.82 0.57
0.01 0.77 0.93 0.16 0.99 0.47

the cells below:

Return the expected text by using left, right, mid, find and len functions. Do not fix length in the respective functi

[Link]. Word Expected result Result

1 Pink Floyd Pink
2 Led Zeppelin Zeppelin
3 Alice in Chains in
4 The Beautiful South Beautiful South
fix length in the respective functions
Create an "Adjusted Region" column created from a vlookup from the table below on the sheet

Group Name Group Segment Revenue Expenses Net Income/Loss

Postcards Store Big 8249.32766981273 594.8782 7654.44942780478
Randles Roadshack Small 6557.87298322195 83.33939 6474.53359724986
Examineers Inc Medium 6180.03574719812 551.2668 5628.76896680659
Buttersworth Co Small 9915.45431122505 336.8577 9578.59663278785
FASST Trax Small 4047.66917808814 481.0083 3566.66087719586
Hula Hoopers Big 4797.0875045761 962.3551 3834.73241023183
Hehehe Medium 6537.80010101674 931.9697 5605.83037623301
Marleyville Tea Small 3151.42277537038 848.2135 2303.20925835259
Creepy Lada Medium 9504.7818270498 827.5632 8677.21860260639
Jokes R Us Big 9378.14046995849 566.7977 8811.34281635519
Petie's Palace Small 6772.1330424373 436.88 6335.25305882547
Ramone's Castle Big 48922666.9866995 41584267 7338400.04800493

There are some groups that had Expenses this year, but they didn't have any Revenue. We don’t want those grou
that tells me if the entry doesnt have any revenue
Create a new column entitled "Groups with 2008 Revenue." Do a lookup from the "mapping_table" tab. Look up

Group Name Group Segment Physician Expense Region Adjusted Region

Postcards Store Big 6186.99575235955 N 1-North
Randles Roadshack Small 4918.40473741646 S 2-South
Examineers Inc Medium 4635.02681039859 E 3-East
Buttersworth Co Small 7436.59073341879 W 4-West
FASST Trax Small 3035.75188356611 SE 2-South
Hula Hoopers Big 3597.81562843207 NE 1-North
Hehehe Big 4903.35007576256 N 1-North
Marleyville Tea Small 2363.56708152778 S 2-South
Creepy Lada Medium 7128.58637028735 E 3-East
Jokes R Us Big 7033.60535246887 W 4-West
Petie's Palace Small 5079.09978182798 SE 2-South
Ramone's Castle Big 36692000.2400247 NE 1-North
n the sheet

Region
N
S
E
W
SE
NE
N
S
E
W
SE
NE

ue. We don’t want those groups included. Create a new column in the below table

mapping_table" tab. Look up the Group Name and return the Group Name.
Depending on the shipping method and the region, return the rate of shipment

Shipping Method - Standard

Region Rail Truck Plane Ship Shipping method
NW $ 0.13 $ 0.14 $ 1.53 $ 0.23 Region
West $ 0.21 $ 0.15 $ 2.03 $ 0.25 Rate
SW $ 0.27 $ 0.17 $ 2.13 $ 0.23
MidWest $ 0.31 $ 0.19 $ 2.28 $ 0.23
East $ 0.48 $ 0.43 $ 3.53 $ 0.38