MS Excel Assessment Test | Practice Questions & Sample Tests

Such action would require Conditional Formatting, and here's how it can be done:

1. Choose the cells containing the Annual Sales data (column F in the given table).
2. Through the Home tab, click on "Conditional Formatting".
3. Select "Highlight Cell Rules" > "Greater Than".
4. In the dialog box that appears, enter 70 as the value to compare against.
5. Select a formatting style to apply to the cells that meet the condition. This could be a fill color, font color, or other visual indicator. Click OK

The function is CONCATENATE.

1. Choose your cell of choice within the "Full Model Name" column, perferably D4 so you can then copy the results to the rest of the table.
2. In the Formula Bar write: =CONCATENATE(A4,” “,B4). Notice the space
   added in the formula between the two cells.
3. Click Enter

This calls for a PivotTable. Here's how to use it:

1. Highlight the entire table. This can be done by clicking on the first cell in the table (A1), holding shift, and clicking on Cell F7, the last cell. 
2. In the Insert tab, under "Tables" Group, click on PivotTable.
3. Don't change anything in the opened dialog box, and click OK. A new
   SpreadSheet will be opened.
4. Under the PivotTable "Field" list, click and drag ‘Year’ to "Row" Labels. Drag
   ‘Annual Sales’ to "Values".
5. Under "Values," click on the newly appeared entry. In the opened menu, click on
   Value Field Settings.
6. In the opened dialog box click on "Sum" and then click OK.

The correct answer is A.

VLOOKUP searches for a value in the leftmost column of a table and returns a value in the same row from a column you specify. It's commonly used to find corresponding values in large datasets.

The correct answers are B and C.

Option B (=B1-A1) directly subtracts two dates, giving the number of days between them.
Option C (=DAYS(B1,A1)) is a function specifically designed to calculate the number of days between two dates.
DATEDIF is used for more complex date calculations, and NETWORKDAYS only counts workdays.

Answer B is correct.

XLOOKUP can search for values in any column and return results from any other column, regardless of position. VLOOKUP, however, is limited to searching in the leftmost column and returning values from columns to the right.
XLOOKUP is more flexible, so if columns are rearranged, it will still work correctly, while VLOOKUP might break or return incorrect results. XLOOKUP can also perform vertical and horizontal lookups, making it more versatile for various data layouts.

The correct answer is B:

=IF(B2>=5000, B2*0.1, IF(B2>=3000, B2*0.05, 0))

This formula works as follows:

First, it checks if the sales amount in B2 is greater than or equal to $5000. If true, it calculates 10% of the sales amount.
If the sales amount is not greater than or equal to $5000, it then checks if it is greater than or equal to $3000. If true, it calculates 5% of the sales amount.
If neither condition is met (i.e., the sales amount is less than $3000), the commission is set to 0.

Option A: This formula is incorrect because it incorrectly sets the commission rate of 5% for sales amounts less than $3000. According to the question, the commission for sales under $3000 should be 0%. The formula mixes the conditions and doesn't apply the correct logic for sales amounts between $3000 and $4999.

Option C: This formula has a logical error. The second IF statement checks if the sales amount is less than $5000, but this condition is already covered by the first IF (which checks for greater than or equal to $5000). The second condition should check if the sales amount is between $3000 and $4999, but instead, it incorrectly handles all amounts less than $5000, including those under $3000, which results in an incorrect calculation.

 Option D: This formula is incorrect because it does not account for sales amounts less than $3000. It calculates 5% commission for all sales amounts under $5000, without checking whether the sales are under $3000, which should have a 0% commission. The formula needs a third condition to handle the case where the sales amount is below $3000.