We have split the answers to the 2021 Christmas pantomime function name hunt across two articles to make the list of functions more manageable and to extend the suspense. Overall, we reckon there were 70 functions to find but we might have missed a few.
We have included a list of the functions with brief descriptions of their purpose and, where applicable, links to Excel Community articles that cover the use of the function. In part 1 we look at the heading and first two paragraphs:
|
AladdIN FOr Ever The last TIME we met MAX aNd MIN they were baTtling a plague of voracious crimson beetles. After a battle lasting many DAYS, the beetles were fiNAlly vanquished, anD MAX AND Min resumed their idyllic rural lIFestyle in their SMALL house on the outskirts of WindSOR. Three YEARs went by anD MIN thought it was time for aNOTher adventure. Towards the end of that YEAR, FRACking started in a nearby FIELD. VALUEd agricultural land was put at risk and a LAMB DAngerously upset but, far worse as it turned out, a LARGE crack appeared at the entrance to an ancient mine-working near the site. At night, strange sounds coulD Be heard coming from the old mine. Although it was late autumn, Min's favourite fruit tree still had PEARS ON and she had gone out to SEARCH for a couple for lunch. She managed to FIND one and thought that she had found a SECOND, but on closer inspection she noticed a MINUTE blemish. Whilst PIcking the pears, Min noticed hER Friend Dave walking across the UPPER path to the town. She branched off from the LOWER path to INTERCEPT him. Dave could sometimes be a bit HYPER, LINKed to the conSUMption of certain food colourings. When Min caught up with him, DAVE RAGEd about the goings on at the abandoned mine. Min said that she had to ROUND UP the goats but promised to meet Dave by the mine entrance in an HOUR. |
Where two functions are shown for the same number, they are alternatives.
| Number | Function | Description |
| 1 | INFO() | Displays information about the 'operating environment'. E.g. the current recalculation mode |
| 2 | TIME() | Converts hours, seconds and minutes to a serial time |
| 3 | MAX() | Returns the highest of its arguments, ignoring logical items and text |
| 4 | N() | Converts non-numbers to numbers. E.g. TRUE to 1, text to 0. |
| 5 | MIN() | Returns the lowest of its arguments, ignoring logical items and text |
| 6 | T() | Converts non-text to blank |
| 7 | DAYS() | Returns the number of days between dates |
| 8 | NA() | Returns the value #N/A |
| 9 | DMAX() | Returns the highest value in a database column subject to criteria |
| 10 | AND() | Returns TRUE if all arguments are TRUE |
| 11 | IF() | Checks a statement and returns one value if the statement is TRUE or another value if FALSE |
| 12 | SMALL() | Returns the kth smallest value in a list (for example: the fifth smallest value) |
| 13 | SORT() | Dynamic Array function that returns a sorted array |
| 14 | YEAR() | Returns the year part of a date |
| 15 | DMIN() | Returns the lowest value in a database column subject to criteria |
| 16 | NOT() | Makes a TRUE value FALSE and a FALSE value TRUE |
| 17 | YEARFRAC() | Returns the year fraction based on the number of days between two dates |
| 18 | FIELDVALUE() | Returns a particular value from a field of a record |
| 19 | LAMBDA() | Allows a calculation to be allocated to a Range Name to create a custom function |
| 20 | LARGE() | Returns the kth largest value in a list (for example: the fifth largest value) |
| 21 | DB() | Calculates depreciation using the fixed declining balance method |
| 22 | PEARSON() | Returns the Pearson product moment correlation coefficient |
| 23 | SEARCH() | Finds the position of one text string within another text string (not case-sensitive) |
| 24 | FIND() | Finds the position of one text string within another text string (case-sensitive) |
| 25 | SECOND() | Returns the number of seconds from a time value |
| 26 | MINUTE() | Returns the number of minutes from a time value |
| 27 | PI() | Returns the value of Pi |
| 28 | ERF() | Returns the error function |
| 29 | UPPER() | Converts a text string to UPPER CASE |
| 30 | LOWER() | CONVERTS A TEXT STRING TO lower case |
| 31 | INTERCEPT() | Calculates an intersection point using best-fit regression |
| 32 | HYPERLINK() | Creates a hyperlink with a location and separate text display |
| 33 | SUM() | Returns the total of its arguments |
| 34 | DAVERAGE() AVERAGE() |
Returns the average value in a database column subject to criteria Returns the arithmetic mean of its arguments |
| 35 | ROUNDUP() | Rounds a value up, away from zero |
| 36 | HOUR() | Returns the number of hours from a time value |
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.
Join the Excel Community
Do you use Excel in your organisation? Are you using it to its maximum potential? Develop your skills and minimise spreadsheet risk with our Excel resources. Membership is open to everyone - non ICAEW members are also welcome to join.
