(i) Using the IF function, write a formula in cell L24 to determine if the number of hours rented for the given server is more than the average. Copy the formula from K25 to K41.

(j) Using the IF function, write a formula in cell M24 to determine if the number of hours rented for the given server is more than the average. Copy the formula to M25 to M41.

Screenshots attached below

thank you!
Transcribed Image Text:20 21 22 23 24 25 26 27 2 28 m 29 30 3 31 22 32 3 33 7 34 2 35 → 36 * 37 38 39 40 41 42 A Server ID Large B 1 Medium 2 Large 3 Large 4 Large 5 Large Large Server Size Server Type 6 Large F 7 Small teman 8 Small paman 9 Large 10 Mediu 10 Medium 11 Large 12 Small Ta 13 Small 14 Small 15 Mediu 15 Medium 16 Large 17 Medium 18 Large p Spot Spot On-Demand Spot Spot t с On-Demand On-Demand Spot Spot 31.JJIZU p t B On-Demand On-Demand Spot Spot On-Demand Spot On-Demand Spot On-Demand D 4070 Rented Date & Time 11/25/2020 2:00 11/25/2020 9:00 11/25/2020 17:00 11/25/2020 21:00 11/26/2020 9:00 EYENYEVEN V 11/26/2020 11:00 *** 11/26/2020 16:00 ini 11/26/2020 23:00 14/02/2006 11/27/2020 1:00 11/23/2030.05. 11/27/2020 23:00 inn inna 11/28/2020 1:00 ini 11/28/2020 2:00 11/28/2020 3:00 44/20/2020.6m 11/28/2020 9:00 11/28/2020 11:00 11/28/2020 16:00 11/28/2020 16:00 11/28/2020 16:00 E Returned Date & Time 11/26/2020 9:00 11/27/2020 1:00 11/30/2020 13:00 11/30/2020 13:00 11/30/2020 13:00 **1/2000 45.00 11/30/2020 13:00 44/20/2000 45.00 11/30/2020 13:00 44/20/2000 42.00 11/30/2020 13:00 44/20/2000 42.00 11/30/2020 13:00 44/20/2000 42.00 11/30/2020 13:00 44/20/2000 42.00 11/30/2020 13:00 44/20/2000 42.00 11/30/2020 13:00 44/20/2000 42.00 11/30/2020 13:00 11/20/2020 12-00 11/30/2020 13:00 11/30/2020 13:00 11/30/2020 13:00 11/30/2020 13:00 11/30/2020 13:00 F # of hours Rented Table 10 31 $ 40 $ 116 $ 112 $ 100 $ cale 98 $ → 93 S ada 86 S q 84 S 62 $ ¥ 60 $ Fala 59 $ Fata 58 S q 52 $ S 50 $ 45 $ 45 $ 45 $ G Rate per hour 0.66560 1.33120 1.33120 1.33120 433430 1.33120 ****** 1.33120 633300 0.33280 0.33300 0.33280 433430 1.33120 0.66560 ****** 1.33120 Barce 0.33280 0.33280 0.33280 O SESSO 0.66560 1.33120 0.66560 1.33120 H Percent Discount 30% $ 40% $ 0% $ 40% $ 40% $ 0% $ 0% $ 20% $ www. 40% $ ou 0% $ 0% $ 20% $ 20% $ 0% $ 30% $ 0% $ 30% $ 0% $ Discount Amount Rate per hour after discount 0.19968 $ 0.53248 S $ 0.53248 $ 0.53248 $ $ $ 0.06656 $ www. 0.53248 $ $ $ 0.06656 $ 0.06656 $ $ 0.19968 $ J $ 0.19968 $ $ K Total Cost 0.46592 $ 14.44 0.79872 $ 31.95 1.33120 $ 154.42 0.79872 $ 89.46 2003 79.87 www. 0.79872 $ **** 1.33120 $ 130.46 ****** 0.33280 $ 30.95 ** 0.26624 $ 22.90 三 **** 0.79872 $ ..... 67.09 :::: 41.27 0.66560 $ 0.00000 1.33120 $ 79.87 ****** 0.26624 $ 15.71 0.26624 $ 15.44 0.33280 $ 17.31 0.46592 $ 23.30 1.33120 $ 59.90 0.46592 $ 20.97 1.33120 $ 59.90 Is number of hours rented more than average? M Is cost more than the average cost? N o P Q R S
Transcribed Image Text:B с D E F G H I A 1 2020 cyber week (during the thanksgiving period) saw a spike in e-commerce sales throughout the USA. A2Z eCommerz LLC website and app also witnessed a surge in traffic. During the cyber week, the IT team rented some temporary servers. Two types of servers were rented, (i) On-demand servers and (ii) Spot servers. Spot servers are idle servers in the cloud service provider's inventory, and they come at a discounted rate. However, you can not use spot servers for the long term because when the provider's idle server inventory reduces, some of the spot servers may be terminated. Hence, there is a termination risk in using spot servers. However, on-demand servers can't be terminated by the provider. 17 2 3 The servers are available in different sizes, but the firm rented only Small, Medium, and Large servers. 4 Table 8 has the pricing and spot server discounts for the servers of various sizes. Table 10 has details on the servers which the firm rented. 5 (a) Write a formula in cell F24 to compute the # of hours the firm rented the server. The number of hours rented = (Returned Date & Time - Rented Date and Time) * 24. Copy the formula to F25 to 6 (b) Using VLOOKUP, write a formula in cell G24 to find the rate per hour from Table 8 for the given server size. Copy the formula to G25 to G41. 7 (c) Using IF and VLOOKUP functions, write a formula in cell H24 to find the spot server's discount rate. For on-demand servers, the discount rate is 0%. Copy the formula to H25 to H41. 8 (d) Write a formula in cell 124 to compute the discount amount on the server. Discount Amount = Percent Discount Rate per hour. Copy the formula to 125 to 141. 9 (e) Write a formula in cell J24 to compute the rate per hour after the discount. Rate per hour after discount = Rate per hour - Discount Amount. Copy the formula from J25 to J41. 10 (f) Write a formula in cell K24 to compute the Total cost for the given server. Total Cost = Rate per hour after discount * # of hours Rented. Copy the formula from K25 to K41. 11 (g) Write a formula in cell G17 to compute the average hours Rented. Average hours rented is the average of the # of hours Rented (F24:F41). 12 (h) Write a formula in cell G18 to calculate the average total cost. The average total cost is the average of the total cost (K24:K41). 13 (i) Using the IF function, write a formula in cell L24 to determine if the number of hours rented for the given server is more than the average. Copy the formula from K25 to K41. 14 (j) Using the IF function, write a formula in cell M24 to determine if the number of hours rented for the given server is more than the average. Copy the formula to M25 to M41. 15 16 18 19 20 21 Server Size 1 Small 2 Medium 3 Large Table 8 On-Demand Instance Hourly Rate $0.33280 $0.66560 $1.33120 Discount for Spot Servers 20% 30% 40% Table 9 Average Hours Rented Average Total Cost $68.66667 K 53.07 M N 0 P Q F

