If you appreciate the humor of the Excel Function Wall Clock, then be sure to check out our line of EXCEL GURU products!Here is the number-by-number explanation of the clock:
- =MIN(1,10) – Start off easy – the =MIN() function returns the minimum of the arguments passed to it. In this case, the minimum of 1 and 10 is 1.
- =MONTH(23790) – Excel stores dates as the number of days since January 1, 1900. This particular date serial number is February 17, 1965 (please- no expensive birthday gifts are necessary for MrExcel…). If you have a column of dates, you will often want to extract the year or month of the date. Using the =MONTH() function will return the month number of that date. In our case, February is a 2
- =INT(PI()) – This concept for the clock started when I thought about using =PI() instead of the 3 on a clock. Except, since =PI() returns 3.141579, I figured I would have to put this function about 1/7th of the way between the 3 and 4. Then, I realized that I could use the INT() function to chop of the decimals and keep only the 3
- =LEN(“FOUR”) – A great text function is =LEN(). It will return the length of any text string. Most of the time, this may not be that useful, but you will find it comes in handy for getting the 2 character state code from a bunch of addresses that look like “Akron, OH 44313”. Use =MID(A2,Len(A2)-7,2). In this case, since the word “FOUR” has 4 characters, the result of =LEN(“FOUR”) is 4
- =SQRT(25) – You can take the square root of any number using =SQRT(). The Square Root of a number is a number than when multiplied by itself will yield the original number. In this case, 5×5=25, so the square root of 25 is 5
- =FACT(3) – The FACT() function returns the Factorial of a number. The Factorial of 10 is 10x9x8x7x6x5x4x3x2x1. This function is great for statisticians calculating combinations and permutations. In our case, the Factorial of 3 is 3x2x1 or 6
- =GCD(77,49) – I remember there was some huge, complex, long manual formula for figuring out the Greatest Common Divisor when I was in 7th grade math with Mr. Irwin. If only Microsoft would have invented the Analysis Tool Pack in 1977, that entire week of calculating greatest common divisor could have been replaced with a couple of =GCD() functions. The greatest common divisor of 77 and 49 is 7
- =2^3 – Did you know that you can have an exponent in Excel by using the ^ sign? This equation is 2 raised to the third power, which is 2x2x2. This is sort of a complicated way to get 8, but you can calculate the expected return on $100 invested in a passbook savings account earning 4% interest for 18 years with =100*1.04^18. In our case, 2x2x2 is 8
- =PMT(9%,9,-53.96) – The most useful of the loan functions is the =PMT() function. If you borrowed $53.96 from the bank to buy some MrExcel books and had to pay it back over 9 years with a 9% interest rate, each yearly payment would be $9.
- =LCM(2,5) – Here is another function from the analysis tool pack that could have shaved a week off of 7th grade mathematics. The Lowest Common Multiple of 2 and 5 is 10
- =ROMAN(2) – Did you know that you can calculate roman numerals in Excel? Enter =ROMAN(1981) in Excel and the producers of “An American Werewolf in London” would know that their copyright line at the end of the credits should say MCMLXXXI. Did Microsoft add this function in Excel just for movie makers? Who else uses Roman Numerals? Actually, if Kenneth Lay would have presented all of their financial statements in Roman Numerals, perhaps ENRON would have gotten away with it. So, if you have bad financial news to report to the boss, present your revenue forecast with the =ROMAN() function. Or, if you need a good function for 11 on your clock, then take =ROMAN(2), which, of course, is II. (pun intended).
- =COLUMN(L1) – You can’t have an Excel Function clock without one of those crazy Information functions. The COLUMN() function will tell you from which column a particular cell resides. In our case, =COLUMN(L1) will tell us that L is column number 12
Order the Excel Function Wall Clock today from the MrExcel store at CafePress.