Table of Contents:
- 1. Introduction
- 2. Max and Min
- 6. Today
- 7. Sort
- 8. Square Root
- 9. Standard Deviation
- 10. Absolute
Now that you have a basic grasp of what Google Sheets is, we will teach you some good formulas to know, so that you can add them to your Google Sheets arsenal. We went through these equations, and came up with 10 formulas we thought would be useful to know. With these 10 equations, you should be on your way to making up your own basic spreadsheets. So in no specific order, our top 10 formulas to know.
In the first lesson, we taught you how to add two or more numbers. Now that’s good and all, but what if you wanted to find the sum of 20 or more cells. Of course, you could add all 20 cells separately, or you can do the sum command. What sum allows you to do is highlight an area, and find the sum of all those values. Here is how the formula looks:
=SUM(set 1, set 2, set 3, …)
So just type in “=SUM(“, and just highlight all of the areas you want added together.
At this point, it might be a bit repetitive, but “product” works the exact same way Sum does. You highlight an area of cells, and it finds what the value is when you multiply all of those numbers together.
=PRODUCT(set 1, set 2, set 3, …)
You may have noticed that during the lesson with basic mathematic functions, we did not go over square roots. This is because to find a square root, you need to use the square root function. No worries though, it is very simple.
=SQRT(value you want to be square rooted)
Just enter in “=SQRT(“ and enter in the value or cell you want square rooted. Just as simple as that.
While less often used, this formula can come in handy when you least expect it. If you have a negative number and want to make it positive, the absolute function can help you. Just type in:
=ABS(Value you want absolutted)
It is important to note that you CANNOT enter in a series of cells to be absolute, you can ONLY absolute one value at a time
Maximum and Minimum:
As you find with most formulas, the same is pretty much self explanatory. This formula is used to find the maximum or minimum number, in a set of cells you specify. This is how the formula looks.
=MAX(set 1, set 2, set 3, …)
=MIN(set 1, set 2, set 3, …)
All you have to do is type =MAX or =MIN, and highlight the cells you want it to take a max or min of. If you want it to look at multiple sets of cells, you can highlight one group, put a comma, and highlight another. We have an example of this in the photo here.
This formula would return you the following:
As the name says, this formula finds the average value across a set of cells. It works the same was as Sum or Product.
=AVERAGE(set 1, set 2, set 3, …)
Just enter in “=Average(“ and the values you want averaged, and voila.
At this point, it may be pure repetition, but this works exactly like sum or product. You highlight a set or sets of cells, and it tells you the standard deviation of the values. This comes very handy if your doing any statistical analysis on your spreadsheet. The formula works as shown:
=STDEV(set 1, set 2, set 3, …)
This one is quite a simple one, but very useful. You may not know, but a popular thing Google Sheets is used for is to make schedules, and knowing today’s date is quite important. By knowing the date, you can figure out in how many days a certain assignment is due. So how do you do this? Well just go to a cell and enter in:
Hit enter, and it’ll automatically update each and every day, telling you the date. You can find more details about this formula by clicking this link.
If you happen to love neatness, then you will ADORE the sort function. What this function can do is take an unorganized set of cells, and organize them. If you have numbers, it can make them go high to low, or low to high. If you have letters you can make them go A-Z or Z-A. If you have both letters and numbers…well that’s more complex, but we encourage you to play around with that. So this is how the sort function works:
=SORT(area you want reorganized, column to sort by, ascending or descending order)
As you can see, the sort function is a bit more complicated than the rest. First, you want to highlight the area you want reorganized. For example, it could be an entire table. From then, you enter which column it should sort by, the first, second, third, etc. Lastly, it asks if you want to do it by ascending or descending order. If you want it to go A-Z or low to high, enter in TRUE. If you want it Z-A or High to Low, enter in FALSE.
If you need help, reference the video. We are highlighting the entire table, and entering the number 2, because we want that column sorted. We want it going low to high as well, so we put in TRUE. Now, what is our final product? It is the same exact table, but organized by column two, going low to high.
This last formula was a bit tricky, so we have attached an in depth article written by Google here.
With these 10 formulas (and the other skills you learn from the previous sessions), you have a solid foundation to start making your own spreadsheets. Look around for ideas, there are plenty of things use spreadsheets for. That being said, stick around for our future lessons, as there is still much to be learned.
However, there are so many more formulas you could possibly use. We have linked here support articles to many different types of formulas if you’re curious. However, we recommend to taking things one step at a time. Once you master all the basics of Google Sheets, then would be a good time to go exploring, as things will come to you much easier. That being said, here are the articles:
Math Functions: Going over mathematical operations as well as trigonometry and other functions.
Statistics: Going over anything to do with statistical analysis
Engineering: Some more niche functions that tend to the engineering field
Financial: Formulas that tend to the financial field, which are also quite niche
There are many other formulas, but these are a list. You can find close to all the formulas included in Google Sheets here.
We hope you enjoyed today’s lesson, and as always, happy spreadsheeting!! 🙂