A to Z of Excel Functions: Week one quiz answers

On Sunday I posted a quiz to see what you’ve learned so far in Week One.  Answers are below:-

1.   Which function can make a good replacement for nested IF functions?

CHOOSE.  Follow the link for an example

2.   Which function would you use to round 18 to the nearest multiple of 25, and how would you go about doing it?

You would use the CEILING function.  To round 18 to the nearest multiple of 25 you would do this:-

=CEILING(18,25)

3.   What would the result of the following function be:

=AND(5>7, 7>8, 8<9)

Because 5 isn’t greater than 7, and 7 isn’t greater than 8, these arguments would return FALSE.  As the AND function needs all logical tests to return TRUE for it to follow suit, the function would give a return of FALSE.

4.   What ASCII character is represented by the number 75, and how would you show this on a worksheet using an Excel function?

The character with an ASCII number of 75 is ‘K’.  To find the corresponding character for an ASCII figure you use the CHAR function. Here’s how you’d do it:-

=CHAR(75)
=K

5.   If cell A5=10, A6=12 and A7=14, how would you use Excel to find the average?

Using the AVERAGE function, you’d type this:-

=AVERAGE(A5, A6, A7)

or..

=AVERAGE(A5:A7)

..to give a value of 12!

That’s it for this weeks quiz.  If you didn’t get them all right check out the links in this post to the functions mentioned.  See you on Sunday for another test!!

2 Comments

  • Pingback: A to Z of Excel Functions: Week one quiz | 2toria

  • Patrick
    June 19, 2012 - 9:43 pm | Permalink

    Small quibbles with two of the answers.

    :)

    1) CHOOSE could be an option, depending on how your original nested IFs were done, but there are other options too. VLOOKUP or HLOOKUP spring to mind, and possibly also INDEX/MATCH. The right choice depends on the nature of the problem

    2) CEILING is the right choice if you always want to round up, but that’s not quite what you asked. If you want to round to the nearest, then MROUND might be a better choice, although it requires the Analysis ToolPak for Excel 2003 and earlier, and cannot handle negative numbers. You could also use:

    =ROUND(18/25,0)*25

  • Leave a Reply

    Your email address will not be published. Required fields are marked *