Work Assessment Trivia Questions

1. The CUNTIF function counts the number of___ in a range when a specified conditions is met.

Rows

Columns

Cells

The CUNTIF function counts the number of cells in a range when a specified condition is met.

Explanation

The CUNTIF function counts the number of cells in a range when a specified condition is met.

2. A local police office wants to create a rule that if an officer pulls over a person for exceeding the speed limit by at least five miles per hour or if that person has two or more speeding violations on record, the officer will fine the speeder the higher of $200 or $50 for each mile over the speed limit. Otherwise, the fine is $45. The speed limit is entered in cell B5, the person's speed is entered in cell B10, and the person's number of previous tickets is entered in cell B11. What function derives the correct answer?

=IF(AND)(B10>B5), B11>=2), 200, 45)

=IF(AND)(B10-B5>=5, B11

=IF(OR(B10-B5>=5, B11>=2), MAX(200, (B10-B5)*50),45)

=IF(OR(B10>B5, B11>=2), MAX(200,50),45)

The correct answer is =IF(OR(B10-B5>=5, B11>=2), MAX(200, (B10-B5)*50),45). This formula uses the IF function to check if either the person's speed is at least five miles per hour over the speed limit or if they have two or more previous speeding violations. If either of these conditions is true, the formula calculates the fine by taking the higher value between $200 and the number of miles over the speed limit multiplied by $50. If neither condition is true, the formula returns a fine of $45.

Explanation

The correct answer is =IF(OR(B10-B5>=5, B11>=2), MAX(200, (B10-B5)*50),45). This formula uses the IF function to check if either the person's speed is at least five miles per hour over the speed limit or if they have two or more previous speeding violations. If either of these conditions is true, the formula calculates the fine by taking the higher value between $200 and the number of miles over the speed limit multiplied by $50. If neither condition is true, the formula returns a fine of $45.

3. The PERCENTRANK function identifies a value's rank as a percentile between_____ of a list of value.

0-1

1-2

2-3

3-4

The PERCENTRANK function in Excel calculates the rank of a given value within a list of values and expresses it as a percentile. The range 0-1 represents the minimum and maximum possible values for the percentile rank. A value of 0 indicates that the given value is at the very bottom of the list, while a value of 1 indicates that it is at the very top of the list. Therefore, the correct answer is 0-1.

Explanation

The PERCENTRANK function in Excel calculates the rank of a given value within a list of values and expresses it as a percentile. The range 0-1 represents the minimum and maximum possible values for the percentile rank. A value of 0 indicates that the given value is at the very bottom of the list, while a value of 1 indicates that it is at the very top of the list. Therefore, the correct answer is 0-1.

4. 2. A worksheet contains test scores for students in an aviation class. The scores are 95, 90, 90, 85, 75, 75, 70, and 60. Using the new RANK function in MS Excel that is considered a 'best practice', what is the rank of the second student who scored 90?

2

3

2.5

None of the above

The rank of the second student who scored 90 is 3. The RANK function in MS Excel assigns a rank to each value in a range, with the highest value receiving a rank of 1. In this case, the scores are ranked as follows: 95 (rank 1), 90 (rank 2), 90 (rank 2), 85 (rank 4), 75 (rank 5), 75 (rank 5), 70 (rank 7), and 60 (rank 8). Since there is a tie for the second and third ranks, the rank of the second student who scored 90 is 3.

Explanation

The rank of the second student who scored 90 is 3. The RANK function in MS Excel assigns a rank to each value in a range, with the highest value receiving a rank of 1. In this case, the scores are ranked as follows: 95 (rank 1), 90 (rank 2), 90 (rank 2), 85 (rank 4), 75 (rank 5), 75 (rank 5), 70 (rank 7), and 60 (rank 8). Since there is a tie for the second and third ranks, the rank of the second student who scored 90 is 3.

5. The SUMIF function calculates the total of a ___ of values when a specified condition is met.

Range

Criteriac. Sum_ranged. average_range.Criteria

Sum_range

Average_range.

The correct answer is "Range". The SUMIF function calculates the total of a range of values when a specified condition is met. In other words, it adds up the values in a specified range that meet a given criteria. The range is the set of values that the function will evaluate and perform the calculation on.

Explanation

The correct answer is "Range". The SUMIF function calculates the total of a range of values when a specified condition is met. In other words, it adds up the values in a specified range that meet a given criteria. The range is the set of values that the function will evaluate and perform the calculation on.

6. A workbook contains a list of university students. You want to identify the total number of students who are seniors and who are majoring in biology. Without modifying the original student dataset, what function can you use to find the answer to your question?

SUMIF

AVERAGEIFS

DCOUNT

COUNTIFS

To find the total number of students who are seniors and majoring in biology, you can use the COUNTIFS function. This function allows you to specify multiple criteria and counts the number of cells that meet all of the specified criteria. In this case, you can set the criteria to be "senior" in one column and "biology" in another column. The COUNTIFS function will then count the number of cells that meet both criteria, giving you the total number of students who are seniors and majoring in biology.

Explanation

To find the total number of students who are seniors and majoring in biology, you can use the COUNTIFS function. This function allows you to specify multiple criteria and counts the number of cells that meet all of the specified criteria. In this case, you can set the criteria to be "senior" in one column and "biology" in another column. The COUNTIFS function will then count the number of cells that meet both criteria, giving you the total number of students who are seniors and majoring in biology.