Getting Started with Microsoft Excel and Basic Commands

 


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

The Count function in Excel is used to find number of cells in a selected range, which contain numbers.

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