Formulas and Functions – What you need to know
The first thing that you need to know is that all functions are formulas, but not all formulas include functions.
Let’s start by defining what they are:
Formula
While there are many ways that we use the term ‘formula’, I’m going to focus on the mathematical aspect.
a mathematical rule expressed in a set of numbers and letters
Definition from Cambridge Dictionary
OK – in plain English, a formula in Excel tells the spreadsheet that this is something to be worked out, and then the answer displayed.
How to Recognise a Formula
All formulas need to start with an equals (=) symbol.
However, a number of spreadsheets will also accept a plus sign (+) at the beginning of a formula.
Functions
There are a number of formulas that people use over and over again, for example adding up a range of numbers.
To help, Excel and other spreadsheets have these calculations pre-written, you just need to add the right information.
Due to the number of functions – each one has a name that relates to their use.
For example:
- SUM
- AVERAGE
- IF
- LOOKUP
Because they are a calculation that you want Excel to perform and show the result, they are a type of formula.
How to Recognise a Function
Like a formula, they start with the equals sign, however they continue with the function name – to identify the specific one to use – followed by an open bracket, the details needed for the calculation and ending with a close bracket.
=FUNCTION-NAME(DETAILS)
Many functions can have more than one piece of information inside the brackets, and they are separated by comma (,).
Putting Formulas and Functions to Use
Using a Formula in your spreadsheet
While it’s straight forward to use, do you need to decide what you want to calculate, as that will “tell you” what to enter.
All you really need to do is type = (equals) and then what you want it to work out.
Of course a key question is where are you typing in the formula?
The answer is “where you want the answer to appear”.
Remember to ensure that you have the right cell selected before you start typing.
Simple example: =2+3
Type it into any spreadsheet (Excel, Google Sheets, iWork Numbers) and the answer 5 will be displayed.
Note: An easy way to start – think of what you’d type into a calculator, but put the equals at the beginning.
Generally we either don’t know exactly what the numbers are to be used, or they may be reliant on something else e.g. another formula.
In that case, we replace the ‘number’ with a cell location where the number can be found.
Simple example: =A1+A2
Note: Type in the numbers first, so that you can select the right cell as you enter your formula.
Using a Function in your spreadsheet
In order to be able to use a function, you need to know what you want to do, and then find the ‘matching’ function name.
There are a lot of functions to choose from, and to make it (a bit) easier, they are grouped:
- Financial
- Date and Time
- Math & Trig
- Statistical
- Lookup and Reference
- Database
- Text
- Logical
- Information
- Engineering
Fortunately, a) you don’t need to know or use every single function (phew), and b) there’s a search function to find the right one.
In Excel – at the left of the Formula Bar is the fx button. Click the button or use the shortcut key Shift + F3, to display the Insert Function dialog.
In the dialog box, you can search based on the name (if you know it) or a brief description of what you want to do.
When you choose the function you want, the dialog box will show you the information it’s expecting, in the order required and allow you to enter data or cell addresses.
Recap
Formulas are how you enter the calculations that you want worked out into a spreadsheet.
Formulas have to start with the equals sign (=).
Functions are a sub-set of formulas – as they also work out answers, but are pre-written for a specific calcuation.
Functions also start with an equals sign but then have the function name followed by the required information in brackets.
Related Posts
- How to choose your favourite online spreadsheet
Unsure of which online spreadsheet program to choose? Find out here the features and benefits of both Microsoft’s Excel and Google Sheets, as well as some tips on how to pick the right one for your needs. - What’s the point of the Excel Information Functions?
What are the information functions in MS Excel? How can you make use of the data they add into your spreadsheet. Read the post to learn more. - How to use Date and Time Functions in Excel
Unsure how to work with dates and times in Excel? This guide will show you how, with a variety of date and time functions explained. - VLookup in Excel: What Do You Need to Know Step by Step
Learn how to use the VLookup function step by step and you’ll find that it’s actually not as difficult as you might think. Check out this guide to some of the basics! - Excel IF Statement: What do you need to know
Unsure of how the IF statement in Excel works? This guide will walk you through all the basics, as well as some more advanced features, so that you can start using this function to its full potential.
