Excel IF Functions.
Excel has several functions that can be used for various mathematical, analytical, and data manipulation tasks. Gaining proficiency with these features can significantly improve your ability to use Excel for data analysis and manipulation.
Get Mohini Kathrotiya’s stories in your inbox
Join Medium for free to get updates from this writer.
Following are some of the functions that aid with data analysis and manipulation:
- SUMIF(): It is used to sum the values in a range that meets specific criteria. It allows adding of values based on a single condition. It takes three main arguments (range, criteria, [sum_range]). It sums values in a specified range based on a given condition, optionally specifying a range to calculate if the condition is met.
Consider the following table of Pear Compay Quater 1 Expense to calculate the SUMIF(), SUMIFS(), and AVERAGEIF():
In the cell, we want the sum of the condition we start with = and then select the SUMIF() function.
We want the total expense of the category software. So, we start by defining the range which is the Category range from C5:C62, and then giving the criteria which are for all software categories so selecting any cell with software category in this case C9, then lastly selecting the sum_range of Total Expenses column G5:G62.
The output of the SUMIF() function is as follows:
2. SUMIFS(): It computes the sum of cells in a range that satisfies several criteria, each stated in a corresponding range, enabling the simultaneous application of increasingly complicated conditions. It takes the following arguments (sum_range, criteria_range1, criteria_range2,…..).
For the same table, in the cell, we want the sum of expenses for East Division and Software Category, so we start with = and then select the SUMIFS() function and mention the conditions.
We need the Total Expenses broken down into several categories, such as Division East and Software. Hence, we begin by defining the sum_range, which is found in column G5:G62. Next, we select any cell with East Division, such as B8, using the criteria_range1 from B5:B62. Since the second criteria is the category of software, the criteria_range2 for it would be C5:C62. Since the second criteria is software, you might choose any cell that has the software category, such as C9. Depending on the data we are referring to, the criteria and criteria_ranges would vary. This is how it works:
The solution of the function would be:
3. AVERAGEIF(): It calculates the average of values within a range that satisfies a given criterion, and if the values vary from the original range, an additional range may be used to compute the average. It takes the following arguments (range, criteria, [average_range]), where the arguments in [] are optional.
For the same table, in the cell, we want the Average Expense for Rent category, so we start with = and then select the AVERAGEIF() function and mention the conditions.
We are interested in the Average Expenses for Rent Category. First, we choose the range to be the Category C5:C62 columns. Next, choose any cell that has Rent as the criterion in the category column. Then, average_range is an optional field, but we’ve chosen it since we want to see the average of our expenses after choosing the Total Expenses G5:G62 column. This is how it works:
The solution of the function would be:
Thank you for reading!!
