Microsoft Excel is a spreadsheet program. In Computer Science, a spreadsheet program is used for calculations involved in budgets, dashboards, forecasting, etc. A spreadsheet program creates a ledger like worksheet where different formulas and expressions are used to interpret data in meaningful ways. This makes a spreadsheet program like that of Microsoft Excel, uniquely important for both organizational and personal purposes.
Today, we are going to learn about some basic functions
which can help us get quickly started for Microsoft Excel.
SUM
Function
In Microsoft Excel, SUM function is very often used function to add quantities. Here’s how do we do that.
For example, in the picture as we can see, a function in Microsoft Excel, always starts with sign “=”. Then, we write the name of the function, and define its parameters. So here in this case, “=SUM(A1:A6)” represents the sum of all quantities, or numbers from cells A1 to A6. And, the results are displayed in cell A9 because, we have placed the function in cell A9 itself.
SUMIF
Function
Suppose, you want to find out sum of only those numbers in a
dataset, which satisfy a particular criterion. In such a scenario, we use SUMIF
Function. Here’s how.
So, as you can see in the above screenshot, the SUMIF function in this case adds the SUM of all numbers in the given data, which are greater than 25. But what is the data is not so simple. What if, the criteria is for some other column and sum operation is for a different column of the data. Here is how we do it, in the next example.
Now, let’s extend our data for fruits. So here we have 10
oranges, 20 apples, 30 bananas, and so on. Now, let’s suppose, we want to find
the SUM of only strawberries. Then, in the first parameter, we select range for
fruits, and in second parameter we filter out strawberry rows. In the third
parameter, we find out the sum of selected rows from column B. As illustrated
in the following example, we have sum of strawberries.
SUMIFS
Function
Now let’s extend our discussion to multiple criteria. If we
want to find the SUM in a selective fashion based on multiple criteria, then
here’s how we do that.
Now, in the first column we have name of the fruit, and in
second column we have color of the fruit. Next, to them we have, quantity of
the fruits. And, we want to find the sum of quantity of strawberries which are
red in color. So, first criteria is Range of Numerical Quantity, then we have
filtering criteria range, then the criteria, and then the criteria
corresponding to that. Further, we have next filtering criteria range and
corresponding criteria.
To understand, we see that, firstly we select columns, in
which sum will be calculated which is Column C. Then, Column A is First range
and the criteria is Strawberry. Then Column B is clearly depicted as Second
range and corresponding criteria is red color.
After doing this, the final Sum is calculated and displayed
by Excel as shown in the following illustration.
COUNT
Function
COUNTIF
Function
Similar to SUMIF function, the COUNTIF Function is used to
count cells containing numbers which satisfy a specified criterion.
COUNTIFS
Function
Taking our learning to next level, we have the COUNTIFs
function. The COUNTIFS function has shares its qualities with SUMIFS function.
The only difference is that it counts cells which contain numbers in a selected
range which satisfy multiple criteria.
From the above illustration, we can clearly understand the usage of COUNTIFS function. From Column A, we have selected “Car”, from Column B we are selecting numbers greater than 10, and as a result we have number of cells which have “Car” and value greater than 10.
IF
Function
IF Function is used to created values depending on certain criteria. As given in the example below, If the adjacent left field value is greater than 50, i.e., the marks of the student is greater than 50, we have two outcomes, Passed or Retest.
No comments:
Post a Comment