Unleashing the Power of Excel Functions - II

18 Nov 2017
Excel Functions

Continuing with the previous post on excel functions , in this article we will learn few more important functions.

Important Excel Functions

--------------------------------------------------------------------------------------------------------------------

IFERROR

Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors produced by other formulas or functions. IFERROR checks for the following errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

SYNTAX

=IFERROR (value, value_if_error)

For example, if A1 contains 10, B1 is blank, and C1 contains the formula =A1/B1, the following formula will catch the #DIV/0! error that results from dividing A1 by B1:

=IFERROR (A1/B1,"Please enter a value in B1")

----------------------------------------------------------------------------------------------------

COUNTIFS

The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

SYNTAX

=COUNTIFS (range1, criteria1, [range2], [criteria2], ...)

Excel Functions Countifs

The difference between countif and countifs is that while countif is used to count the cells that meets a single criteria, countifs is used to count the cells that meets multiple criteria.

If you have an older version of Excel that doesn't have the COUNTIFS function, you can use the COUNTIF function instead like this:

=COUNTIF(range,">=X")-COUNTIF(range,">Y")

----------------------------------------------------------------------------------------

SUM/ SUMIF / SUMIFS

SUM function is used to add the values in a range

You use the SUMIF function to sum the values in a range that meet criteria that you specify.

The SUMIFS function adds all arguments that meet multiple criteria

SYNTAX

=SUM(range)

For example, the formula =SUM(C2:C5) adds the values in range C2:C5.

=SUMIF(range, criteria, [sum_range])

For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."

=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)

Example for SUMIFS with multiple criteria and OR logic is as below

Excel Function Sumifs

---------------------------------------------------------------------------------------------------------------------------------------

SUMPRODUCT

The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.

The purpose of the SUMPRODUCT function is to multiply, then sum, arrays. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. Up to 30 arrays can be supplied.

SYNTAX

=SUMPRODUCT (array1, [array2], ...)

    The purpose of the SUMPRODUCT function is to multiply, then sum, arrays. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. Up to 30 arrays can be supplied.

    We can use this function along with SUM function to get WEIGHTED AVERAGE.

    Weighted Avg using SumProduct and Sum

    Notes:

    1. SUMPRODUCT treats non-numeric items in arrays as zeros.
    2. Array arguments must must be the same size. Otherwise, SUMPRODUCT will generate a #VALUE! error value.
    3. Logical tests inside arrays will create TRUE and FALSE values. In most cases, you'll want to coerce these to 1's and 0's

    -------------------------------------------------------------------------------------------------------------

    AVERAGE / AVERAGEIF/ Average of top/bottom N values

    To get the average of a set of numbers, use the AVERAGE function.

    To get the average of a set of numbers, with some criteria, use the AVERAGEIF function.

    To average the top n scores in a data set, you can use a AVERAGE formula with LARGE function.

    SYNTAX

    =AVERAGE(RANGE)

    For example, to get the average of values in range C2:C5, use =AVERAGE(C2:C5)

    =AVERAGEIF(RANGE, Criteria)

    For example, to get the average of values, excluding Zero values in range C2:C5, use =AVERAGEIF(C2:C5, "<>0")

    =AVERAGE(LARGE(range,{1,2,..,n}))

    For example, to average the top 3 scores in a data set, use =AVERAGE(LARGE(B6:F6,{1,2,3}))

    -----------------------------------------------------------------------------------------------------------

    INDEX AND MATCH

    The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns.

    SYNTAX:

    =INDEX ( array row number )

    Index

     

    MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. 

    SYNTAX

    =MATCH (  lookup value lookup array match type )

    INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.

    Match
     

    INDEX MATCH Vs VLOOKUP

    VLOOKUP requires more processing power from Excel because it needs to evaluate the entire table array you’ve selected.  With INDEX MATCH, Excel only has to consider the lookup column and the return column.  With fewer absolute cells to consider, Excel can process this formula much faster.

    SYNTAX:

    =INDEX(Array, MATCH formula)

    Index-Match

    Please watch this section to see more on Excel functions and other Advanced Features.