Most of us use Excel functions and are very much aware of quite a few ones like SUM, COUNT, AVERAGE, VLOOKUP, SUMIF etc. However, there are some of them which are not known to many.
CHOOSE(): This function picks one entity/value from a list, based on the index value given by the user.
Eg: =CHOOSE(4, “silver”, “gold”, “iron”, “bronze”) will return bronze
CLEAN(): This function removes any non-printable characters form the text, which is usually found in the data which is imported from elsewhere.
Eg: CLEAN(“text”)
CODE(): This function shows the ANSI value of a single character/first character in a piece of text.
Eg: =CODE(“A”) returns 65
DATEDIF(): This function calculates the difference between 2 dates and returns the value in days, months or years as specified by the user.
Eg: DATEDIF(“10-May-70”,20-Jun-70, “d”) will return 40
FREQUENCY(): This array function, used to analyse and group values, compares a range of data to finds out the entities which fall into specified bin intervals.
Eg: =FREQUENCY(A1:A5, A7:A9) where A1:A5 is the data array and A7:A9 is the bins array.
LARGE(): This function, similar to max, picks up the highest number from a list, but can also pick up the second highest or third highest numbers as per the specification of the user. Similarly, SMALL() is used to pick up either the smallest number or the first/second/third smallest number from the list.
Eg: =LARGE(u{345,324,456,768,600},2) would return 600 as this is the second highest value in the given array list.
Some functions are available only with the Analysis ToolPak add-in installed
BIN2DEC: This function converts a binary number to decimal. However, the binary number has a limit of 10 characters.
Eg =BINDEC(10) will return 2. Similarly, there are =BIN2OCT and =BIN2HEX functions also
CONVERT(): This function converts a given number from one unit to another.
Eg: =CONVERT(50, “kgs”, “g”) will return 50000.
DELTA(): This function compares two values and tests if they are exactly the same. It returns 1 only if both the numbers are equal. Works only with numbers.
Eg: =DELTA(5,4) would return 0.
EUROCONVERT(): Converts a number to euros, converts a number from euros to a euro member currency, or converts a number from one euro member currency to another by using the euro as an intermediary.
Eg: =EUROCONVERT(1.2, DEM,EUR) would return .61
EDATE(): This function is used to find out the date after/before a specified number of months in the past or future.
Eg: =EDate(“01-Jan-98”,3) would return 01-Apr-98.
GCD() and LCM(): This function is used to find the greatest common devisor(or the HCF) of the specified numbers. Similarly LCM() finds out the lease common multiple of the given numbers.
GESTEP() It tests a number and verifies if it is greater or equal to another number. If so, it returns 1, else 0
Eg: GESTEP(200,300) would return 0
NETWORKDAYS() This function calculates thenumber of working days between 2 dates, given the list of holidays in between.
Eg: =networkdays(“12-Jan-2010”, “27-jan-2010”,{15-Jan-2010”, “26-Jan-2010”}) would give 10 working days,(excluding the two specified holidays, 2 Saturdays and 2 Sundays). Similarly,. =WORKDAY((“12-Jan-2010”,10, ”,{15-Jan-2010”, “26-Jan-2010”}) would give the next working date, after 10 days as 28-Jan-2010.
YEARFRAC(): This function computes the difference between the 2 dates and gives the result as a decimal fraction
Eg:=YEARFRAC(“21-Feb-2010”, “20-Jul-2010”) would return .41
New functions in Excel 2007
SUMIFS() : An extension to SUMIF(), this function adds the values of the cells in the range that meet multiple (up to 127) criteria.
Eg=SUMIFS (C1:C5, A1:A5, ">05-may-90",B1:B5, “A”). [C1:C5 has list of dates while B1:B5 has list of letters]
COUNTIFS(): This is an extension to COUNTIF and can be used for counting the values in the range, instead of summation, provided multiple conditions are met.
IFERROR(): The is function is used to retrun a value, in case a formula evaluates to an error.
AVERAGEIF() : This function computes the average, only if a certain criteria is met.
For eg: = AVERAGEIF(A1:A10, “Soap”, B1:B10) [Here A1:A10 has the item list, including soaps, while B1:B10 has their prices.
Cube Functions:
Cube functions are a new class of functions in Excel 2007, which allow importing the data from an SQL database (OLAP cube); this data is then manipulated and used by the organizations to monitor the company’s turnover and the areas where it needs to develop.
These are: CUBEKPIMEMBER,CUBEMEMBER,,CUBEMEMBERPROPERTY,CUBERANKEDMEMBER,CUBESET, CUBESETCOUNT and CUBEVALUE