Excel Formulas Overview

In Excel, a formula is a tool that helps you calculate results by using the data you provide or the values stored in different cells. It’s a simple way to perform math and get answers based on your data. It is a powerful tool that allows you to automate your calculations, analyse the data and also create dynamic templates or reports.

Basic Formula Structure

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 /.

Creating a Simple Formula

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:

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:

=A2*B1

What will the above formula do? It will multiply the quantity in cell A2 by the price in cell B2 and give the result in cell C2.

Common Operators and Functions

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.

Using a Function

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:

=AVERAGE(A2:A10)

You can either pass a number as a value to the function or even pass the cell references and Excel will calculate without any problems. If we pass 5 for the above function, will Excel throw any error? No, you can use it as below:

=AVERAGE(A2:A10,5)

Understand the Syntax of a Function

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.

Tips for Effective Formula Use

• 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.

Conclusion

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.