Quick Links
Microsoft Excel offershundreds of functions. So, there’s bound to be at least a handful you don’t know exist. These unique functions have specific purposes that you’ll be thrilled to learn about and use.
Related:12 Basic Excel Functions Everybody Should Know
FLOOR and CEILING for Rounding
You can use the FLOOR and CEILING mathfunctions for roundingtowards or away from zero by a specified multiple. Use FLOOR to round down and CEILING to round up.
The syntax for each is
and
where both arguments are required.
To round 4.4 down to the nearest multiple of 2, you’d use the following formula:
To round 5.6 up to the nearest multiple of 2, you’d use this formula:
MODE.SNGL for Finding Repetitive Values
Originally simply theMODE function, Microsoft created a newer version of this statistical function for improved accuracy. Use MODE.SNGL to find a single frequently recurring number in an array or cell range.
The syntax isMODE.SNGL(array1, array2, …)where only the first argument is required. You can use numbers, names, arrays, or references that contain numbers. Use the optional argument(s) for additional cell ranges.
Here, we look for the repetitive number that appears the most in cells A1 through A5.
To find a recurring number in A1 through A5 and C1 through C5, you’d use this formula:
CONVERT for Converting From One Measurement to Another
For a useful engineering function, you canuse CONVERTto change a value from one measurement system to another.
Related:How to Convert Almost Any Unit in Microsoft Excel
The syntax isCONVERT(value, from, to)where you’ll need all three arguments. For thefromandtoarguments, you’ll use an abbreviation. Check theMicrosoft Support site for the abbreviationsyou need for weight and mass, distance, time, pressure, force, energy, power, magnetism, temperature, volume, area, information, and speed.
To convert the value in cell A1 from Celsius to Fahrenheit, you would use this formula:
To convert the value in cell B1 from centimeters to inches, use this formula:
DELTA for Testing Equal or Not Equal Values
Another engineering function that’s useful is DELTA. With it, you’ll use theKronecker deltafunction to test whether two values are equal. Different than theEXACT function, the result is either 1 (true) or 0 (false).
The syntax isDELTA(value1, value2)where only the first argument is required and can be a number or cell reference. If you leave the second argument blank, Excel assumes zero.
To test the values in cells A1 and B1, you would enter this formula:
To test the values 2 and -2, use this formula:
GESTEP for Testing Greater Than or Equal To a Threshold
One more engineering function you may find useful is GESTEP which allows you to test values that aregreater than or equal toa step (threshold). The result is either 1 (true) or 0 (false).
The syntax isGESTEP(value, step)where only the first argument is required and can be a number or cell reference. If you leave the second argument blank, Excel uses zero.
To test the value in cell A1 against step 4, you’d use this formula:
To test a value of 10 against step 12, use this formula:
ADDRESS for Finding the Location of a Cell
Let’s move on to a lookup function in Excel. To find the exact address of a cell, you’re able to use the ADDRESS function. This is convenient if you want an error-freereference to a cell.
Related:How to Cross Reference Cells Between Microsoft Excel Spreadsheets
The syntax isADDRESS(row, column, type, style, name)where only the first two arguments are required. Enter the row number for the first argument and the column number for the second.
The optional arguments are as follows:
To find the address of the cell in row 2, column 3, you’d use this formula:
To find the address of the same cell using an absolute row and relative column, you’d use this formula:
To find the address of the same cell in the sheet named Sheet2, use the following formula. Note that the commas represent the blank argumentstypeandstyle.
PI for the Value of Pi
If you need to use the value of pi for equations in your sheet, you can obtain it with the PI function.
The syntax is simplyPI()with no arguments. You can add moreelements to the formulaif you want to use the value for a calculation.
To return the value of pi, simply use the function’s formula including the parentheses:
To multiply the value of pi by 10, you’d use this formula:
ARABIC and ROMAN for Converting Numerals
Another math function you may find handy is for converting to and fromArabicandRoman numerals.
The syntax for each isARABIC(text)andROMAN(value, form)where the first argument is required for each.
The optional argument for the ROMAN function specifies the type of Roman numeral from Classic to Simplified. Enter the number 0, word TRUE, or omit the argument for Classic. Use a 1, 2, or 3 for a more concise result. Or, enter the number 4 or the word FALSE for Simplified.
To convert the Roman numeral MMIM to an Arabic number, use this formula making sure to include the text in quotes:
To convert 2,999 to a Roman numeral, you’d use this formula:
To convert the same number in Simplified form, use one of these formulas:
REPT for Entering Repeating Text
If you want to add a series of characters, symbols, or text as a placeholder or for a visual effect, use the REPTtext function.
Related:How to Add Text to a Cell With a Formula in Excel
The syntax isREPT(text, number)where both arguments are required. Enter the text argument within quotes and then the number of times to repeat that text.
To repeat the word Excel 10 times in a cell, you’d use this formula:
To repeat an asterisk 20 times, you’d use this formula:
We’ve covered manyExcel functionsat How-To Geek and try to walk you through using the most common options. Hopefully one of these out-of-the-ordinary functions is exactly what you need for math, engineering, statistical, lookup, or textual data.
Basic·Budgeting·Data Entry·Logical·Text·Time and Date
Explained
Copying Formulas·Evaluating Formulas·Finding Functions·Fixing Formula Errors·Functions vs Formulas·Comparing Lookup Functions·Locking Formulas·Structuring Formulas·Translating Formulas
