How to Use Formulas and Functions in Numbers on Mac

Do you use Numbers on your Mac for tracking, logging, manipulating, and analyzing data? Whether you know it or not, the application offers some built-in tools to help you handle your data better.

You can put away the calculator and stop spending hours doing things manually. We’re here to show you how to use formulas and functions in Numbers to make your data easier to work with.

Using the Formula Editor

Before jumping into inserting formulas and functions in Numbers, it’s important to familiarize yourself with the Formula Editor.

To do this easily, click a cell where you’d like to add a formula or function and type the Equal Sign. This is all it takes to open the editor. You can also click Insert > New Formula from the toolbar which will also open the Functions Browser (which we’ll discuss later).

You can move the Formula Editor by dragging the left side and resize it by dragging from one of the edges with the double-sided arrow.

You can click on a cell or drag through a range of cells that you want in your formula and they will appear in the editor. You can also use arithmetic operators to add, subtract, multiply, and divide. And of course, you can type in numbers with or without decimals.

You will click the checkmark in green when you finish with the editor and want to apply your formula. And if you change your mind, click the X in red to close it without saving any changes.

Quick and Easy Formulas

Conveniently, Numbers offers some basic formulas for you to use without having to enter them manually.

Select a cell where you would like to use a formula. Click the Insert button in the toolbar and pick from sum, average, minimum, maximum, count, or product.

Numbers will make a good guess based on the data in your sheet for which cells to use and highlight them for you. And quite often, Numbers is right, but not always. If the cells you see highlighted are correct, hit your Return key to apply the formula.

If the cells highlighted are not the ones you want to use, click the cell containing the formula and the Formula Editor will open. Then, select the correct cells to make them display in the editor. To finalize the formula, click the checkmark to apply it.

More Useful Formulas and Functions

If the basic formulas or functions we’ve mentioned so far just aren’t enough for what you’d like to do, we have some other useful ways to work with your data.

Compare Values

You can use comparison operators to see if values in cells are equal, greater than, less than, or not equal. The results of the formula will display as True or False. This is helpful if you want to compare sales, income, or expenses over months or years.

For our example, we want to see if our sales from January of this year are greater than or equal to the previous year.

1. Select the cell where you want the formula displayed and open the Formula Editor.
2. Click the first cell for the formula.
3. Type the comparison operator. For greater than or equal to, enter <=.
4. Click the second cell for the formula.
5. Click the checkmark or hit your Return key.

You can see in our example that our sales for this January are in fact greater than or equal to last year with the result “True” in the formula’s cell.

Split Text

You can split up strings of text if you have more information than you need or want to pull out specific data using the Right and Left functions. This is useful for extracting area codes from phone numbers, states from cities, or zip codes from address strings.

For this example, we have a spreadsheet where the data contains both a city and two-letter state abbreviation for a customer, as in Chicago, IL. We want to pull out the state for each person into a separate field.

1. Select the cell where you want the formula displayed and open the Formula Editor.
2. Open the Functions Browser by clicking the Format button on the top right.
3. Search for “Right,” select it in the list, and click Insert Function.
4. In the Formula Editor, click “source-string” and then click the cell with the data.
5. To the right in the editor, click “string-length” and type the number 2.
6. Click the checkmark to apply the formula.

What this formula does is pull out two characters (step 5) from the right side of the string (step 3). By applying this formula to our column, we can now see just the state’s abbreviation for each customer.

Remove Extra Spaces

If the data in your spreadsheet comes from a database, it may not always be formatted the way you need it. Using our customer spreadsheet example above, you can have a customer’s first and last names with too many spaces between them. This can cause problems for automated emails or even mail merges using those names.

With the Trim function in numbers, we can automatically remove those extra spaces in one fell swoop.

1. Select the cell where you want the formula and open both the Formula Editor and Functions Browser.
2. Search for “Trim,” select it in the list, and click Insert Function.
3. In the Formula Editor, click “source-string” and then click the cell with the data.
4. Click the checkmark to apply the formula.

Now we have a nice and clean list of our customer names without extra spaces to wreak havoc with our other software.

Concatenate Data

Maybe your situation is the reverse of the one above. You might have data in two different cells that you want to combine into one. You can do this with both numbers and text using the Concatenate function.

Let’s say that the data we received has a customer’s first and last names split into two different cells. Our email software needs those names to be together.

1. Select the cell where you want the formula and open both the Formula Editor and Functions Browser.
2. Search for “Concatenate,” select it in the list, and click Insert Function.
3. Click “string” and then click the cell with the first name and insert a comma.
4. Add beginning and ending quotes with a space in the center (” “) and insert a comma.
5. Click the cell containing the last name.
6. Click the checkmark to apply the formula.

There we go! We have a column with our customer’s first and last names together instead of spreading them across different cells.

Convert to Plain Text

One more function in Numbers that can come in super handy, especially when obtaining data from various sources, is the Plaintext function. You may have text and numbers in your spreadsheet that are formatted in rich text. Instances can include links that are clickable and names that are bold, italics, or underlined.

Using the Plaintext function, you can strip down your data to simple, plain text.

1. Select the cell where you want the formula and open both the Formula Editor and Functions Browser.
2. Search for “Plaintext,” select it in the list, and click Insert Function.
3. Click “source-string” and then click the cell with the data.
4. Click the checkmark to apply the formula.

You should then see your data without the fancy rich text, HTML, or other formatting.

Copy Your Formulas and Functions

With the examples we’ve shown you here, you’ll most likely want to apply the formula or function to more than a single cell. So you can easily drag to copy your formula to adjacent cells in rows or columns, depending on how your data is laid out.

Select a cell with the formula you want to copy. When you see the yellow dot on the cell border, click it and drag to the cells you want to copy it to and release.

That formula or function should not only copy to those cells but immediately apply to the corresponding data.

Easier Ways to Analyze Data in Numbers

Manipulating and analyzing the data in your Numbers spreadsheets can be done much easier with formulas and functions. For handling numbers and text both, give these formulas a try and explore some of the other built-in functions Numbers has to offer. You just never know which one might be a huge help until you test it out.

For more help with Numbers, take a look at how to create interactive charts or use conditional formatting.

Read the full article: How to Use Formulas and Functions in Numbers on Mac

from MakeUseOf https://ift.tt/2MZDxlc
via IFTTT

Share on Google Plus

About Oyetoke Toby