b) Create a formula in cell E8 that uses the VLOOKUP function with the table array $B$3:$C$5. The formula will determine each customer membership status based on the possible reward levels and accompanying minimum sales made. You should be able to autofill down through cells E8:E17. c) Create a formula in cell D3 that uses the AVERAGEIF function. The formula will determine the average of the total sales made for each reward level. You should be able to autofill down through cells D3:D5. d) Create a formula in cell E3 that uses the COUNTIF function. The formula will determine the amount of salespeople in each Reward Level. You should be able to autofill down through cells E3:E5.
b) Create a formula in cell E8 that uses the VLOOKUP function with the table array $B$3:$C$5. The formula will determine each customer membership status based on the possible reward levels and accompanying minimum sales made. You should be able to autofill down through cells E8:E17. c) Create a formula in cell D3 that uses the AVERAGEIF function. The formula will determine the average of the total sales made for each reward level. You should be able to autofill down through cells D3:D5. d) Create a formula in cell E3 that uses the COUNTIF function. The formula will determine the amount of salespeople in each Reward Level. You should be able to autofill down through cells E3:E5.
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
Related questions
Question
Please answer b,c,d
Transcribed Image Text:2
7
1
2345
6
7
68
8
9
10
11
12
13
14
15
16
17
A
Minimum Sales Made
$
555
$
B
$
5,000.00
10,000.00
Name
Ryan
Andy
Stanley
Phyllis
Jim
Dwight
Michael
Pam
Karen
Creed
$
$
$
$
$
Ssssssssss
$
$
$
$
$
C
Reward Level
Bronze
Silver
Gold
Total Sales Made
3,800.00
5,000.00
6,000.00
7,500.00
12,000.00
14,500.00
10,000.00
300.00
8,500.00
200.00
SSS
$
$
$
D
Average (AVERAGEIF)
1,433.33
6,750.00
12,166.67
Reward Level (IF/AND)
Bronze
Silver
Silver
Silver
Gold
Gold
Gold
Bronze
Silver
Bronze
E
Count (COUNTIF)
3
4
3
Reward Level (VLOOKUP)
Bronze
Silver
Silver
Silver
Gold
Gold
Gold
Bronze
Silver
Bronze
Use the above spreadsheet for the following questions a, b, c, and d.
a) Create a formula in cell D8 that uses the IF and AND functions. The formula will
determine each Reward Level based on the possible Rewards and accompanying
minimum sales made. You should be able to autofill down through cells D8:D17.
b) Create a formula in cell E8 that uses the VLOOKUP function with the table
array $B$3:$C$5. The formula will determine each customer membership status
based on the possible reward levels and accompanying minimum sales made. You
should be able to autofill down through cells E8:E17.
c) Create a formula in cell D3 that uses the AVERAGEIF function. The formula will
determine the average of the total sales made for each reward level. You should be
able to autofill down through cells D3:D5.
d) Create a formula in cell E3 that uses the COUNTIF function. The formula will
determine the amount of salespeople in each Reward Level. You should be able to
autofill down through cells E3:E5.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 4 steps with 4 images
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education