## Microsoft Excel

#### Q37. You need to determine the commission earned by each Sales Rep, based on the Sales amounts in B3:B50 and the Commission rate specified in cell A1. You want to enter a formula in C3 and copy it down to C50. Which formula should you use?

A B C
1 8.5% 2018 Commission
2 Sales Rep 2018 Sales Commission Earned
3 Jordan Hinton \$123,938.00
4 Lilah Douglas \$5594,810.00
5 Karyn Reese \$235,954.00
6 Chiquita Walsh \$684,760.00

#### Q62. What is `Colors[Inventory]` referring to here?

`=SUMIFS(Colors[Inventory],Colors[Colors],"Orange")`

#### Q63. Which VLOOKUP function, when entered into cell L2 and then dragged to cell L5, returns the average number of calls for the representative IDs listed in column J?

• `=VLOOKUP(A2,J2:L5,1,FALSE)`
• `=VLOOKUP(J2,A\$2:C\$7,1,FALSE)`
• `=VLOOKUP(J2,A\$2:C\$7,3,FALSE)`
• `=VLOOKUP(J2,A2:C7,3,FALSE)`

`because we are interested in the value of the 3rd column of the table`

#### Q64. Which formula calculates the total value of a single row of cells across a range of columns?

`the sum of columns C to Y for the same row 15`

#### Q66. How can you create the lower table from the top one when the tables are `not` linked?

`because it needs to be transposed without creating a reference`

### Q70. Which Excel feature allows you to hide rows or columns with an easily visible expand/collapse?

#### Q71. Monthly revenues of 2019 are entered in B2:M2, as shown below, To get year-to-date running total revenues, what formula should you enter in B3 and autofill through M3?

`we are calculating the running total here`

#### Q75. What is the difference between pressing the delete key and using the clear command in the Home tab’s Editing group?

• deletes removes the entire column or row. Clear removes the content from the column or row
• Delete removes the cell itself, shifting cells either up or to the left. Clear removes content and properties but does not muves cells
• Delete removes formulas and values. clear removes formulas, values, hyperlinks, formats, comments and notes

#### Q81. This data needs to be sorted by Group, then by Last Name, then by First Name. How do you accomplish this?

1. Rearrange the columns in this order: Group, Last Name, First Name.
2. Right-click any of the headers.
3. Select Sort All.
1. Select any cell in the dataset.
2. In the Data tab, click the Sort button.
3. Add two levels to the default level.
4. Populate the Sort-by fields in this order: Group, Last Name, First Name.
1. Highlight the entire dataset.
2. In the Data tab, click the Sort button. The headers appear.
3. Drag the headers into this order: Group, Last Name, First Name.
1. Select a cell in the Group column, then sort.
2. Select a cell in the Last Name column, then sort.
3. Select a cell in the FIrst Name column, then sort.

#### Q90. What is Colors[Inventory] referring to here?

=SUMIFS(Colors[Inventory],Colors[Colors],”Orange”)

#### Q96. Cell A1 contains the number 3. Which formula returns the text Apple?

• `=SELECT(A1, "Banana", "Orange", "Apple", "Mango")`
• `=CHOOSE(A1, "Banana", "Orange", "Apple", "Mango")`
• `=CHOOSE(A1,"Banana","Orange","Apple","Mango")`
• `=MATCH(A1,{"Banana","Orange","Apple","Mango"})`

#### Q102. You are determining `%` growth by dividing Growth by Sales. Which Excel function would you use to avoid #DIV/0! errors?

Read More :   24 Useful Excel Macro Examples for VBA Beginners (Ready-to-use)

Check Also
Close