Mathematical Functions In Excel – MS Excel Functions Tutorial Part 3

In previous posts, we learnt what are excel functions and formulas and how to insert formulas, functions and named ranges in excel. In this post, we will focus on Mathematical Functions in Excel.

Mathematical functions are used for performing arithmetic calculations. In this post, we will discuss the most important and common mathematical functions.

Function # 1 SUM

The sum function is used for addition. For e.g. if we wish to add the total number of hours for which our employees worked in a day we may use the SUM function.

The function SUM(E6:E9) will return the total no. of hours for which the employees worked.

Function # 2 PRODUCT

The product function is used for multiplication. Let us suppose that we have to calculate the daily wages of our employees.

We can calculate the daily wages by multiplying the per hour rate with the no of hours for which each employee has worked. The formula for this will be:

=PRODUCT (E7,$E$5)

E5 is an absolute reference in this case since the wage rate per hour is a fixed number. The formula takes different values for the number of hours worked (Column E). Therefore it is a relative reference by default.

Function # 3 SUMPRODUCT

This is a very important function introduced in excel 2007. This function multiples the values in one range of cells to the values in another range of cells and then adds them. To understand this function better let us consider a variant of our previous example. Let us suppose that after having calculated the wage/day of our employees we need to calculate the total amount of wages which we will have to pay considering all the employees together.

Assume that each employee works for stipulated number of hours each day. In order to calculate the monthly wages we have to multiply the employee wage/day with the number of days for which the employee worked.

Wage/month=wage/day*number of days for which the employee worked (in a month)

Now in order to calculate our total cash outflow for wages we have to add the wage/month of each employee. Our Sumproduct formula will be:

=SUMPRODUCT(F7:F10,G7:G10)

This formula tells excel to multiply the numbers in the range F7 to F10 by the corresponding numbers in the range G7 to G10. This means that the wage/day of each employee is multiplied with the number of days for which the employee worked each month. This calculates the monthly wage amount which has to be paid by the organization per employee. When these monthly wage amounts per employee are added we get the total amount of wages which the organization has to pay.

Function # 4 AVERAGE

This function calculates the average of a given range or values.  We have shown an example on using average function in part 1.

Function # 5 COUNT

This function finds the number of items in a list of items. Let us suppose that we have a product database which contains the names of products along with their sales amount(INR). Now let us assume that we need to find out the total number of items in the list.

For the sake of simplicity we have considered a small list. But if our list is large COUNT function can really be handy in finding the total number of items in the list of items. Our COUNT function will be:

=COUNT(E5:E12)

This formula tells excel to count the total number of items in the list and return the number. This function will return the value “8” since this list has 8 items.

Note: This formula will not work if we enter the range as D5 to D12 in the COUNT formula. The COUNT formula only works on numbers.

=COUNT(D5:D12) is INCORRECT and will return the value 0.

Function # 6 COUNTA

This function is similar to the COUNT function except that it counts everything including blank spaces, text etc. The COUNT function on the other hand counts only numerical values in a list. The COUNTA function is useful when we have a huge unformatted list which might have black cells, text in between and interested in knowing the total no. of elements in the list which includes numbers, text,  spaces etc.

Let us take the same example with little variations to understand how COUNTA works. Let us assume that our previous list is unformatted.

How COUNT works on this

COUNT function returns “8” here because it only counts the cells which contain numerical values. It does consider cells E7,E10 & E14 as these contain text.

How COUNTA works on this

COUNTA returns the value “11” as it counts the total number of items in the list including the cells which contain text.

Function # 7 COUNTIF

This function counts the number of times an item occurs in a list of items based on certain conditions. Let us assume that we have a small database which contains region-wise sales. Our list is unsorted here and we need to find out the number of times the region “North” appears in the list.

Let us use the COUNTIF function:

=COUNTIF(D6:D16,”North”)

This formula tells excel to traverse the range D6 to D16 and return the number of times when “North” occurs in this range.

This formula returns the number “5” which clearly indicates that “North” occurs 5 times in the list.

Function # 8 SUMIF

This function adds the numerical values in a cell range based on a certain criteria. Let us take our previous example with little additions. Let us suppose that we have one more column for “Product Name”. Now our aim is to find out the total sales of in the “North” region.

The formula for performing this operation will be:

=SUMIF(D6:D16,”North”,E6:E16)

This formula tells excel to traverse the range D6 to D16 and add the corresponding values in the Sales column wherever the criteria “North” is true.

This formula gives us the total sales in the “North” region as 55000 INR.

So we learnt how to use mathematical functions in excel. In next post, we will learn about text functions in excel.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">