In this article, we will cover the following topics.
What is Formulas in Excel? Mistakes to avoid when working with formulas in Excel What is Function in Excel? The importance of functions Common functions Numeric Functions String functions Date Time functions V Lookup function
Tutorials Data
For this tutorial, we will work with the following datasets. Home supplies budget House Building Project Schedule
=A2 * D2 / 2
HERE,
“=” tells Excel that this is a formula, and it should evaluate it. “A2” * D2" makes reference to cell addresses A2 and D2 then multiplies the values found in these cell addresses. “/” is the division arithmetic operator “2” is a discrete value
Formulas practical exercise
We will work with the sample data for the home budget to calculate the subtotal.
Create a new workbook in Excel Enter the data shown in the home supplies budget above. Your worksheet should look as follows.
We will now write the formula that calculates the subtotal Set the focus to cell E4 Enter the following formula.
=C4*D4
HERE,
“C4D4” uses the arithmetic operator multiplication () to multiply the value of the cell address C4 and D4.
Press enter key You will get the following result
The following animated image shows you how to auto select cell address and apply the same formula to other rows.
Mistakes to avoid when working with formulas in Excel
Remember the rules of Brackets of Division, Multiplication, Addition, & Subtraction (BODMAS). This means expressions are brackets are evaluated first. For arithmetic operators, the division is evaluated first followed by multiplication then addition and subtraction is the last one to be evaluated. Using this rule, we can rewrite the above formula as =(A2 * D2) / 2. This will ensure that A2 and D2 are first evaluated then divided by two. Excel spreadsheet formulas usually work with numeric data; you can take advantage of data validation to specify the type of data that should be accepted by a cell i.e. numbers only. To ensure that you are working with the correct cell addresses referenced in the formulas, you can press F2 on the keyboard. This will highlight the cell addresses used in the formula, and you can cross check to ensure they are the desired cell addresses. When you are working with many rows, you can use serial numbers for all the rows and have a record count at the bottom of the sheet. You should compare the serial number count with the record total to ensure that your formulas included all the rows.
Check Out Top 10 Excel Spreadsheet Formulas
= E4 + E5 + E6 + E7 + E8
With a function, you would write the above formula as
SUM for summation of a range of numbers AVERAGE for calculating the average of a given range of numbers COUNT for counting the number of items in a given range
The importance of functions
Functions increase user productivity when working with excel. Let’s say you would like to get the grand total for the above home supplies budget. To make it simpler, you can use a formula to get the grand total. Using a formula, you would have to reference the cells E4 through to E8 one by one. You would have to use the following formula.
=SUM (E4:E8)
As you can see from the above function used to get the sum of a range of cells, it is much more efficient to use a function to get the sum than using the formula which will have to reference a lot of cells.
Common functions
Let’s look at some of the most commonly used functions in ms excel formulas. We will start with statistical functions.
Numeric Functions
As the name suggests, these functions operate on numeric data. The following table shows some of the common numeric functions.
String functions
These basic excel functions are used to manipulate text data. The following table shows some of the common string functions.
Date Time Functions
These functions are used to manipulate date values. The following table shows some of the common date functions
VLOOKUP function
The VLOOKUP function is used to perform a vertical look up in the left most column and return a value in the same row from a column that you specify. Let’s explain this in a layman’s language. The home supplies budget has a serial number column that uniquely identifies each item in the budget. Suppose you have the item serial number, and you would like to know the item description, you can use the VLOOKUP function. Here is how the VLOOKUP function would work.
=VLOOKUP (C12, A4:B8, 2, FALSE)
HERE,
“=VLOOKUP” calls the vertical lookup function “C12” specifies the value to be looked up in the left most column “A4:B8” specifies the table array with the data “2” specifies the column number with the row value to be returned by the VLOOKUP function “FALSE,” tells the VLOOKUP function that we are looking for an exact match of the supplied look up value
The animated image below shows this in action
Download the above Excel Code
Summary
Excel allows you to manipulate the data using formulas and/or functions. Functions are generally more productive compared to writing formulas. Functions are also more accurate compared to formulas because the margin of making mistakes is very minimum.
Here is a list of important Excel Formula and Function
SUM function = =SUM(E4:E8)
MIN function = =MIN(E4:E8)
MAX function = =MAX(E4:E8)
AVERAGE function = =AVERAGE(E4:E8)
COUNT function = =COUNT(E4:E8)
DAYS function = =DAYS(D4,C4)
VLOOKUP function = =VLOOKUP (C12, A4:B8, 2, FALSE)
DATE function = =DATE(2020,2,4)
