Spreadsheets offer a great platform to manage data tables. But are there any easy Google Sheets functions for daily calculations? Find out below.
I believe the most required operation in tables is to find the total sum of different values. The first thing that comes to mind is to add every single cell of interest:
=E2+E4+E8+E13
But this formula will become extremely time-consuming if there are too many cells to take into account.
The proper way to add cells is to use a special Google Sheets function – SUM – that lists all cells automatically using commas:
=SUM(E2,E4,E8,E13)
If the range consists of adjacent cells, simply indicate its first and last cells even if there are blank ones somewhere between. Thus, you'll avoid enumerating each and every cell in Google Sheets SUM formula.
Let me complicate the task. I want to add numbers from different data ranges on multiple sheets, for example, A4:A8 from Sheet1 and B4:B7 from Sheet2. And I want to sum them in a single cell:
=SUM('Sheet1'!A4:A8,'Sheet2'!B4:B7)
As you can see, I just added one more sheet into Google Sheets SUM formula and separated two different ranges by a comma.
I often hear people ask about finding the percentage of different totals. This is usually calculated by the Google Sheets percentage formula like this:
The same also works whenever you need to check what part this or that number represents of the total:
In my table where I keep records of all sales for the past 10 days, I can calculate the percentage of each sale from the total sales.
First, I go to E12 and find total sales:
=SUM(E2:E11)
Then, I check what part the first day sales constitutes of the total in F2:
=E2/$E$12
I recommend to make a few adjustments as well:
=SUM(F2:F11
)
If it returns 100% – everything's correct.
Why do I recommend using the percentage format?
Well, on one hand, to avoid multiplying each result by 100 if you'd like to get percents. On the other, to avoid dividing the results to 100 if you want to use them for any further non-percent math operations.
Here's what I mean:
I use the percentage number format in cells C4, B10, and B15. All Google Sheets formulas that reference these cells are much easier. I don't have to divide by 100 or add the percent symbol (%) to formulas in C10 and C15.
The same cannot be said about C8, C9, and C14. I must make these extra adjustments to get the correct result.
To work with loads of data in Google Sheets, nested functions and other more complicated calculations are used as a rule. Array formulas are there in Google Sheets for that purpose as well.
For instance, I have a table of sales per client. I'm curious to find the maximum sale of milk chocolate to Smith to check if I can give him an extra discount next time. I use the next array formula in E18:
=ArrayFormula(MAX(IF(($B$2:$B$13="Smith")*($C$2:$C$13="Milk Chocolate"),$E$2:$E$13,"")))
I've got $259 as a result.
My first array formula in E16 returns the maximum purchase made by Smith – $366:
=ArrayFormula(MAX(IF(($B$2:$B$13="Smith"),$E$2:$E$13)))
E17 show the maximum money spent for milk chocolate – $518:
=ArrayFormula(MAX(IF(($C$2:$C$13="Milk Chocolate"),$E$2:$E$13)))
Now, I'm going to replace all values used in Google Sheets formulas with their cell references:
Have you noticed what has changed?
=ArrayFormula(MAX(IF(($B$2:$B$13=B18)*($C$2:$C$13=C18),$E$2:$E$13,"")))
Here's what I had before:
=ArrayFormula(MAX(IF(($B$2:$B$13="Smith")*($C$2:$C$13="Milk Chocolate"),$E$2:$E$13,"")))
Just like that, juggling with values in cells you reference you can quickly get different results based on different conditions without changing the formula itself.
Let's have a look at a few more functions and formulas examples handy for every-day use.
Suppose your data is written partly as numbers and partly as text: 300 euros, total – 400 dollars. But you need to extract numbers only.
I know just a function for that:
It pulls the text by mask with a regular expression.
The text in my case is a cell with a data (A2). And I use this regular expression: [0-9]+
It means that I'm looking for any quantity (+) of numbers from 0 to 9 ([0-9]) written one after another:
If numbers has fractions, the regular expression will look like this:
"[0-9]*\.[0-9]+[0-9]+"
for numbers with two decimal places
"[0-9]*\.[0-9]+"
for numbers with one decimal place
Formulas within the text help getting a neatly looking row with some totals – numbers with their short descriptions.
I'm going to create such rows in lines 14 and 15. To start with, I merge cells in those rows via Format > Merge cells and then count the sum for column E:
=SUM(E2:E13)
Then I put the text I'd like to have as a description to double quotes and combine it with the formula using an ampersand:
="Total chocolate sales: "&SUM(E2:E13)&" dollars"
To make my numbers decimals, I use the TEXT function and set the format: "#,##0"
Another way is to use Google Sheets CONCATENATE function, like I used in A15:
=CONCATENATE("Total discount for customers: ",TEXT(SUM(F2:F13),"#.##")," dollars")
What if you upload the data from somewhere and all numbers appear with spaces, like 8 544 instead of 8544? Google Sheets will return these as text, you know.
Here's how to turn these values written as text to "normal numbers":
=VALUE(SUBSTITUTE("8 544"," ",""))
or
=VALUE(SUBSTITUTE(A2," ",""))
where A2 contains 8 544.
How does it work? The SUBSTITUTE function replaces all spaces in the text (check the second argument – there's space in double-quotes) with an "empty string" (the third argument). Then, VALUE converts text to numbers.
There are some Google Sheets functions that help manipulate text in your spreadsheets, for instance, change the case to sentence case. If you have something strange like soURcE dAtA, you can get Source data instead:
Let me explain that in detail. I take the first character in a cell:
=LEFT(A1,1)
and switch it to the upper case:
=UPPER(LEFT(A1,1))
Then I take the remaining text:
=RIGHT(A1,LEN(A1)-1)
and force it into lower case:
=LOWER(RIGHT(A1,LEN(A1)-1))
Lastly, I bring all pieces of the formula together with an ampersand:
=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))
Of course, there's much more Google Sheets has to offer. Don't be afraid of different complex formulas – just try and experiment. After all, these toolsets let us solve many different tasks. Good luck! :)
2 responses to "Google Sheets: everyday formulas examples"
how to micro run in google sheet?
how to connect excel sheet to google sheet automatically update.
Hello Prikshit,
You will find the info on how to use macros in Google Sheets in this Docs Editors Help page.
As for connecting Excel to Google Sheets, please read a detailed description of the options here.