In this tutorial, I will demonstrate some basic formulas in the popular Apple spreadsheet application known as Numbers. Using formulas in Numbers is a great way to convert raw data into useful information. Better yet, formulas allow you to know that the calculations you perform will always be accurate!
An Introduction to Formulas in Numbers
By the end of this tutorial you will have learned how to create a daily profit tracker through the use of:
- Mathematical Operators (creating your own formula) and
- Built-in functions (using the baked in functions)
Entering Example Data
Before I begin creating functions, I will need some example data to work with. Imagine I own a small business and I would like to track the total amount of revenue this business generates. This business is only open on the weekdays, so I will create a row of labels spanning Monday through Friday and a column of labels for Gross Sales, Expenses, and finally Net Profit.
I'm going to enter some example sales data for the previous week.
- Monday I made 200 in sales and had no expenses.
- Tuesday I made 110 in sales, but had a 50 in expenses.
- Wednesday I made 170 in sales and only had 10 in expenses.
- Thursday I made 240 in sales and had 70 in expenses.
- Friday I made 300 in sales with only 20 in expenses.
The First Formula
Now I'll build the first formula to total the gross sales for the week. I will be starting my formula by choosing the cell I want the results to appear in and pressing the equals key. This lets Numbers know I would like the information in this cell to be automatically calculated from data in other cells.
To the right, you will notice the sidebar has automatically switched to the Formulas view. I'm going to click the cell that has Monday's gross sales in it. Now I'm going to press the + key. Then I'm going to click on Tuesdays sales and hit the + key. I'm going to repeat this process until all days of the week have been added and my formula reads:
Then I will hit enter.
Notice that the cell is now populated with the total sales for the week.
Using a Built-in Formula
As for the expenses row, I would like to total those as well. This time I'm going to use a formula that Numbers has built into it. This formula is called SUM.
I will place my cursor in the H4 cell and begin this formula with an equals sign. Then I will type SUM. I will then click and drag across the cells I would like added together and hit enter. Now the H4 cell shows my total expenses for the week.
Calculate Net Profit
To calculate my net profit, I will put my cursor in the H5 cell, start a new formula with equals, and tell Numbers to subtract H4 from H3 using the minus key. I press the enter key. Now I know exactly what my net profit was for the week.
To get a more granular breakdown, I'll repeat the process for each day of the week.
Two Different Ways to Calculate Averages
Now imagine I would like to see the average net daily profit I generate. Well, I can easily build that by taking the total I already have available in H3 and dividing it by the number of days I am open (5).
I could also just use the “Average” formula that is baked into Numbers by pressing equals, then typing AVERAGE. Once I see the formula appear in the cell, I may then click and drag across the cells I would like to average.
While it may seem like adding, subtracting, multiplying, averaging, or otherwise manipulating this data would be easy enough to do manually, stop to consider a couple of key factors:
- What if the data were to become more complex? Would it still be easy?
- Why would I not want to set this up once and never have to think/worry about it again?
By creating formulas in numbers I don't have to worry about any calculations at all. I simply enter my data and let Numbers to the heavy lifting.
I've barely scratched the surface of what Numbers' formulas can do. There are many built-in formulas for you to explore and enjoy. By learning some of these formulas, you will become a master number cruncher. What could possibly be better than that?