Question 1

The sort buttons in Excel can be found under:

Accepted Answer

A)  the Data tab in the Sort & Filter group. 
B)  the Home tab in the Styles group. 
C)  the Insert tab in the Sort group. 
D)  the Sort tab in the Filter group.

Question 2

If cell G7 contains the function , it states that if the value in cell C3 is 9, the number 7 will be assigned to cell G7; if the value in cell C3 is not 9, the number 4 will be assigned to cell G7.

Accepted Answer

A)  =IFG7=9)G7=7)=ORG7=4) 
B)  =IFG7=7)=THENC3=9)=ORC3=4) 
C)  =IFC3=9)C3=7)=ORC3=4) 
D)  =IFC3=9,7,4)

Question 3

Which of the following functions is a logical function that returns TRUE if any condition is true and FALSE if not?

Accepted Answer

A)  TOvalue if true, value if false) 
B)  ANDcondition 1, condition 2…) 
C)  ORcondition 1, condition 2…) 
D)  IFcondition, value if true, value if false)

Question 4

What is a database?

Accepted Answer

A)  a structured collection of related files and data 
B)  simply a collection of data 
C)  a data file holding a single file 
D)  flat files used to store data

Question 5

Use the data given below to answer the following questions).
Below is a spreadsheet of purchase orders for a computer hardware retailer. \(\begin{array}{|l|l|l|l|l|l|l|l|l|}
\hline& \text { A } & \text { B } & C & \mathrm{D}&E&F&G&H \
\hline &\text { Purchase } & & & \
1& \text { Orders } & & & \
\hline 2\
\hline 3 & \text { Supplier } & \begin{array}{l}
\text { item }\\text { Description }\
\end{array} & \begin{array}{l}
\text { Item } \
\text { Cost }
\end{array} & \text { Quantity } & \begin{array}{l}
\text { Cost per } \
\text { Order }
\end{array} & \begin{array}{l}
\text { AP Terms } \
\text { Months) }
\end{array} & \begin{array}{l}
\text { Order } \
\text { No. }
\end{array} & \begin{array}{l}
\text { Order } \
\text { Size }
\end{array} \
\hline & \text { Rex } & \text { Graphics } & & & & & \
4&\text { Technologies } & \text { Card } & \$ 89 & 35 & \text { \$3115 } & 20 & \text { AL123 } & \text { Large } \
\hline5 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}& \text { Monitor } & \$ 150 & 15 & \$ 2250 & 25 & \text { AL234 } & \text { Small } \
\hline6 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Keyboard } & \$ 15 & 40 & \$ 600 & 15 & \text { AL345 } & \text { Large } \
\hline7 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Speakers } & \$ 15 & 20 & \$ 300 & 25 & \text { AL456 } & \text { Small } \
\hline8 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { HD Cables } & \$ 5 & 10 & \$ 50 & 25 & \text { KO876 } & \text { Small } \
\hline9 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Processor } & \$ 278 & 27 & \$ 6950 & 30 & \text { KO765 } & \text { Large }\
\hline10 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Hard disk } & \$ 120 & 18 & \$ 2160 & 20 & \text { KO654 } & \text { Small }\

\hline 11\
\hline12&\text { Supplier }&\text { Order Size }&\begin{array}{l}
\text { item } \
\text { cost }
\end{array} \
\hline13 &\text { Rex Technologies}&\text { Large } &>15\
\hline 14&\text { Max's Wavetech }\
\hline
\end{array}\)
-To find the number of orders with A/P terms less than 25 months, what Excel formula should be used in A12?

Accepted Answer

A)  =COUNTIFF4:F10,"<25") 
B)  =COUNTF4:F10,25) 
C)  =AVERAGEF4:F10,"<25") 
D)  =COUNTIFF4:F10,F5)

Question 6

For which of the following MATCH functions must the values in the lookup_array be ordered in a descending order?

Accepted Answer

A)  When match_type = -1 
B)  When match_type >1 
C)  When match_type = 0 
D)  When match_type = 1

Question 7

In a database, information is stored and maintained in .

Accepted Answer

A)  fields 
B)  measurements 
C)  entities 
D)  attributes

Question 8

