Understanding formulas and functions in Excel
Let’s talk a little bit about formulas and functions, excel does calculations using formulas and functions, understanding of formulas and functions in excel is paramount.
This might sound the same, but let’s make it a little bit clear. Formulas and Functions are the same because they always start with and equal (=) or plus (+) sign.
There is no exception between them, you have to start with an equal or plus sign.
FORMULAS
Formulas are basic arithmetic, like:
Addition |
+ |
Subtraction |
– |
Multiplication |
* |
Division |
/ |
Exponents |
^ |
Excel uses cell referencing to perform both formulas and functions, let’s see how to understand formulas and functions the excel way.
In this workbook above, you’ll notice a very simple formula. The first formula simply reads 2+2=4, but you shouldn’t do that when it comes to excel.
The appropriate way is to say the content of column D row 4 plus the content of column D row 5 (=D4+D5), but you might wonder what’s the difference, they are both correct.
But what if we change the content of one of the cells, let’s say to 3, look what happens below;
Now this 2+2=4 is wrong because the formula has nothing to do with the contents of D4 and D5, while the other formula is correct because it’s using cell referencing with the contents of D4 and D5.
FUNCTIONS
They are over three hundred functions in excel and not too worry, we won’t go over every single one. I will make sure; you start thinking like excel and that’s pretty possible.
Functions perform so many different tasks, like finance, statistics, engineering, physics and so on.
Pre-made named formulas are called functions in Excel, each function does a unique task and uses at least one set of parentheses {()}.
SYNTAX
Syntax of a function in excel refers to the layout and order of the function and its argument.
General syntax for all function: = name_of_function(……..)
In the parentheses, some functions will have a lot of argument in it, while others won’t even have a single argument in the parentheses at all, but you have to have at least an argument in the parentheses
Examples of functions in excel
- =Avg()
- =Sqrt()
- =Today()
An average function allows you to take average of range of numbers.
Square root of range of numbers is possible using the square root function, in the example above or you might want to insert today’s date in a worksheet you are working on, simply type equals today with an open and closed parenthesis.
Try using the average function as the example below, here is a workbook showing how the average function works.
You have a column of numbers and you want to get the average, the wrong way to do it is to do it manually, you add up the numbers in column D and divide by amount of numbers.
That’s absolutely not the right way to do it, you have to figure out how many numbers and then start adding up, too stressful right?
All you have to do is use the average function in excel to get the task done, fast and efficient, you might decide to remove or add to the range of numbers and the average function would still work efficiently.
Well, now that you know what formulas and functions are all about and understanding of formulas and functions in excel is clear to you, let’s do something amazing with them.
Download for practice
You can watch the video below for visual explanation. Thanks, sharing is caring.