Saturday, December 19, 2015

What is a Formula?

In my opinion, the two top reasons to use Excel are the automatic organization of rows and columns, and formulas. Instead of adding numbers by hand or searching manually through a huge list, you can write a formula and Excel will calculate it for you.

As always, if this topic is brand new to you, I highly recommend watching this video, where I walk through the basics. Then I encourage you to try it out on your own to get comfortable with it.



If you've used Excel much, or at all, you've probably seen a formula of some kind. They are (usually) live, meaning they recalculate anytime you hit Enter or Tab on your spreadsheet. You can turn this off if you want, but that's for another post.

Formulas can be as simple or as complicated as you need.  My personal favorites include:

SUM
SUMIF
COUNT
COUNTIF
SUMPRODUCT
INDEX
VLOOKUP
IF

Obviously, some of these are my favorites at work when dealing with large datasets. And while there are over 200 Excel functions to choose from, I think you'll find that most of what you want to do can be done with these few.

Formatting

The first thing to know about writing a formula is how to start. The answer is simple. You start with "=". Go type = into any cell in Excel. Anything happen?

Now type just about any letter in the alphabet. Strange fact: there are no functions that start with J, but there is at least one for every other letter of the alphabet. Who knew?

But anyway, when you start typing after an = sign, Excel starts guessing at what formula you want. If you select a formula from the list, it will tell you what the formula does.

Once you've found your formula, either hit Tab or finish typing it in with an open parentheses (. Now you get to the meat of the formula.

Parameters

I use this word a lot when talking about my spreadsheets. What I mean are the values used within a formula. In an extremely simple case like the SUM formula, the parameters are the numbers you want to add together.

Let's use the SUMIF formula as a slightly more complicated example. When you type =SUMIF( into Excel, you'll see a box that says SUMIF(range, criteria, [sum_range]). What on earth does this mean?

What it means is that for this formula, your parameters are range, criteria, and sum_range. Sum_range is in brackets because it's optional. The formula will work whether you enter the third parameter or not.

Now, just because you know what Excel calls your parameters does not mean you know how to write the formula. There are a few ways to figure it out: Trial and error, Excel help, or check out one of my tutorials! Honestly, all three of these are viable ways to get what you want, and each will be faster depending on the situation.

The Bottom Line

To write a formula, always start with your "=" sign, and then don't forget to make sure you know what your parameters are. I will have lots of tutorials on my favorite formulas, but if there is a specific one you want, please let me know! I'd be happy to help.

Good luck writing your first formulas!

No comments:

Post a Comment