Lesson 11: Math formulas in excel

How to Work With Excel Math Formulas!

This video is a part of free Excel Training Singapore, where I show you how to use Math Formulas in Excel from the basics and also cover a lot of advanced Features.

Mathematical formulas in excel consists of predefined functions such as RAND, INT, SUMPRODUCT, SUMIFS as many more. These functions take digits as their input to perform the function on them and return the output.

Uses and benefits of math formulas

Microsoft excel gives it’s users the benefit of using various mathematical formulas inbuilt in excel’s memory. These formulas help reduce human load and produces error free data. Most commonly used formulas are mentioned below –

• INT – INT stands for integer. This function returns the integer part of the number. It takes only one input that is a number.
• MOD – MOD is another mathematical function that returns only the remainder when a number is divided by another number. It takes two numbers as its inputs. In case of negative inputs, the remainder is displayed along with the sign of denominator.
• RAND – Rand is the short form used for the mathematical function random. This function doesn’t takes any argument as the input. It just generates the output as any number from 0-1.
• RANDBETWEEN- RANDBETWEEN stands for random in between two given values. This function takes input as two numbers and gives output as any number between the two given numbers.
• Round- This function is used to round off the given inputs to nearest integer. The number of decimal places till where you wish to round off your data must be specified. This function takes inputs as two numbers.
• Sum – the sum mathematical function is used to find the sum of given inputs. It is one of those functions that can take as input a series of inputs comprising of digits only.
• SUMIF – this mathematical function is used to add certain given numbers when the prementioned criteria is met. This function takes three inputs namely, range, sum range and criteria.

How to use the integer function?

Integer function in excel is denoted by the abbreviation INT. This function returns the absolute integer value of any number. To see how this function works, follow the following mentioned steps –

• Start by typing equals to symbol in any given data set where you want to use the int function
• Following the equals to, type INT followed by the input the single argument which is a number or just select the cell of which you want integer value. If you enter any text inside the brackets, it will display an error.
• Here, on pressing enter, the output to displayed is 12, which is the integer part of the number and the remaining 0.2 is omitted
• In case of a negative number, the output gets rounded of to the nearest largest integer

How to use the RANDBETWEEN function?

RANDBETWEEN stands for random values between two given numbers. This function generates the output as any number between the two mentioned numbers. To understand how to use the function, follow the below steps –

• Start by typing an equal to sign followed by the function name RANDBETWEEN.
• Inside the brackets mention the two arguments. The output so generated will be any random number between the two given inputs
• You can also write data in a separate cell and add it’s cell references in the bracket to input the data.
• In case of negative digit inputs, the processing of data takes place in the same manner and the output is displayed between the inputs only. The only caution that needed to be taken care of is that the bottom value input must not be greater than the top value

How to use the SUMIF function?

The Sumif mathematical function is used to add numbers when a certain criterion is met. The three inputs consist of range, sum range and criteria. To understand how these works, follow the following mentioned steps –

• Start by typing the equals to sign followed by the function name which is SUMIF. Inside the bracket fulfill the three requirements by filling in the range, criteria and sum range.
• This function tends to ignore any texts and empty cells in the sum range. If the criteria range and sum range lengths are different then it takes the criteria range into consideration.
• To input the cell range, users can select the cell references of the entire data and perform further actions
• Wild card characters can also be used in the criteria input . Select the cells that have text contained in them for the range input
• For the criteria input, put in any condition as shown in the example below. And then select the cells with digits as the sum range.
• Press enters. The given condition will be checked and the result will be displayed accordingly.

Learn Microsoft excel in Singapore!

Microsoft excel is one of the most popular and useful devices used in all walks of life. To equip yourself with absolute knowledge of Microsoft Excel, join a best microsoft excel courses today.

In this tutorial of this Free Excel Training Singapore, you will learn about the MATH formulas

• INT function
• MOD function
• RAND function
• RANDBETWEEN function
• ROUND function
• SUM function
• SUMIF function
• SUMIFS function
• SUMPRODUCT function

This video is a part of Excel Training Singapore, where I show you how to use Excel from the basics and also cover a lot of advanced Features.