Understand Formulas vs. Functions in Excel.
Understanding Formulas vs. Functions
Many new Excel users are confused about the different between the terms “formula” versus “function” – so let’s clarify that now, making your use of our videos on creating custom formulas and using Excel’s many built-in functions that much easier to understand.
First, a formula in Excel is the same thing as it is on a calculator, a sheet of ledger paper, or a calculation you do in your head. When you’re calculating the tip to leave your waiter at dinner, you’re doing a formula, regardless of your method for figuring it out.
Formulas can be used to calculate a commission on total sales or the amount of tax or shipping to add to a purchase.
Here, I’m calculating the commission due on a sale, where the salesperson is supposed to get 10% of the sales total. I’m working in Column J, the Commission column.
So I type an equal sign in the first cell in that column, click on the total sales, add my operator (the asterisk, for multiplication), and then type.10. When I press ENTER, my formula is completed, and the commission is calculated.
I can repeat that for all of the sales people, for their total sales, using AutoFill.
Now, to demonstrate how that’s different from a function – which is a preset formula with established components (known as arguments) that perform specific tasks within the function – let’s AVERAGE the commissions for our sales team using a formula and then do the same thing with a function for their sales.
First, looking at approaching this with a formula, let’s think about the math involved. If we have 10 sales reps and we want the average of all of their commissions in Column J, we’d total their commissions and then divide by 10. As a formula, that would be
=(J6+J7+J8+J9+J10+J11+J12+J13+J14+J15)/10.
That’s a lot of clicking and typing of plus signs, right? Plus, we have to manually remember to put all of those added cells inside a set of parentheses so our /10 works and divides the total of all 10 cells by 10.
A much easier way to go? A function – and the AVERAGE function perfectly demonstrates how the same math can be done, but with much less work for you.
By typing =AVERAGE at the foot of the Sales TOTAL in Column I, and then pressing the TAB key (or choosing AVERAGE from the pop-up list that appears as soon as I start typing my function name), I can then just drag through the cells to be added up, and press ENTER.
Bang, zoom, Excel knows, because I started with the word AVERAGE, that I want to add up all those numbers and then divide the total by however many numbers were added. Same answer, many fewer things to click and type.
To see all the functions Excel has to offer, click the fx button on the Formula bar, and use the resulting dialog box to search for functions by what they do, their name, or category. You can use the dialog box to complete the function you choose, plugging in values and cell addresses the function needs to give you the answer you seek.
Note that help is built into the dialog box, explaining the overall purpose of the function displayed at the time, and each argument in the function, so you know what it is that Excel is asking you to provide to complete the function you’ve chosen. Using a PMT function as an example, which calculates payments on a loan, you can see there are lots of arguments to fill in, and each one is explained.
So – the difference? A formula is a calculation you build, cell by cell, number by number, adding your own operators, parentheses, and so on. It gives you the freedom to create any calculation you can imagine. In other videos, you’ll learn about more complex formulas, including the ability to nest them inside of other formulas and even within functions.
Functions, on the other hand, follow a fixed path, with a preset series of arguments that you supply values – numbers or cell addresses or both – to satisfy.
Less freedom, but certainly more consistency, and for the non-mathematicians in the world, much easier.