ICAEW.com works better with JavaScript enabled.

The 2021 Excel Community pantomime – the answers, part 1

Author: Simon Hurst

Published: 13 Jan 2022

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

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.

Excel