Question 1

Explain the different Lookup functions in Excel.

Accepted Answer

Excel provides some useful functions for finding specific data in a spreadsheet. These functions are useful in many applications:
VLOOKUP(lookup_value, table_array, col_index_num) looks up a value in the leftmost
column of a table and returns a value in the same row from a column you specify. The table must be sorted in an ascending order.
HLOOKUP(lookup_value, table_array, row_index_num) looks up a value in the top row of a table and returns a value in the same column from a row you specify. The table must be sorted in an ascending order from left to right.
INDEX(array, row_num, col_num) Returns a value or reference of the cell at the intersection of a particular row and column in a given range.
MATCH(lookup_value, lookup_array, match_type) Returns the relative position of an item in an array that matches a specified value in a specified order.

Question 2

For which of the following columns can the COUNT function be performed?

Accepted Answer

A)  column G 
B)  column E 
C)  column B 
D)  column A 
A)  column G 
B)  column E 
C)  column B 
D)  column A B

Question 3

To find the average of the total cost of orders from Rex Technologies, what Excel formula should be used in A12?

Accepted Answer

A)  =AVERAGE(C4:C10) 
B)  =AVERAGE(C4:C7) 
C)  =AVERAGE(E4:E7) 
D)  =AVERAGE(E4:E10) 
A)  =AVERAGE(C4:C10) 
B)  =AVERAGE(C4:C7) 
C)  =AVERAGE(E4:E7) 
D)  =AVERAGE(E4:E10) C

Question 4

Use the data given below to answer the following question(s).
Below is a spreadsheet of purchase orders for a computer hardware retailer. \(\begin{array}{|l|l|l|l|l|l|l|l|l|} \hline&A&B&C&D&E&F&G&H\
\hline & \text { Purchase } \
1 & \text { Orders } \
\hline2\
\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 { A/P 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 & \$ 3115 & 20 & \text { AL123 } & \text { Large } \
\hline & \text { Rex } & & & & & & &\5 & \text { Technologies } & \text { Monitor } & \$ 150 & 15 & \$ 2250 & 25 & \text { AL234 } & \text { Small } \
\hline & \text { Rex } & & & & & & &\6 & \text { Technologies } & \text { Keyboard } & \$ 15 & 40 & \$ 600 & 15 & \text { AL345 } & \text { Large } \
\hline & \text { Rex } & & & & & & &\
 7 &\text { Technologies}& \text { Speakers }&\$ 15 & 20 & \$ 300 & 25 & \text { AL456 } & \text { Small } \\hline & \text { Rex } \8&\text { Wavetech } & \text { HD Cables } & \$ 5 & 10 & \$ 50 & 25 & \text { KO876 } & \text { Small } \
\hline &\text { Max's } & & & & & & &\9&\text { Wavetech } & \text { Processor } & \$ 278 & 27 & \$ 6950 & 30 & \text { KO765 } & \text { Large } \
\hline& \text { Max's } & & & & & &\10&\text { Wavetech } & \text { Hard disk } & \$ 120 & 18 & \$ 2160 & 20 & \text { KO654 } & \text { Small }\\hline 11 & \
\hline 12 & \
\hline
\end{array}\)
-To find the total order cost, what Excel formula should be used in A12?

Accepted Answer

A)  =COUNT(C4:C10) 
B)  =COUNT(C4:C7) 
C)  =MAX(C4:C10) 
D)  =SUM(E4:E10) 
A)  =COUNT(C4:C10) 
B)  =COUNT(C4:C7) 
C)  =MAX(C4:C10) 
D)  =SUM(E4:E10)

Question 5

If purchase quantities of 25 units or higher are found to be large orders, and orders less than 25 are considered to be small, what IF function should be entered in H4 to be copied to H5:H10 to calculate each order's size?

Accepted Answer

A)  =IF(D4=AND=OR=25,"Large","Small") 
B)  =IF(D425,"Small")=AND(D4=25,"Large") 
C)  =IF(D4=25,"Large")=OR("Small") 
D)  =IF(D4>=25,"Large","Small") 
A)  =IF(D4=AND=OR=25,"Large","Small") 
B)  =IF(D425,"Small")=AND(D4=25,"Large") 
C)  =IF(D4=25,"Large")=OR("Small") 
D)  =IF(D4>=25,"Large","Small")

Question 6

