Quick Calculations In Mac Numbers

You can get the sum, average and perform other calculations on selected cells without needing to create a formula in another cell. The Quick Calculations appear at the bottom of the Numbers window whenever you have more than one cell selected. They also can be dragged-and-dropped to create permanent formulas and will obey filters.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. Let's take a look at using quick calculations in Mac Numbers. 
MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
Now usually when you are performing calculations in Numbers you select a new cell and you enter a formula in that cell that then adds up or does some other calculation on other cells. But there's a quick way to get things like sums and averages or find the greatest or lowest number in a group. That's using Quick Calculations. So here I've got a sample little spreadsheet. If I selected just one cell in that spreadsheet, like this one, I'll see at the bottom the value of that cell. In this case is says the actual value is 39. Now if I select more than one cell I'll see something different down there. I'm going to hold the Command key and select a second cell. So now I have two selected. Now you can see the value is gone and it's replaced with this list of quick calculations, also called Instant Calculations. So you could see right here the first one is Sum and you could see it says 77. Sure enough 39 plus 38 is 77. So I get a quick sum here of all the cells selected. I'll also see the average, the minimum value, the maximum value, and count A which is the number of cells that have a value in them.
Now if I select another cell, like this one, you could see it's now adding up all three of these, getting the average of all three, and min and max of those. I could select all of the cells in this column here, like that by clicking with the Shift key, and I could see here Count A tells me I have 16 values, that's not including this one because it's empty, and I've got the Sum and average there.
I could also simply click the letter at the top of the column. What that will do is it will give me the values but it knows enough not to use any Header cells there. So it's not going to try to add the word apples into this or include it except that it will actually include it here in the number of cells that have an actual value. So it shows me that there are 17 that have an actual value including apples. But the Sum and the Average are going to be based on just the ones that are the actual cells, not the Header cell. So the average here of 24.375 is the same as if I only select these.
Now the cool thing is you don't have to just select things in the same column. I could select this cell and then Command click and select several other cells like that and then I could get the sum of everything selected. I can continue to add some things in there. So you can get things like sums and averages or find the lowest or highest value with a variety of different selections. If I want to select this to this to find the highest value, there it is. The maximum value is 39. I could see the average for all these cells is just under 19 here.
Now you don't have to just settle for these five different functions. If you click here you'll see a list of all the functions that you can get along the bottom. So the defaults are at the top here. But you can add some more. So, for instance, if I wanted the median value I could select that and add it in. So now I could see the Median value for all these is 24. That will stay there. So I can select another range of cells and see the median value. You could also rearrange these. So I could Median over to the left just by dragging it and placing it there instead. I could scroll back and forth with the trackpad here if there's more than that could fit in the area. Or you've got these little arrows here you could click. You could easily go in here and remove ones you don't need and add ones that you do.
Another neat thing that you could do with these is you could drag and drop them. So let me add another table here. I'm going to move this over to the side and let's say I want to use this for some calculations. If I wanted to get the Sum of all of these I could select them like that and I could drag and drop and it will add a formula in here. If I selected this I could see it has given me the sum from C2 to C18 of the Sales Table. It will allow you to do some complex calculations that are not easy to type otherwise. For instance if I wanted to take these cells here and then maybe add a few there and a few here and maybe a couple here and for some reason I wanted the average of all of these I could drag this over into here and if I look at this here you could see it's going to do the average of and then it has got each individual range in there. So, that might take me a little while to type but actually to do the selection and drag and drop like that was pretty quick.
Another great thing you could do with these is you could get the Sum or Average of a filtered table. So if I were to go and add a new row into the table like that and then change the table to it had one footer row there and then I were to use the Sum formula here, so Sum and then I'll click on C there for the whole column, it will give me the sum. But if I then go to Organize, Filter, Add a Filter and use the Sale column there and say I'm going to exclude everything that has True in it, you could see the Sum is still the complete sum of all of the cells in this column. It's not taking into account the filter. However, if I forget about that and instead select this here. I could see the sum is 390. Now I add that filter in. Notice that the sum here in the quick calculation is, in fact, the filtered amount. So here it says 390. Here is says 322. So that is really great to be able to get a quick calculation on a filtered table without having to write a complex formula that also mimics the filter with Sum If or Average If or something like that.
But I think the best use of Quick Filters is to eliminate the need a lot of times for these footer rows here. Those are great if you really always need to see the totals and it is a very important part of that table. But if you only occasionally need to see the totals you can just select all of the cells with the double click there of the letter and you get the sum right there at the bottom and you could do that whenever you need it without having to have an extra row at the bottom. You can have situations where you can have a table like this and completely avoid using calculations at all, just using the quick calculations there for your needs which is great if you're not familiar with spreadsheets and don't feel like getting into functions and formulas right now.
Hope you found this useful. Thanks for watching.

Comments: 8 Responses to “Quick Calculations In Mac Numbers”

Carl Sechrest

2 years ago

Is it possible to do Quick Calculations using an iPad and Numbers?

Gary Rosenzweig

Carl: This doesn't work the same way in Numbers for iPad, no.

Jeff

Using 11.1 numbers; Watched your video on linking cells in other tables. BUT, it won't link a cell connected / using a formula with other cells in a table to another tables.
Example: I create a basic food costing formula. =price unit/portion to give the sum of cost per unit in a recipes. Fine, works. BUT when you pull that cell with the cost per unit in a recipe into another table it RED triangles. Everything from "string issue" to missing a number. Why do this? Because when I update the price..

Jeff: Not sure what you are experiencing there. Check your formula carefully. Or maybe you are looking for something different. See https://macmost.com/create-powerful-numbers-spreadsheets-with-the-indirect-function.html

Leif

Problem: a large number of lines with data in columns (needed to be scrolled) require quick calculations (to be saved in new data sheet) for different columns (but same lines).
Question: how to move the highlight from one column to the next without having to scroll/re-highlight the same lines?
Hope you understand.

Leif: Sorry, no, I can't figure out what you mean with that description. Tray asking at https://macmost.com/ask and describe it using cell references like B4 or F99.

Zoran

Hey Gary,
how do I change the value in the field based on another field?
Example:
If the value in field A is 3500 set the field B to 90

Zoran: Sounds like you are looking for the IF function. https://macmost.com/learn-how-to-use-the-if-function-in-numbers.html

Comments Closed.

Quick Calculations In Mac Numbers

Comments: 8 Responses to “Quick Calculations In Mac Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information