5 Custom Excel Functions To Try
With versatility as one of it’s strong points, Microsoft Excel allows users to perform a series of custom excel functions on their excel spreadsheets. Do you know what these functions are? Discover them here and try them out for yourself today!
- Character Replacement
One of the most convenient custom excel functions would be character replacement, where users are able to replace certain specific characters with numbers. In order to do so, users can use IF, MID and REPLACE functions. The formula users can use should resemble something like this:
[ =IF((MID(__,_,_))=”_”,REPLACE(__,_,_,_), __) ]
This is to let Microsoft Excel know which characters are to be replaced and what they are to be replaced with. The formula can be tweaked according to position and characters but once done correctly, the replacements will be smooth and seamless.
- COMBINA function
Another useful feature out of all custom excel functions would be the COMBINA function. This function allows users to return number of combinations (with repetitions) for a given number of material. The formula to be entered would be: =COMBINA(__,__). When applying this to cells, it will reveal the number of combinations the user can create.
- SKEW.P function A function first introduced in Excel 2013, SKEW.P (=SKEW.P(___)) returns skewness of distributions that are based on populations. This functions well when it comes to applying it to data sets, though users do have to take note that if there are invalid values that are referenced, there would most likely be errors in the form of #NUM! Error. Some other error types to look out for would be data type ( #VALUE! Error and #DIV/0! Error).
- User Defined FunctionsMicrosoft Excel also allows users to create user defined functions from other workbook files through the use of VBA using the formula: [=OtherWorkbookName.xls!Functionname(__) ]. There are also specific add ins that allows users to reference functions without having to create references if they are installed.
- RAND function
Keep track of your data and adjust data values with the help of RAND function, a function that notifies and allows users to gain access of random numbers in their data sets with every change they make to their data. Users can retrieve random numbers between a data range. RAND functions can be used in different formulas, for example, when users want to track random numbers that are greater/equal to 0 but less than 1, it would look like =RAND() but if the range is less than a certain value, would appear like =RAND()*__.