Advanced Excel For Actuaries Quiz

1. Using the INDEXMATCH method, what formula did you enter into cell D7 to get the Plus brand Camera warranty price?

The correct answer is =INDEX($H$4:$I$17,MATCH(B7,$G$4:$G$17,0),MATCH(C7,$H$3:$I$3,0)). This formula uses the INDEX and MATCH functions to retrieve the warranty price for the Plus brand Camera. The INDEX function returns a value from a specified range based on a row and column number. The MATCH function is used to find the position of a value in a range. In this case, the first MATCH function is used to find the row number of the Plus brand in the range G4:G17, and the second MATCH function is used to find the column number of the Camera in the range H3:I3. By combining these functions with the INDEX function, the formula retrieves the correct warranty price for the Plus brand Camera.

Explanation

The correct answer is =INDEX($H$4:$I$17,MATCH(B7,$G$4:$G$17,0),MATCH(C7,$H$3:$I$3,0)). This formula uses the INDEX and MATCH functions to retrieve the warranty price for the Plus brand Camera. The INDEX function returns a value from a specified range based on a row and column number. The MATCH function is used to find the position of a value in a range. In this case, the first MATCH function is used to find the row number of the Plus brand in the range G4:G17, and the second MATCH function is used to find the column number of the Camera in the range H3:I3. By combining these functions with the INDEX function, the formula retrieves the correct warranty price for the Plus brand Camera.

Submit

2. On the Summary tab, what formula did you use to get Region 6's Aug value (cell G9)?

=INDIRECT("'"&F$1&"'!"&$L9)

=INDIRECT("'"&G$1&"'!"&$L2)

=INDIRECT("'"&G$1&"'!"&$L9)

=SUM(INDIRECT(H1&"!"&"B2:B13"))

The formula used to get Region 6's Aug value (cell G9) is =INDIRECT("'"&G$1&"'!"&$L9). This formula uses the INDIRECT function to reference the value in cell G1 (which contains the name of the sheet) and combines it with the value in cell L9 to create a cell reference in the form 'SheetName'!$L9. The INDIRECT function then returns the value from that cell.

Explanation

The formula used to get Region 6's Aug value (cell G9) is =INDIRECT("'"&G$1&"'!"&$L9). This formula uses the INDIRECT function to reference the value in cell G1 (which contains the name of the sheet) and combines it with the value in cell L9 to create a cell reference in the form 'SheetName'!$L9. The INDIRECT function then returns the value from that cell.

3. What Total Warranty Sales for 2015 did you get as an answer?

12964

19753

22975

27852

The correct answer for the Total Warranty Sales for 2015 is 27852.

Explanation

The correct answer for the Total Warranty Sales for 2015 is 27852.

4. To create a dynamic range for the last 10 sales values, what does "COUNT(DynamicCharts!$B$3:$B$1000)-1" in the formula: "=OFFSET(DynamicCharts!$B$3,COUNT(DynamicCharts!$B$3:$B$1000)-1,0,-10,1)"

It is the new range width

It represents the number of rows down from the reference point

It represents the height of the new dynamic range

It represents the number of columns over from the reference point

The expression "COUNT(DynamicCharts!$B$3:$B$1000)-1" in the formula represents the number of rows down from the reference point. The COUNT function counts the number of cells in the range B3 to B1000 that contain values. By subtracting 1 from this count, the formula determines how many rows down from the reference point (B3) the new dynamic range should start. This allows the formula to create a dynamic range for the last 10 sales values by specifying the appropriate height and starting position.

Explanation

The expression "COUNT(DynamicCharts!$B$3:$B$1000)-1" in the formula represents the number of rows down from the reference point. The COUNT function counts the number of cells in the range B3 to B1000 that contain values. By subtracting 1 from this count, the formula determines how many rows down from the reference point (B3) the new dynamic range should start. This allows the formula to create a dynamic range for the last 10 sales values by specifying the appropriate height and starting position.

5. What is the correct formula to parse the Region number for the first part of the text string cell F2?

=if(left(F2,2)="10","10",left(F2,1))

=left(F2,1)

=if(find(F2,2,2)="10","10",left(F2,2))

=left(F2,2)

The correct formula to parse the Region number for the first part of the text string in cell F2 is "=if(left(F2,2)="10","10",left(F2,1))". This formula checks if the first two characters of the text string in cell F2 are equal to "10". If they are, it returns "10". If they are not, it returns the first character of the text string.

Explanation

The correct formula to parse the Region number for the first part of the text string in cell F2 is "=if(left(F2,2)="10","10",left(F2,1))". This formula checks if the first two characters of the text string in cell F2 are equal to "10". If they are, it returns "10". If they are not, it returns the first character of the text string.