Question

(i) Using the IF function, write a formula in cell L24 to determine if the number of hours rented for the given server is more than the average. Copy the formula from K25 to K41.

(j) Using the IF function, write a formula in cell M24 to determine if the number of hours rented for the given server is more than the average. Copy the formula to M25 to M41.

Screenshots attached below

thank you!

20
21
22
23
24
25
26
27
2
28
m
29
30
3
31
22
32
3
33
7
34
2
35
→
36
*
37
38
39
40
41
42
A
Server
ID
Large
B
1 Medium
2 Large
3 Large
4 Large
5 Large
Large
Server Size Server Type
6 Large
F
7 Small
teman
8
Small
paman
9 Large
10 Mediu
10 Medium
11 Large
12 Small
Ta
13 Small
14 Small
15 Mediu
15 Medium
16 Large
17 Medium
18 Large
p
Spot
Spot
On-Demand
Spot
Spot
t
с
On-Demand
On-Demand
Spot
Spot
31.JJIZU
p
t
B
On-Demand
On-Demand
Spot
Spot
On-Demand
Spot
On-Demand
Spot
On-Demand
D
4070
Rented
Date & Time
11/25/2020 2:00
11/25/2020 9:00
11/25/2020 17:00
11/25/2020 21:00
11/26/2020 9:00
EYENYEVEN V
11/26/2020 11:00
***
11/26/2020 16:00
ini
11/26/2020 23:00
14/02/2006
11/27/2020 1:00
11/23/2030.05.
11/27/2020 23:00
inn inna
11/28/2020 1:00
ini
11/28/2020 2:00
11/28/2020 3:00
44/20/2020.6m
11/28/2020 9:00
11/28/2020 11:00
11/28/2020 16:00
11/28/2020 16:00
11/28/2020 16:00
E
Returned
Date & Time
11/26/2020 9:00
11/27/2020 1:00
11/30/2020 13:00
11/30/2020 13:00
11/30/2020 13:00
**1/2000 45.00
11/30/2020 13:00
44/20/2000 45.00
11/30/2020 13:00
44/20/2000
42.00
11/30/2020 13:00
44/20/2000 42.00
11/30/2020 13:00
44/20/2000 42.00
11/30/2020 13:00
44/20/2000 42.00
11/30/2020 13:00
44/20/2000 42.00
11/30/2020 13:00
44/20/2000 42.00
11/30/2020 13:00
11/20/2020 12-00
11/30/2020 13:00
11/30/2020 13:00
11/30/2020 13:00
11/30/2020 13:00
11/30/2020 13:00
F
# of hours
Rented
Table 10
31 $
40 $
116 $
112 $
100 $
cale
98 $
→
93 S
ada
86 S
q
84 S
62 $
¥
60 $
Fala
59 $
Fata
58 S
q
52 $
S
50 $
45 $
45 $
45 $
G
Rate per
hour
0.66560
1.33120
1.33120
1.33120
433430
1.33120
******
1.33120
633300
0.33280
0.33300
0.33280
433430
1.33120
0.66560
******
1.33120
Barce
0.33280
0.33280
0.33280
O SESSO
0.66560
1.33120
0.66560
1.33120
H
Percent
Discount
30% $
40% $
0% $
40% $
40% $
0% $
0% $
20% $
www.
40% $
ou
0% $
0% $
20% $
20% $
0% $
30% $
0% $
30% $
0% $
Discount
Amount
Rate per hour
after discount
0.19968 $
0.53248 S
$
0.53248 $
0.53248 $
$
$
0.06656 $
www.
0.53248 $
$
$
0.06656 $
0.06656 $
$
0.19968 $
J
$
0.19968 $
$
K
Total
Cost
0.46592 $
14.44
0.79872 $ 31.95
1.33120 $ 154.42
0.79872 $ 89.46
2003
79.87
www.
0.79872 $
****
1.33120 $ 130.46
******
0.33280 $ 30.95
**
0.26624 $ 22.90
三
****
0.79872 $
.....
67.09
::::
41.27
0.66560 $
0.00000
1.33120 $
79.87
******
0.26624 $ 15.71
0.26624 $ 15.44
0.33280 $ 17.31
0.46592 $ 23.30
1.33120 $ 59.90
0.46592 $ 20.97
1.33120 $ 59.90
Is number of hours
rented more than
average?
M
Is cost more
than the
average
cost?
N
o
P
Q
R
S

