Formulas & Functions - Part I
It might seem a little neurotic to make any distinction between Formulas & Functions, but experienced users know the difference, even if they don't hold specific definitions in their minds. The reason they do is because they know that Excel is designed to save time and increase efficiency, and so any time they can make Excel work harder THEY WILL!
One of the most intimidating aspects for new users "learning" Excel, is the belief that there is some form of special programming language, or a black book of secret Excel guru commands that they have to learn before they can actually use the program. The truth is that anyone with an 8th grade math education can learn to use Excel in less than 15 minutes, if they're able to take 3 minutes first to learn the difference between Formulas & Functions.
So, what is a Formula?
It's actually a silly question when asked aloud, but the reason new users ask is because they don't realize how obvious the answer actually is. A formula in Excel is exactly the same as every other formula you've ever encountered. It's simply a recipe for solving a problem that's based on the relationship between any two or more values.
In the simplest sense, every formula has no less than 2 components: Numbers & Operators. When you want to add two numbers together, you write down 2+2=4. The numbers are evaluated (sorry, standard math term) using the addition operator "+". Obviously, you're not at this site to learn how to put two and two together, but metaphorically you probably want to understand how to combine the capabilities in Excel so you can get more work done in less time.
Naturally, the most productive formulas are the ones that help us solve for some unknown value and frequently introduce a 3rd component; the dreaded Variable! Still, you're way beyond this if you can use a pencil and a calculator, since you already know what "x" means in the formula: 2+x=4. The interesting part is when you realize that formulas like this are awkward in Excel precisely because they're TOO SLOW! [Read my Post entitled Excel Power & Performance].
Excel steps in and gets warmed up when you need to solve either: (a) Multi-step problems, where you need to eliminate "fat-finger syndrome" errors on your calculator, and (b) Complex equation formulas that can range from geometry (Pythagorean Theorem), to statistics (Standard Deviation), to financial problems (Black-Scholes Option Pricing).
So, how does Excel deal with Formulas?
Excel deals with formulas quickly and without remorse, but there is a small hitch. In a sense, Excel works your formula backwards a lot like the gameshow Jeopardy. If you want Excel to provide the correct answer, YOU must give it the correct question.
With a calculator, most people are accustomed to entering in "2+2" and then hitting the Enter button. In Excel, once you've chosen a cell (say A3) to use as your mini-calculator, you have to start by telling Excel that it's about to get a problem to solve -- the first thing you type in the cell is the equals ("=") sign. In cell A3, simply type in "=2+2" and hit the enter key. Voila, if it says 4 then you're in great shape otherwise you've messed up royally and the planets will begin spinning out of orbit at any moment. Also, you can try again and if you get it to work you'll have saved the universe.
Ultimately, every calculation you would normally perform using a calculator can be entered into an Excel worksheet cell. Excel offers several major advantages over calculators though, that can save you time, reduce errors and allow you to solve multiple, complex problems simultaneously.
First, it does this by letting you use Cell References, instead of typing every number specifically into each cell. Basically, Excel lets you add any 2 numbers (using the previous formula) by replacing the number "2" with a reference to another cell that can hold ANY number you choose -- a Variable cell!
If you continue to use cell A3 (as above) to display your result, you can enter 1 number into cell A1 and a second number into cell A2, and then the formula in cell A3 would read "=A1+A2". The best part is that you don't even have to type that in because Excel will enter the Cell Reference for you if you just click on the cell you want it to refer to. All you have to do is type "=", click on the first cell (A1), type "+", click on the second cell (A2), and hit Enter. You can switch up your Operators (+,-,*,/) depending on the formula you want to compute, and add as many Cell References as you need to get the job done.
The Order of Operations (PEMDAS)
"Please Excuse My Dear Aunt Sally" - Parentheses, Exponents, Multiplication, Division, Addition, Subtraction
If you remember learning this in junior high, then you're all set and you'll be able to enter formulas like "=(A6-A7)/A8^A9" which takes the difference of the numbers in A6 and A7, and divides it by the value in A8 raised to the exponent in A9 ("^" is the exponent operator). Now we're starting to pick up a bit more speed, and you're probably starting to see why you'd rather have Excel do this work for you.
As long as you include the right Operators, and you write the formula according the the Order of Operations you'll come out on top. Why? Because the geniuses that designed Excel, built it around the same basic math rules we use everyday!
The second advantage Excel has over calculators is that once you use Cell References, you only have to enter your Numbers in once and you can reuse them over and over again in multiple formulas. Perhaps even more convenient, is that you can SEE the numbers you're using all at the same time and you don't have to worry about getting the wrong result just because you hit the wrong key on your calculator, but no longer know where the number went. Everything is Excel is visible, so you can spot mistakes quick and get to the right result without repeating your calculations over and over.
Finally (though definitely not the last of Excel's advantages), you can label all of your Numbers so you can reuse the worksheet on future calculations. It's the essence of model building, which is the process of using Excel to create tools that can be used to solve similar problems rather than calculating your results from scratch every time you need an answer. Excel let's you BUILD on your prior work and experience so that you don't have to do the same thing over and over again; you can get Excel to deal with repetitive calculations and you can even share your models with your friends and co-workers.
While Formulas are great because most people are familiar with them, experienced users only rely on Formulas for very specific, custom calculations. The simple reason for this is that since most people are familiar with basic formulas (adding numbers, multiplying numbers, finding square roots, solving for standard deviation, etc.) the geniuses who developed Excel programmed these into the software and named them Functions. In Part II, we'll explain some basic functions and show you how these make the entire process even simpler and faster no matter how many numbers you're trying to evaluate.
We'll also introduce you to one of the most valuable Excel handbooks we've ever used (and that's saying alot), that can teach you how every one of Excel's Functions work in a matter of days, if not hours.