Use the data given below to answer the following question(s)
Below is the spreadsheet for demand prediction of a company that sells chocolates. $$\begin{array} { | l | l | l | l | } 
\hline & \text { A } & \text { B } & \text { C } \
\hline 1 & \text { Demand Prediction } & & \
& \text { Models } & & \
\hline 2 & & & \
\hline 3 & \text { Linear Model } & & \
\hline 4 & \text { A } & 10,000 & \
\hline 5 & \text { B } & 10 & \
\hline 6 & & & \
\hline 7 & \text { Price } & \text { Demand } & \
\hline 8 & \$ 50 & 9,500 & \
\hline 9 & \$ 55 & 9,450 & \
\hline 10 & \$ 45 & 9,550 & \
\hline 11 & & & \
\hline
\end{array}$$
-If a dollar sign is used after the column in B5 (B$5), how will the formula at B8 be represented in C9 using absolute addressing?

Accepted Answer

A)  C3-B$5*C9 
B)  C5-C$6*B9 
C)  C5-C$6*C9 
D)  C5-C$5*B9 
A)  C3-B$5*C9 
B)  C5-C$6*B9 
C)  C5-C$6*C9 
D)  C5-C$5*B9

Question 7

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)  =IF(G7=9)(G7=7)=OR(G7=4) 
B)  =IF(G7=7)=THEN(C3=9)=OR(C3=4) 
C)  =IF(C3=9)(C3=7)=OR(C3=4) 
D)  =IF(C3=9,7,4) 
A)  =IF(G7=9)(G7=7)=OR(G7=4) 
B)  =IF(G7=7)=THEN(C3=9)=OR(C3=4) 
C)  =IF(C3=9)(C3=7)=OR(C3=4) 
D)  =IF(C3=9,7,4)

Question 8

What is the Insert function in Excel?

Accepted Answer

The easiest way to locate a particular f

Question 9

If, in the spreadsheet, cells B9 and B10 were empty, which of the following formulas should be entered in B8 so that the formula can be dragged to B9 and B10 to obtain their correct values?

Accepted Answer

A)  B4-B5*A8 
B)  B4-B5*$A8 
C)  $B4-B5*$A8 
D)  $B$4-$B$5*$A8 
A)  B4-B5*A8 
B)  B4-B5*$A8 
C)  $B4-B5*$A8 
D)  $B$4-$B$5*$A8

Question 10

To copy a formula from a single cell or range of cells down a column or across a row, first , click and hold the mouse on the small square in the lower right-hand corner of the cell, and drag the formula to the "target" cells which you wish to copy.

Accepted Answer

A)  press Ctrl-C 
B)  select the cell or range 
C)  press Ctrl-Enter 
D)  select the whole spreadsheet 
A)  press Ctrl-C 
B)  select the cell or range 
C)  press Ctrl-Enter 
D)  select the whole spreadsheet

Question 11

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 
A)  When match_type = -1 
B)  When match_type >1 
C)  When match_type = 0 
D)  When match_type = 1

Question 12

Trace the process of copying and pasting a cell, which has a formula in it, such that the formula is not retained in the pasted cell.

Accepted Answer

A)  Home - Paste - Paste Special - Paste Values 
B)  Home - Paste - Paste Special - Paste Validation 
C)  Home - Paste - Paste Special - Paste Formats 
D)  Home - Paste - Paste Special - Paste Formulas 
A)  Home - Paste - Paste Special - Paste Values 
B)  Home - Paste - Paste Special - Paste Validation 
C)  Home - Paste - Paste Special - Paste Formats 
D)  Home - Paste - Paste Special - Paste Formulas

Question 13

The function returns a value or reference of the cell at the intersection of a particular row and column in a given range.

Accepted Answer

A)  VLOOKUP(lookup_value, table_array, col_index_num) 
B)  INDEX(array, row_num, col_num) 
C)  MATCH(lookup_value, lookup_array, match_type) 
D)  HLOOKUP(lookup_value, table_array, row_index_num) 
A)  VLOOKUP(lookup_value, table_array, col_index_num) 
B)  INDEX(array, row_num, col_num) 
C)  MATCH(lookup_value, lookup_array, match_type) 
D)  HLOOKUP(lookup_value, table_array, row_index_num)

Question 14

