Excel Formulas Overview

A formula begins with an equal sign (=) along with the combination of cell references, values, and operators.

• Cell References: This refers to a range of cells in a spreadsheet such as B2 which refers to a cell in Column B and in Row 2 which you can see in the below image.
• Values: These refer to numbers or text that you directly enter into the formula. For example =1+1 or =2-1.
• Operators: These are the mathematical symbols like +, -, *, and /.

Let’s check how to calculate the total cost of a product. You have the quantity in cell A2 and the price in cell B2. To find the total cost of a product, you can use the following formula in cell C2:

Excel provides a wide range of operators and functions that enable us to perform various calculations with ease. Some of the common ones are:

• Some of the Arithmetic Operators such as Addition (+), Subtraction (-), Multiplication (*), and Division (/).
• Comparison Operators such as Equal (=), Less than (<), Greater than (>), Less than or equal to (<=),Greater than or equal to (>=), Not equal to (<>)
• List of Logical Operators such as AND, OR, NOT
• Some of the Mathematical Functions such as SUM, AVERAGE, COUNT, MAX, MIN, etc.

Let’s say you have a set of numbers from A2:A10 and you need to get the average of those numbers. You can use the below formula to get your results:

Understanding the syntax of a function in Excel is crucial. It’s important to know which arguments are mandatory and which are not.

Let’s take a look at the following functions as examples:

1. AVERAGE
   • The syntax is =AVERAGE(value1, [value2, …])
   • You can see that value1 is the first value or range to consider without square brackets, indicating that it is a mandatory argument whereas value2 and subsequent values are optional.
2. IF
   • The syntax is  =IF(logical_test, [value_if_true], [value_if_false])
   • Notice the difference between the syntax of AVERAGE and IF functions. Both have one mandatory argument to be passed. However, the IF function has two optional arguments. These arguments specify what value to return if logic is true (value_if_true) or false (value_if_false) even if you are just passing TRUE and FALSE.

• Absolute and Relative References: Understand when should you use absolute ($A$1) and relative (A1) references in your formulas to make your formula reusable in other cells as well.
• Error Checking: Excel shows the error messages to help you identify the mistakes in your formulas and correct them with ease. But, it’s important to understand the reason and meaning behind the error.
• Using Named Ranges: Creating a named range for a range of cells gives you an easier way to create formulas.

Mastering Excel Formulas is a fundamental skill for anyone who is working with Excel spreadsheets whether it is for calculating some results or for data analysis. By understanding the basic structure, operators, and functions, you will be able to create powerful and dynamic calculations and also automate your data analysis tasks.