Formulas and Functions
A formula in excel uses standard mathematical symbols to operate on values that are available on cells.
Formula can operate on both constant values and on cell references. The constant could be any value like text, number logical values like TRUE/FALSE etc.
A formula can also be a combination of constant values and Cell references.
Different Mathematical operators that are used in excel:
+ For addition
– For subtraction
* For multiplication
/ For division
% for percentage
^ For exponentiation (power)
How to create a formula in Excel
- EVERY formula begins with an equal sign (=). If you don’t start your formula with equal to sign, excel treats it as text.
- To write a formula in a cell, click in the cell and then type the formula. Once you have finished entering the formula, press enter or tab.
- To start with, type the formula = 100*2 and as soon as you hit enter after finishing the formula, you will notice the result is calculated as 200 in the cell.
- Suppose you have 100 in cell A1 and 2 in cell A2, now instead of hard coding the value into your formula just enter the formula =A1*A2 or give the cell reference by selecting the cell A1 and A2 from your mouse or keyboard after entering the equal to sign.
Now hit enter to see the result.
How to insert a function in excel
A function is a pre-defined formula in excel that takes argument(s), and perform a range of operations. Functions can take arguments such as constant values, cells, ranges etc. and returns value(s).
We will see how we could insert an average formula to calculate the average of four numbers as shown in the pic below.
Step 1
Select the cell where you would like to calculate average score; in this case I want average value in cell E2 and click on insert function under Formula tab in excel.
Step 2
You will get the screen as shown below where all the available excel functions are listed, if you are not sure as to what functions do you need to use to perform a particular task then try typing the task that you want to do in the description box at the top and excel will recommend the relevant functions.
I have selected average function from the list and now press OK.
Step 3
Select the range which has your numbers e.g. as the numbers here spread from A2 to D2, I have provided the range as A2:D2, you need not manually put the range name, you could easily select the range by mouse and press OK.
And now you could see that we have successfully entered a function in a cell to calculate average of a range of numbers.
It was just a simple example to get you familiar with the process of how to insert a function in excel.
Similarly you could play around with the available functions for exploration.
How to Copy Formulas, Functions and Cell References in excel.
It’s always a best practice to use cell references in formulas instead of typing actual numbers. It makes the formula dynamic i.e. your formula will produce updated result when the values are changed on those cell references, it makes the formula robust.
One of the major advantages of using cell references in excel formula is that we could simply copy and paste the formula across rows or columns to calculate the results in a data table.
