Microsoft Excel Formulas Trivia Quiz

Answer: phab

The mid function displays a middle section of a cell's contents. The number 3 indicates at which character the function should start. In this case, 3=p. The 4 indicates how many characters should be a part of the solution. Therefore, =mid(A1, 3, 4) will produce the result "phab".

Answer: AdventuresinAuthoring

To concatenate cells means to join them together. The function =concatenate(A1, A2, A3) will display a message of "AdventuresinAuthoring". No spaces appear in the concatenated phrase since no spaces were included in the function. To show spaces, the correct function would be =concatenate(A1, " ", A2, " ", A3). Quotation marks display text and the space between the two quotation marks indicates a space in the result.

Answer: B$3

Normally, dragging a cell one space down would change only the row, not the column, which means normally the formula would be B4. However, when the $ is placed in front of a row or column, it means the row or column should not be changed. Moving a cell with the formula =B$3 down (or up) will keep the 3 the same. If the function is $B3, then that means the B will never change. $B$3 means that both the B and the 3 will never change. $B$3 is referred to as an absolute reference; B$3 and $B3 are referred to as mixed references; and B3 is referred to as a relative reference.

Answer: 0

The mod function displays the remainder of an equation. The 2 indicates that the number in A1 should be divided by 2. Twelve divided by two equals six with a remainder of zero. Therefore, =mod(A1, 2) will show a result of 0.

Answer: iserr

The iserr function will return TRUE if there is an error in the cell indicated and FALSE if there is no error. If A1 = 3/0, and B1 = iserr(A1), then B1 will display TRUE, since numbers can't be divided by zero. Another type of error is a circular reference, where a cell with a formula in it is being involved in the calculation, either directly or indirectly.

For example, if A1 = SUM(A1:A3), then A1 will be an error, and B1 will display TRUE. Also, if A1 = A2 and A2 = A1, then that would result in a circular reference.

Answer: Question Six

The trim function trims extra spaces; it does not trim all spaces. Just considering the space between two words, an extra space is anything more than one space. It will trim the words down to only one space between characters. Since there is only one space between the characters, the trim function won't change anything. If there are multiple spaces between "question" and "six", then trim(A1) will remove all but one of those spaces.

Answer: =round(833, -2)

The round function shows a rounded decimal to the number of spaces indicated. The second number in the round function is the specified number. =round(A1, 2) will round A1 to 2 decimal places, =round(A1, -2) will round A1 to -2 decimal places, which is the same as being rounded to the nearest hundred. =round(833, -2) will give an answer of 800.

The round function follows normal rounding rules, so if the function was =round(855, -2), then the answer would be 900.

Answer: 9

The odd function rounds a number to the next highest odd number. In this case, the next highest odd number is 9. Even if the number was 7.1, the cell will still display a value of 9, since it always rounds up. Likewise, the even function rounds a number to the next highest even number.

Answer: =median(A1:A5)

The correct formula is =median(A1:A5). The median function displays the median number in a set of numbers. The median number is the middle number when the set of numbers is listed in ascending order. However, the numbers themselves don't need to be ascending, Excel will do that when calculating the median function.

However, the cells themelves won't change when Excel puts the numbers in ascending order.

Answer: Yes

Suppose you are on Sheet1, and you want the value of A1 in Sheet2. To do this, you need to first click the equal sign (since all formulas begin with an equal sign), then click on Sheet2 near the bottom of the screen. This will show Sheet2. Then, click on A1 on Sheet2. If this is all you want your function to do on this sheet, then you can press Enter, and Excel will return you to Sheet1 with the value of A1 in Sheet2 in the appropriate cell. If you delete this sheet, then a #REF message will be displayed.

There is a shortcut to get to the next sheet that doesn't involve clicking on the tab. To use this shortcut, press Ctrl + Page Down to go to the next sheet. (Pressing Ctrl + Page Up will take you to the previous sheet). However, this shortcut will not work when using a formula.

It is also possible to use a cell from a different workbook. Have both workbooks open at the same time. Click the equal sign, then go to the different workbook, click on the appropriate cell, and then click Enter. However, if you delete the workbook, then a #REF message will be displayed.