Once question I get asked about a LOT it when it comes to Excel is just what is the difference between a Function and a Formula? In this article I will discuss the differences and work through some examples.
A Formula in Excel can be described as a statement that is written by a spreadsheet user that requires a calculation to generate a specific result. These formulas can be as simple or complex as the users wants.
A Formula can contain values, references to cells, defined names and also Functions- but more about that later.
Let’s start at the beginning. A Formula must start with the equals = sign. If you do not enter this sign then, Excel will take your cell entry as text and no calculation will be performed on your cell entries.
So after the equals sign a formula will then include the cell reference or address of the values that will be manipulated or calculated using various operands that are place in between.
These operands are standard mathematical operators that you will be familiar with:- Addition, Multiplication, Division, Subtraction and Exponents.
An example of a simple formula is
=A1+B1 which is typed into cell A2
This will add the contents of cell A1 and B1 and display the result in cell A2.
So in summary an Excel Formula is typed directly into the Formula bar, they do not have built in wizards, they cannot be nested and they are usually simple calculations.
The Excel Function on the other hand is a pre defined set of instructions built into Excel which will carry out some type of operation to provide and output value. Another way to explain is a function is a formula that is already pre written for you in Excel. For example SUM, COUNT, IF, AVERAGE and so on,
The great thing about Functions are they can be nested inside of each other, used to simplify complicated formulas, and have built in wizard to guide the user in the different parts of the function.
All of these pre programmed functions are grouped together in the Formulas Tab. just use the Formulas Tab to access them or you can click on the insert function fx button to bring up the function dialog box.
There are three ways to start using a Function, just start typing the action you want to perform for example SUM, AVERAGE etc or you can select the category if calculation for example Date & Time, Financial or TEXT and finally you could scroll though all of the available options.
So if we take the same example as above a function that would generate the same result ans writing the formula would be
=SUM(A1:B1) this will sum the contents of A1 and B1 and display the cell contents in A2.
So can you combine the two?. Well yes you can, and often users do to be able to simplify more complicated calculations. Let’s take a look at an example or two.
3. =IF(A1=<5,”Less Than”,”Not Less Than”)
All of the above are formulas, but some contain other functions like example 2 and example 3 as well as operators such as plus and minus to provide an output.
That’s it, it really is that simple.