In this post we are going to look at Formulas and Functions in Excel. Formulas and Functions is a core feature in Excel. We can use Formulas to perform different types of mathematical operations. Functions help to perform mathematical and logical operations in a much convenience way. In this example, we'll see some of the basic uses of Formulas & Functions in Excel.
Let's discuss about Formulas and Functions using a simple Exercise.
Open Excel. Create a new workbook and in the sheet 1 type the below data.
Let's add a new column next to the Unit Price column. Select the D1 cell and type "Line Total"
Now to calculate the line total we know what to do. Multiply the Quantity (QTY) by Unit Price.
Line Total = QTY * Unit Price
As in example, if you want to calculate the Line Total of Pens, multiply the QTY of pens by the Unit Price of pens.
In Excel, nothing that much different you have to do. Simply write this formula in D2 cell.
= B2*C2
It will display the value 50 (50*1.00).
Let's see each part separately.
The equal sign (=) - When you use the equal sign, Excel identifies the rest of the content as a Formula or Function.
Cell addresses (B2, C2) - formula will take the values from these cells.
Mathematical Operation (*) - perform the specified mathematical operation.
To calculate the next item Line Total, you can type =B3*C3.
Don't type one by one. Instead you can simply copy the formula to the rest of the cells. Click the fill handle of the D2 cell and drag down. Alternatively, double click on the fill handle. It will copy the formula accordingly to the rest of the cells in that column.
Next let's try using a function. We are going to use the SUM function for this example. Function is a tool that used to perform an operation on cell data. The SUM function is used to perform the summation operation on given cell data.
Let's try the SUM function to calculate the total of Line Totals. In cell D18, type the below function.
=SUM(D2:D16)
As in the previous formula example, let's try to understand each part in this function.
- The equal sign(=) - Using the equal sign (=) signals that this cell contains a formula or function
- The Function name - next to the equal sign (=) specify the function name. In this case it's SUM
- The pair of parentheses - next to the function name type a pair of parentheses. Every function in Excel has a set of parentheses
- Passed cell data - We provide cells to the function. In this caseD2 : D16. (D2 to D16) The function uses these cells to perform the operation.
Similarly there are some other functions we use frequently in Excel. Such as AVERAGE, MIN, MAX and COUNT.
First try out AVERAGE function. For this I'm going to calculate the average of Unit Prices. In C19 cell type the below function.
=AVERAGE(C2:C16)
This will display the average of unit prices.
Next check the MAX function. For this I'm going to get the maximum Unit Price. In C20cell type the below function.
=MAX(C2:C16)
Similarly check the MIN function. For this I'm going to get the minimum Unit Price. In C21cell type the below function.
=MIN(C2:C16)
Finally check the COUNT function. For this I'm going to count Unit Prices. In C22 cell type the below function.
=COUNT(C2:C16)
Try to get the MIN, MAX and Average of Line totals and QTY too.
Feel free to ask questions in the comment section. I'll try my best to answer them at my earliest convenience.