ICAEW.com works better with JavaScript enabled.

The 2021 Excel Community pantomime – the answers part 2

Author: Simon Hurst

Published: 17 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 71 functions to find (we spotted DAY as well as DAYS since part 1) 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 2 we look at the final four paragraphs:

When they got to the mine, Dave asked MIN IF Something looked strange. As Min stared towards the entrance she thought she could see wisps of multicoloured smoke drifting out. She wasn't sure whether the smoke was real OR IMAGINARY so she walked towards the mine to take a better look. "That IS ODD" she exclaimed as she peered through the widening crack extending around a bouldeR AND BETWEEN the rocks at the entrance. Min thought it was time to call for reinforcements, so she sent a TEXT to Max. A few minutes later Min, MAX And Dave squeezed themselves through the crack and INTo the mine workings. Min saw something glistening on the FLOOR in front of them. "Wait a SEC" said MIN As she took a larGE STEP in order to pick it up. It was an ornate flask containing some sort of eliXIR, Reddy-brown in colour. Min wondered whether to wait for a BIT OR carry on and what would be the conSEQUENCEs of drinking the strange potion. She took STOCK, HISTORY suggesting that discretion would be the better part oF Valour in the circumstances.

As their eyes became acclimatised to the dim light, they realised they were sTANding in a dank CELL, next to the BASE of a ROUND COLUMN, one of a ROW of several COLUMNS supporting the CEILING. Suddenly, a small boy appeared carrying a lamp and gave it to Min. On the side of the lamp there was a DISC engraved with strange writing. "What does it mean?" asked Dave. "LET me have a look" said Max "I have several DEGREES in exotic languages". Max studied the lamp inscription carefully and then said "I can say with some CONFIDENCE that it reads: 'rub the side of the lamp three times and wish for the resolution of a major geopolitical PROBlem'".

Dave looked across at Max and asked where he thought that the POWER of the lamp came from. The more Min thought about Dave's power query, the more certain she became that it was the answer to all of their problems.

A few DAYs later, Max and Min were looking through the Sunday papeR AND saw that there had been a COUP, DAYS before in an oppressed part of the world. The coup had been successful and all those involved were certain to live happily ever after. At last, things had begun to LOOK UP.

Where two functions are shown for the same number, they are alternatives.

Number Function Description
37 MINIFS()

IFS()

Returns the minimum value from a range, based on criteria

Checks a set of statements and returns a value corresponding to the first TRUE statement

38 OR()
Returns TRUE if any arguments are TRUE
39 IMAGINARY()
Returns the imaginary coefficient of a complex number
40

ISODD()

ODD()

Returns TRUE if the value is odd

Returns the nearest odd integer up

41  RANDBETWEEN()
Returns a random number between an upper and a lower limit
42 TEXT() Convert a value to text and apply a specified number format
43 MAXA()
Returns the highest of its arguments, does not ignore logical items and text
44 INT()
Truncates a number to an integer
45 FLOOR()
Rounds down to the nearest multiple of significance
46  SEC()
Returns the secant of an angle
47 MINA()
Returns the lowest of its arguments, does not ignore logical items and text
48  GESTEP()
Tests whether a number is larger than a threshold value
49 XIRR() Returns the internal rate of return for a schedule of cash flows
50 BITOR()
Returns bitwise 'OR' of two numbers
51 SEQUENCE()
Dynamic Array function that returns a series of values
52 STOCKHISTORY() Returns historical quote data
53 FV()
Returns the future value of an investment based on periodic, constant payments at a constant interest rate
54 TAN()  Returns the tangent of an angle 
55 CELL()  Returns information about the top left-hand cell in a range
56 BASE()
Converts number to text representation with the given base (radix) 
57 ROUND()  Rounds a value to a specified number of digits
58 COLUMN()
Returns the column index number of the reference
59  ROW()
Returns the row number of the reference
60 COLUMNS() Returns the number of columns in a reference
61 CEILING()  Rounds up to the nearest multiple of significance
62 DISC()  Returns the discount rate 
63 LET()  Allows calculations to be allocated to named parameters that can be reused elsewhere within the function arguments
64 

DEGREES()

SIN()

Converts radians to degrees

Returns the sine of an angle

65  CONFIDENCE()  Returns the confidence interval for a population mean
66 PROB()
Returns the probability that values in a range are between two limits or equal to a lower limit
67 POWER()
Returns the result of a value raised to a power
68 DAY()
Returns the day of the month from a date
69 RAND()
Returns a random number greater than or equal to 0 and less than 1
70 COUPDAYS()  Returns the number of days in the coupon period that contains the settlement date
71 LOOKUP()
Looks up a value from a list or array

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