Use the data given below to answer the following questions).
Below is a spreadsheet of purchase orders for a computer hardware retailer. \(\begin{array}{|l|l|l|l|l|l|l|l|l|}
\hline& \text { A } & \text { B } & C & \mathrm{D}&E&F&G&H \
\hline &\text { Purchase } & & & \
1& \text { Orders } & & & \
\hline 2\
\hline 3 & \text { Supplier } & \begin{array}{l}
\text { item }\\text { Description }\
\end{array} & \begin{array}{l}
\text { Item } \
\text { Cost }
\end{array} & \text { Quantity } & \begin{array}{l}
\text { Cost per } \
\text { Order }
\end{array} & \begin{array}{l}
\text { AP Terms } \
\text { Months) }
\end{array} & \begin{array}{l}
\text { Order } \
\text { No. }
\end{array} & \begin{array}{l}
\text { Order } \
\text { Size }
\end{array} \
\hline & \text { Rex } & \text { Graphics } & & & & & \
4&\text { Technologies } & \text { Card } & \$ 89 & 35 & \text { \$3115 } & 20 & \text { AL123 } & \text { Large } \
\hline5 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}& \text { Monitor } & \$ 150 & 15 & \$ 2250 & 25 & \text { AL234 } & \text { Small } \
\hline6 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Keyboard } & \$ 15 & 40 & \$ 600 & 15 & \text { AL345 } & \text { Large } \
\hline7 & \begin{array}{l}
\text { Rex } \
\text { Technologies }
\end{array}&\text { Speakers } & \$ 15 & 20 & \$ 300 & 25 & \text { AL456 } & \text { Small } \
\hline8 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { HD Cables } & \$ 5 & 10 & \$ 50 & 25 & \text { KO876 } & \text { Small } \
\hline9 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Processor } & \$ 278 & 27 & \$ 6950 & 30 & \text { KO765 } & \text { Large }\
\hline10 & \begin{array}{l}
\text { Max's } \
\text { wavetech }
\end{array}&\text { Hard disk } & \$ 120 & 18 & \$ 2160 & 20 & \text { KO654 } & \text { Small }\

\hline 11\
\hline12&\text { Supplier }&\text { Order Size }&\begin{array}{l}
\text { item } \
\text { cost }
\end{array} \
\hline13 &\text { Rex Technologies}&\text { Large } &>15\
\hline 14&\text { Max's Wavetech }\
\hline
\end{array}\)
-To find the largest quantity of items ordered from Rex Technologies, what Excel formula should be used in A12?

Accepted Answer

A)  =COUNTIFD4:D10) 
B)  =SUMD4:D7) 
C)  =MAXD4:D7) 
D)  =COUNTD4:D7)

Question 9

Which of the following Lookup functions returns the relative position of an item in an array that equals a specified value in a specified order?

Accepted Answer

A)  HLOOKUPlookup_value, table_array, row_index_num) 
B)  MATCHlookup_value, lookup_array, match_type) 
C)  INDEXarray, row_num, col_num) 
D)  VLOOKUPlookup_value, table_array, col_index_num)

Question 10

Using the spreadsheet below, provide the steps in using Excel formulas in finding the cost of the first order for Item number 1345, and the total cost of all Item numbers 1345, using the Match and Index functions in Excel. Column B is sorted by item number in ascending order. \(\begin{array}{|l|l|l|l|l|l|}
\hline & \text { A } & \text { B } & \text { C } & \text { D } & \text { E } \
\hline 1 & \text { Purchase Orders } & & & & \
\hline2\
\hline 3 & \text { Supplier } & \text { Item No. } & \text { Item Cost } & \text { Quantity } & \text { Cost per Order } \
\hline 4 & \text { Rex Technologies } & 1123 & \$ 89 & 35 & \$ 3115 \
\hline 5 & \text { Rex Technologies } & 1234 & \$ 150 & 15 & \$ 2250 \
\hline 6 & \text { Rex Technologies } & 1345 & \$ 15 & 40 & \$ 600 \
\hline 7 & \text { Rex Technologies } & 1345 & \$ 15 & 20 & \$ 300 \
\hline 8 & \text { Max's Wavetech } & 1345 & \$ 5 & 10 & \$ 50 \
\hline 9 & \text { MMax's Wavetech } & 1765 & \$ 278 & 27 & \$ 6950 \
\hline 10 & \text { Max's Wavetech } & 1654 & \$ 120 & 18 & \$ 2160 \
\hline 11 & \text { Rex Technologies } & 1765 & \$ 54 & 56 & \$ 3024 \
\hline 12 & \text { Rex Technologies } & 1100 & \$ 71 & 33 & \$ 2343 \
\hline 13 & \text { Rex Technologies } & 1100 & \$ 10 & 14 & \$ 140 \
\hline 14 & \text { Rex Technologies } & 1683 & \$ 7 & 25 & \$ 175 \
\hline 15 & \text { IMax's Wavetech } & 1683 & \$ 100 & 31 & \$ 3100 \
\hline
\end{array}\)

Accepted Answer

The answer of Using the spreadsheet below, provide the steps...

Exam 2: Database Analytics