Use the data given below to answer the following question(s)
Below is the spreadsheet for demand prediction of a company that sells chocolates. $$\begin{array} { | l | l | l | l | } 
\hline & \text { A } & \text { B } & \text { C } \
\hline 1 & \text { Demand Prediction } & & \
& \text { Models } & & \
\hline 2 & & & \
\hline 3 & \text { Linear Model } & & \
\hline 4 & \text { A } & 10,000 & \
\hline 5 & \text { B } & 10 & \
\hline 6 & & & \
\hline 7 & \text { Price } & \text { Demand } & \
\hline 8 & \$ 50 & 9,500 & \
\hline 9 & \$ 55 & 9,450 & \
\hline 10 & \$ 45 & 9,550 & \
\hline 11 & & & \
\hline
\end{array}$$
-Given that D = a-bP, where D, is demand, "a" and "b," are linear constants, and P, is price, from the below spreadsheet, how will the formula in B9 be represented in Excel using relative addressing?

Accepted Answer

A)  B4-B5*A9 
B)  C5-C6*A10 
C)  B4-B5*A10 
D)  B5-B6*A10 
A)  B4-B5*A9 
B)  C5-C6*A10 
C)  B4-B5*A10 
D)  B5-B6*A10

Question 15

Describe the method of calculating the net present value (NPV) in Excel.

Accepted Answer

The Excel function NPV (rate, value1, va

Question 16

Use the data given below to answer the following question(s).
Below is a spreadsheet of purchase orders for a computer hardware retailer. \(\begin{array}{|l|l|l|l|l|l|l|l|l|} \hline&A&B&C&D&E&F&G&H\
\hline & \text { Purchase } \
1 & \text { Orders } \
\hline2\
\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 { A/P 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 & \$ 3115 & 20 & \text { AL123 } & \text { Large } \
\hline & \text { Rex } & & & & & & &\5 & \text { Technologies } & \text { Monitor } & \$ 150 & 15 & \$ 2250 & 25 & \text { AL234 } & \text { Small } \
\hline & \text { Rex } & & & & & & &\6 & \text { Technologies } & \text { Keyboard } & \$ 15 & 40 & \$ 600 & 15 & \text { AL345 } & \text { Large } \
\hline & \text { Rex } & & & & & & &\
 7 &\text { Technologies}& \text { Speakers }&\$ 15 & 20 & \$ 300 & 25 & \text { AL456 } & \text { Small } \\hline & \text { Rex } \8&\text { Wavetech } & \text { HD Cables } & \$ 5 & 10 & \$ 50 & 25 & \text { KO876 } & \text { Small } \
\hline &\text { Max's } & & & & & & &\9&\text { Wavetech } & \text { Processor } & \$ 278 & 27 & \$ 6950 & 30 & \text { KO765 } & \text { Large } \
\hline& \text { Max's } & & & & & &\10&\text { Wavetech } & \text { Hard disk } & \$ 120 & 18 & \$ 2160 & 20 & \text { KO654 } & \text { Small }\\hline 11 & \
\hline 12 & \
\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)  =COUNTIF(D4:D10) 
B)  =SUM(D4:D7) 
C)  =MAX(D4:D7) 
D)  =COUNT(D4:D7) 
A)  =COUNTIF(D4:D10) 
B)  =SUM(D4:D7) 
C)  =MAX(D4:D7) 
D)  =COUNT(D4:D7)

Question 17

Which of the following symbols is used to represent exponents in Excel?

Accepted Answer

A)  ^ 
B)  * 
C)  # 
D)  ! 
A)  ^ 
B)  * 
C)  # 
D)  !

Question 18

Which of the following ways would 102 × 53 / 100 - 73 be represented in an Excel spreadsheet?

Accepted Answer

A)  10(2) * 5(3) / 100 ^ 73 
B)  10(2) ^ 5(3) / 100 - 73 
C)  10^2 * 5^3 / 100 - 73 
D)  10*2 ^ 5*3 / 100 - 73 
A)  10(2) * 5(3) / 100 ^ 73 
B)  10(2) ^ 5(3) / 100 - 73 
C)  10^2 * 5^3 / 100 - 73 
D)  10*2 ^ 5*3 / 100 - 73

Question 19

AND(condition 1, condition 2…) is a logical function that returns TRUE if all conditions are true and FALSE if not.

Accepted Answer

A) True 
 B)False

Question 20

Identify the equation for calculating the net present value for a stated period of time, where Ft = cash flow in period t, and i is the discount rate.

Accepted Answer

