FREE! Click here to Join FunTrivia. Thousands of games, quizzes, and lots more!

Quiz about What Does this Excel Formula Do

What Does this Excel Formula Do? Quiz

Do you know your stuff? Well, this quiz will test your knowledge in Excel formulas. Formulas tested in Excel 2003.

A multiple-choice quiz by Cyclonic. Estimated time: 5 mins.

Author
Cyclonic

Time
5 mins

Type
Multiple Choice

Quiz #
194,403

Updated
Jun 04 23

# Qns
10

Difficulty
Average

Avg Score
7 / 10

Plays
1672

One at a Time - Single Page - Timed Game

Question 1 of 10

1. =IF(E6=G6;"Nice";"Not so Nice")
Hint

Creates borders around cell G6

Checks if E6 equals G6 and if it does, the result is true and the formula returns 'Nice'

Sums up the range E6 to G6 and if the sum is greater than zero then, the formula returns 'Nice'

It changes your font color to Red

NEXT>

Question 2 of 10

2. Okey, next up..

=SUMIF(G3:G11;2)
Hint

Sums up range G3 to G11 showing the total with two decimals

Sums the number of cells within the range G3 to G11 which have the value two (2)

Sums up range G3 to G11 if the value in cell (G3) is two (2)

Counts the number of cells within the range G3 to G11 which have the value two (2)

NEXT>

Question 3 of 10

3. What does this do then?

=NOW()
Hint

It returns the current date and time

It tells you when it is time to go home from work

It returns information on the properties of the workbook you are working with

NOW stands for, Notes On Workbook and adds a comment to the active cell

NEXT>

Question 4 of 10

4. How about this one?

=CONCATENATE(F22;" ";G22)
Hint

It returns the cell value of G22 if cell F22 is blank

It changes the format of cells F22 and G22 to text

It subtracts the cell value of F22 from the value of G22

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

NEXT>

Question 5 of 10

5. The next could be useful and is a little tougher...

=VLOOKUP(I21;K21:L25;2;FALSE)
Hint

The formula adds the value of cell I21 to the first blank cell in array K21 to L25

The formula looks for the exact value of cell I21 in the range K21 to K25 and returns the corresponding value in range L21 to L25

The formula multiplies the value of cell I21 and the range K21 to L25 with the value two (2)

The formula returns the product of the cell range K21 to L25 if all values are numeric

NEXT>

Question 6 of 10

6. I had to think there for a while... next!

=MAX(G4:G9)
Hint

Sorts the values within the range in ascending order

Sorts the values within the range in descending order

Change letters from lower-case to upper-case within the range of cells

Returns the highest value within the range of cells

NEXT>

Question 7 of 10

7. What will this one do?

=LEN(G10)
Hint

This formula will return the natural logarithm of the value in cell G10

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

This formula will convert all characters in cell G10 to lowercase

This formula will return the total number of minutes and seconds you have been working with the active workbook

NEXT>

Question 8 of 10

8. How about this one...

=INFO("numfile")
Hint

Returns the version of your operating system

Returns the version number of MS Excel

Returns the number of active worksheets in all open workbooks

Displays your logon id

NEXT>

Question 9 of 10

9. Here is a combined one

=IF(ISBLANK(G5)=TRUE;(IF(ISERROR(J5-J6)=TRUE;"Error!";(J5-J6)));G5)
Hint

The formula verifies that cell G5 is blank and if it is not, it returns the result of cells J5-J6

The formula checks if G5 is blank and if it is, it returns the result of cells J5-J6 if it is numeric. Otherwise it will return "Error!"

The formula checks the result of J5-J6 and if it is in error (non-numeric) it returns the value of G5

The formula checks if G5 is blank and if it is, it returns the value of cell G5

NEXT>

Question 10 of 10

10. Last one...

=SUBTOTAL(109;H2:H9)
Hint

Returns the standard deviation of all cells within the range H2 to H9

Returns the sum of all visible cells within the range H2 to H9

Returns the average of all cell within the range H2 to H9

Returns the count of all visible cells within the range H2 to H9

(Optional) Create a Free FunTrivia ID to save the points you are about to earn:

Select a User ID:

Choose a Password:

Your Email:

Quiz Answer Key and Fun Facts

1. =IF(E6=G6;"Nice";"Not so Nice")

Answer: Checks if E6 equals G6 and if it does, the result is true and the formula returns 'Nice'

IF is probably the one of the more useful formulas since you can combine it freely with other formulas. IF basically checks if your condition is met or not. It returns TRUE or FALSE.

2. Okey, next up.. =SUMIF(G3:G11;2)

Answer: Sums the number of cells within the range G3 to G11 which have the value two (2)

SUMIF sums up your range of cells if a condition has been met. A condition could be a constant value or a cell reference. A similar formula is the COUNTIF which counts the number of cells that meet your condition.

3. What does this do then? =NOW()

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.

4. How about this one? =CONCATENATE(F22;" ";G22)

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.

5. The next could be useful and is a little tougher... =VLOOKUP(I21;K21:L25;2;FALSE)

Answer: The formula looks for the exact value of cell I21 in the range K21 to K25 and returns the corresponding value in range L21 to L25

VLOOKUP is a powerful formula if used correctly.

VLOOKUP looks for the value of cell I21 in the leftmost column of the cell range. In the question that will be the K-column. The formula then returns a value in the same row from a column you specify in that cell range. In the question we where looking for the value of cell I21 in the cell range K21 to L25 and we wanted to return the value from column 2, the L-column.

HLOOKUP and LOOKUP can be used in a similar fashion.

6. I had to think there for a while... next! =MAX(G4:G9)

Answer: Returns the highest value within the range of cells

The opposite of MAX is MIN and MAX always return the largest value within the specified cell range. The formula ignores blank cells or for example cell with text.

7. What will this one do? =LEN(G10)

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

8. How about this one... =INFO("numfile")

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.

9. Here is a combined one =IF(ISBLANK(G5)=TRUE;(IF(ISERROR(J5-J6)=TRUE;"Error!";(J5-J6)));G5)

Answer: The formula checks if G5 is blank and if it is, it returns the result of cells J5-J6 if it is numeric. Otherwise it will return "Error!"

A combined formula could be powerful and with this example you can make sure you only have numeric values in a column.

ISBLANK is true if a cell is empty. ISERROR checks if a cell is in error, for example returns #N/A, #VALUE!, #DIV/0! etc.

10. Last one... =SUBTOTAL(109;H2:H9)

Answer: Returns the sum of all visible cells within the range H2 to H9

There are 22 functions available to use with the SUBTOTAL formula. 1 and 101 will, for instance, do an average for you.

Source: Author Cyclonic

This quiz was reviewed by FunTrivia editor crisw before going online.
Any errors found in FunTrivia content are routinely corrected through our feedback system.