B
с
D
E
F
G
H
I
A
1 2020 cyber week (during the thanksgiving period) saw a spike in e-commerce sales throughout the USA. A2Z eCommerz LLC website and app also witnessed a surge in traffic.
During the cyber week, the IT team rented some temporary servers. Two types of servers were rented, (i) On-demand servers and (ii) Spot servers.
Spot servers are idle servers in the cloud service provider's inventory, and they come at a discounted rate.
However, you can not use spot servers for the long term because when the provider's idle server inventory reduces, some of the spot servers may be terminated.
Hence, there is a termination risk in using spot servers. However, on-demand servers can't be terminated by the provider.
17
2
3 The servers are available in different sizes, but the firm rented only Small, Medium, and Large servers.
4 Table 8 has the pricing and spot server discounts for the servers of various sizes. Table 10 has details on the servers which the firm rented.
5 (a) Write a formula in cell F24 to compute the # of hours the firm rented the server. The number of hours rented = (Returned Date & Time - Rented Date and Time) * 24. Copy the formula to F25 to
6 (b) Using VLOOKUP, write a formula in cell G24 to find the rate per hour from Table 8 for the given server size. Copy the formula to G25 to G41.
7
(c) Using IF and VLOOKUP functions, write a formula in cell H24 to find the spot server's discount rate. For on-demand servers, the discount rate is 0%. Copy the formula to H25 to H41.
8
(d) Write a formula in cell 124 to compute the discount amount on the server. Discount Amount = Percent Discount Rate per hour. Copy the formula to 125 to 141.
9 (e) Write a formula in cell J24 to compute the rate per hour after the discount. Rate per hour after discount = Rate per hour - Discount Amount. Copy the formula from J25 to J41.
10 (f) Write a formula in cell K24 to compute the Total cost for the given server. Total Cost = Rate per hour after discount * # of hours Rented. Copy the formula from K25 to K41.
11 (g) Write a formula in cell G17 to compute the average hours Rented. Average hours rented is the average of the # of hours Rented (F24:F41).
12 (h) Write a formula in cell G18 to calculate the average total cost. The average total cost is the average of the total cost (K24:K41).
13 (i) Using the IF function, write a formula in cell L24 to determine if the number of hours rented for the given server is more than the average. Copy the formula from K25 to K41.
14 (j) Using the IF function, write a formula in cell M24 to determine if the number of hours rented for the given server is more than the average. Copy the formula to M25 to M41.
15
16
18
19
20
21
Server Size
1 Small
2 Medium
3 Large
Table 8
On-Demand
Instance
Hourly Rate
$0.33280
$0.66560
$1.33120
Discount for Spot
Servers
20%
30%
40%
Table 9
Average Hours
Rented
Average Total
Cost
$68.66667
K
53.07
M
N
0
P
Q
F

Accepted Answer

(i)
The formula to be used in cell L24 is given below. Copy it down the range L25:L41.…