A)  $ \mathrm{NPV}=\sqrt{\frac{\sum_{t=0}^{n} \mid \frac{F_{\mathrm{t}}+i}{2}}{n}} $ 
B)  $ \mathrm{NPV}=\sum_{t-0}^{n} F_{t}(1+i)^{t} $ 
C)  $\mathrm{NPV}=\sum_{t-0}^{n} \frac{F_{t}}{(1+i)^{t}} $ 
D)  $ \mathrm{NPV}=\sum_{t-0}^{n} \frac{F^{t}}{i} \sqrt{(1+i)^{t}} $ 
A)  $ \mathrm{NPV}=\sqrt{\frac{\sum_{t=0}^{n} \mid \frac{F_{\mathrm{t}}+i}{2}}{n}} $ 
B)  $ \mathrm{NPV}=\sum_{t-0}^{n} F_{t}(1+i)^{t} $ 
C)  $\mathrm{NPV}=\sum_{t-0}^{n} \frac{F_{t}}{(1+i)^{t}} $ 
D)  $ \mathrm{NPV}=\sum_{t-0}^{n} \frac{F^{t}}{i} \sqrt{(1+i)^{t}} $

Explain the different Lookup functions in Excel.

For which of the following columns can the COUNT function be performed?

To find the average of the total cost of orders from Rex Technologies, what Excel formula should be used in A12?

If purchase quantities of 25 units or higher are found to be large orders, and orders less than 25 are considered to be small, what IF function should be entered in H4 to be copied to H5:H10 to calculate each order's size?

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.

What is the Insert function in Excel?

If, in the spreadsheet, cells B9 and B10 were empty, which of the following formulas should be entered in B8 so that the formula can be dragged to B9 and B10 to obtain their correct values?

To copy a formula from a single cell or range of cells down a column or across a row, first , click and hold the mouse on the small square in the lower right-hand corner of the cell, and drag the formula to the "target" cells which you wish to copy.

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

Trace the process of copying and pasting a cell, which has a formula in it, such that the formula is not retained in the pasted cell.

The function returns a value or reference of the cell at the intersection of a particular row and column in a given range.

Describe the method of calculating the net present value (NPV) in Excel.

Which of the following symbols is used to represent exponents in Excel?

Which of the following ways would 102 × 53 / 100 - 73 be represented in an Excel spreadsheet?

AND(condition 1, condition 2…) is a logical function that returns TRUE if all conditions are true and FALSE if not.

Identify the equation for calculating the net present value for a stated period of time, where Ft = cash flow in period t, and i is the discount rate.

Business Analytics

Visualizing and Exploring Data

Descriptive Statistical Measures

Probability Distributions and Data Modeling

Sampling and Estimation

Statistical Inference

Trendlines and Regression Analysis

Forecasting Techniques

Introduction to Data Mining

Spreadsheet Modeling and Analysis

Monte Carlo Simulation and Risk Analysis

Linear Optimization

Applications of Linear Optimization

Integer Optimization

Decision Analysis

Filters

Exam 2: Analytics on Spreadsheets

Explain the different Lookup functions in Excel.

For which of the following columns can the COUNT function be performed?

To find the average of the total cost of orders from Rex Technologies, what Excel formula should be used in A12?

If purchase quantities of 25 units or higher are found to be large orders, and orders less than 25 are considered to be small, what IF function should be entered in H4 to be copied to H5:H10 to calculate each order's size?

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.

What is the Insert function in Excel?

If, in the spreadsheet, cells B9 and B10 were empty, which of the following formulas should be entered in B8 so that the formula can be dragged to B9 and B10 to obtain their correct values?

To copy a formula from a single cell or range of cells down a column or across a row, first , click and hold the mouse on the small square in the lower right-hand corner of the cell, and drag the formula to the "target" cells which you wish to copy.

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

Trace the process of copying and pasting a cell, which has a formula in it, such that the formula is not retained in the pasted cell.

The function returns a value or reference of the cell at the intersection of a particular row and column in a given range.

Describe the method of calculating the net present value (NPV) in Excel.

Which of the following symbols is used to represent exponents in Excel?

Which of the following ways would 102 × 53 / 100 - 73 be represented in an Excel spreadsheet?

AND(condition 1, condition 2…) is a logical function that returns TRUE if all conditions are true and FALSE if not.

Identify the equation for calculating the net present value for a stated period of time, where Ft = cash flow in period t, and i is the discount rate.

Business Analytics

Visualizing and Exploring Data

Descriptive Statistical Measures

Probability Distributions and Data Modeling

Sampling and Estimation

Statistical Inference

Trendlines and Regression Analysis

Forecasting Techniques

Introduction to Data Mining

Spreadsheet Modeling and Analysis

Monte Carlo Simulation and Risk Analysis

Linear Optimization

Applications of Linear Optimization

Integer Optimization

Decision Analysis

Filters