Microsoft Excel Trivia

1. If the word "alphabet" is in A1, then what does the function =mid(A1, 3, 4) display?

From Quiz Microsoft Excel Formulas

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".

From Quiz A Little Left of Center

Answer: thi

The left command takes the left part of the desired cell for the number of positions indicated. If the formula = Upper(left(a1,3)) had been used then THI would have been correct.

From Quiz Microsoft Excel Formulas

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.

From Quiz A Little Left of Center

Answer: two.

The right command gets the extreme right of the desired cell for the indicated number of characters which in this case includes the full stop.

From Quiz Excel-lent

Answer: 07-JAN-2021 10:43 (Incremented by one day)

To comprehend this, it is necessary to understand how Excel (and all other spreadsheets I have encountered) store dates. Dates are stored as an integer representing the number of days since an arbitrary start point. Usually, day 1 is the 1st January 1900 or 1st January 1904. Windows versions of Excel default to the former, those on Apple have traditionally used the latter. The principal advantage of the 1904 system is that a leap year occurs every four years for nearly two centuries whereas the former system in Excel fails to account for 1900 not being a leap year (apparently this is for compatibility with older spreadsheet programmes). Time is stored as a fraction of the 24-hour day, eg 0.25 would represent 6am. Adding 1 to a date increases the integer part, the number of days since day 0. In the quoted example, the new date is the next day 07-JAN-2021. The numbers to the right of the decimal point are unchanged, thus the time element is unchanged.

From Quiz Microsoft Excel Formulas

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.

From Quiz A Little Left of Center

Answer: three

The formula first asks to get the fourteen leftmost characters and then get the 5 rightmost of those.

From Quiz What Does this Excel Formula Do?

Answer: It returns the current date and time

Notes On Workbook... hehe... lol :) NOW returns the current date and time and the result of the formula will always be 'Volatile'. NOW can for example be used for counting down days to a deadline or just for keeping track of the current time.

From Quiz Tips and Tricks in Microsoft Excel

Answer: FV

The correct syntax for this formula is =FV(rate,nepr,pmt,pv,type) where rate is the interest rate per period, nper is the number of payment periods, pmt is the payment made per period (if any), pv is the present value or the initial deposit, and type indicates whether the interest is paid at the beginning or end of the month. FV stands for Future Value. =RAND is a function that returns a random number, =LN will return the natural log of a number and =MAX will return the largest number in a selected range.

From Quiz Excel-lent

Answer: INDIRECT

This might not seem very useful in itself, but as spreadsheets get bigger the usefulness of being able to treat (for instance) the names of other tabs in a workbook as a variable is seen. One of the strengths of spreadsheets is using the same formula down a range of cells. Changing the formula for every cell to adapt to changes is wasteful. A formula that accounts for different situations is always to be preferred. As an example, if I have a spreadsheet of my submitted FunTrivia Quick Questions, which includes tabs named Science, Literature and Geography, on each I have a calculation in cell A1 telling me how many questions I have submitted in that category. Here's an attempt at a simple example. On my summary page I start with the subjects Science, Literature & Geography in A1-A3 respectively I could write in B1 "=Science!A1", in B2 "=Literature!A1". But a more consistent way would be to use the formula in B1: =Indirect(A1&"!$A$1"). Then copying this formula down to B2 & B3. This can also be extended if more categories are added - the list in column A can be sorted and the formulas will still work correctly.

From Quiz Microsoft Excel Formulas

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.

From Quiz A Little Left of Center

Answer: rmat

The mid command advises to go in so many spaces and then give the desired amount. In this particular case the same result could also have been obtained by the simpler formula of =mid(f1,10,4)

From Quiz What Does this Excel Formula Do?

Answer: It returns the combined values of F22 and G22 separated by a single 'space'

CONCATENATE can be helpful when combining just cell values or a combination of cell values and constants. Unfortunately it does not work that well on cells with date formatting.

From Quiz Tips and Tricks in Microsoft Excel

Answer: Totalling a column or row

Select the cell where you want the total, and Excel will attempt to 'guess' which cells you want added.

From Quiz Microsoft Excel Formulas

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.

From Quiz Excel-lent

Answer: ROMAN

There is such a function and as an easy aide memoire it is called ROMAN. This is one function I have never used in anger as nothing I have worked on requires an output in roman numerals. Not surprisingly, it only works for positive values and rounds down to the nearest integer below, since only positive integers are representable in Roman numerals. Still the function is there for those who do need it. The other listed functions are imaginary at the moment but there are functions such as OCT2BIN which converts octal to binary.

From Quiz Microsoft Excel Formulas

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.

From Quiz A Little Left of Center

Answer: all answers are correct

There are often many ways of expressing the same thing.

From Quiz Tips and Tricks in Microsoft Excel

Answer: Equals sign, function name, matching brackets

Most functions also include an argument, eg, =MAX(C5:C12), will return the maximum value in the cell range C5 to C12. There are, however, several functions that do not require an argument for them to work. An example is =PI(), which will return PI to 15 decimal places.

From Quiz Microsoft Excel Formulas

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.

From Quiz A Little Left of Center

Answer: S TH

The upper command puts everything into capital letters, and spaces are counted as characters.

From Quiz What Does this Excel Formula Do?

Answer: This formula will return the number of characters of the value/string in cell G10

Yes, it counts the number of characters. It could be used for removing annoying incorrect minus signs. 6.000- which should be -6.000 can be fixed with =IF(ISTEXT(E17)=TRUE;VALUE(LEFT(E17;(LEN(E17)-1)))*-1;E17).

From Quiz Microsoft Excel Shortcuts

Answer: ALT E D

ALT E D takes you to a menu that allows you not only to delete the selected rows or columns but also to shift the just the highlighted section left or up without deleting the entire row or column. ALT E G lets you select a specific cell to go to. ALT N D allows you to add a scatter chart to your document while ALT N G allows you to enter a signature line where someone can provide a digital signature.

From Quiz Microsoft Excel Formulas

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.

From Quiz A Little Left of Center

Answer: , Lastname

This is actually not the desired result if one wishes to return the last name only as the formula is only finding where the comma is and would only work as intended if the same number of letters were in the first name and the last name. The function "proper" puts the string in proper name case, meaning only the first letter of each word gets capitalized.

From Quiz What Does this Excel Formula Do?

Answer: Returns the number of active worksheets in all open workbooks

Pretty useless, right? BTW, why does it return four sheets when I've only got one workbook open with three sheets standard? Well... my guess is that personal.xls (if you use it) counts as an open workbook with one active sheet.

From Quiz Tips and Tricks in Microsoft Excel

Answer: Rocky

'Links' is a purring kitten, 'The Dot' is, well, a red dot and Ruff is Dennis the Menace's dog. The ridiculous paperclip is also present in Excel, but please do not use it when I am around- it annoys the daylights out of me! The Office Assistant can be turned off by right-clicking on the animation, and then selecting 'Hide'.

This is category 21127
Last Updated Nov 16 2024 5:50 AM

= Top 5% Rated Quiz, Top 10% Rated Quiz, Top 20% Rated Quiz, A Well Rated Quiz
= added recently, = Editor's Pick = FunTrivia Editor = Gold Member
Teachers / educators: FunTrivia welcomes the use of our website and quizzes in the classroom as a teaching aid or for preparing and testing students. See our education section. Our quizzes are printable and may be used as question sheets by k-12 teachers, parents, and home schoolers.

 ·  All questions, answers, and quiz content on this website is copyright FunTrivia, Inc and may not be reproduced without permission. Any images from TV shows and movies are copyright their studios, and are being used under "fair use" for commentary and education.