Within several months of working with Google Spreadsheets we had to use various formulas to analyze data. As expected, what can be done using MS Excel, can be resolved in Google Spreadsheets. But numerous attempts to solve problems using our favorite search engine only led to new questions with almost no answers.
Therefore, we decided to make your life easier and gain fame.
- 1 Briefly on the issue
- 2 Formula Errors
- 3 How to write formulas
- 4 Formulas
- 5 Text formulas
- 6 Logical and other
Briefly on the issue
For Excel or Google Spreadsheets to recognize a formula, you need to use ‘=’ in the formula bar (Image 1).
Next, introduce the formula using your keyboard or select the cells you’re going to work with using your mouse.
To identify cells use two types of designations:
- Alpha-numeric (letter=column; number=row), for example ‘A1’.
- In the R1C1 system both rows and columns are marked using numbers. In this system the cell address ‘B3’ will look like R3C3 (R=row, C=column). Scripts use both styles.
Our output value will display where the formula (for example, ‘=SUM (A1:A10)’) is introduced.
The general principle of the RC formula is shown on Image 2.
As can be seen from Image 3, cells with value correspond with cells in which we used ‘=’. To preserve the aesthetic appearance of the formulas, we use  symbols (RC instead of RC), though it’s not necessary.
Link types (types of addressing)
To address cells, use links of 3 types:
- Relative links (for example, A1);
- Absolute links (for example, $ A $ 1);
- Mixed links (for example, $ A1 or A $ 1, they are half relative, half absolute).
The ‘$’ sign refers to the type of link. Differences between various types of links can be seen by pulling the autocomplete marker in an active cell or cell range that contains the formula with references.
A relative link ‘remembers’ how far (in rows and columns) you clicked, relative to the position of the cell where you put ‘=’ (shift in rows and columns). Drag down the autocomplete marker and the formula will be copied to all cells through which we stretched it.
As mentioned previously, if you drag the autocomplete marker with the formula containing relative links, the table will count its addresses. If the formula contains absolute links, their address will remain unchanged. Simply put – the absolute link always refers to the same cell. To make a relative link absolute, put the ‘$’ sign before column letter and row address (for example, $A$1). A faster option – highlight the relative link and press ‘F4’ – Spreadsheet will automatically place the ‘$’ sign. Click twice and the link will become a mixed type – A$1, if you use it the third time – $A1, fourth time – the link will become relative again.
Mixed links are half absolute, half relative. The ‘$’ sign stands either before the column letter, or before the row number. This type of links is the most difficult to understand. For example, the cell has ‘=A$1’ formula in it. It’s relative to column A and absolute to row 1. If you pull the autocorrect marker on this formula up or down, the links in all copied formulas will point to A1 and become absolute. However, if we drag the marker left or right the links will behave like relative, meaning that Spreadsheet will recalculate their addresses. Thus, formulas created using autocorrect will use the same row number ($1) but change the meaning of the column letter (A, B, C…).
Let’s take a look at the example of combining cells along with multiplying them by a certain coefficient.
This example provides the existence of coefficient value in each calculated cell (cells D8, D9,D10…E8,F8…). (Image 4)
Red arrows show the direction of stretching the formula (can be found in C2) using autocorrect marker. Pay attention to the formula in cell D8. When dragging down, only the number symbolizing the row changes. When dragging right, only the column changes.
Let’s simplify the example using the ‘$’ sign (Image 5).
It’s not necessary to always secure all rows and columns, sometimes only one of them is fixed in place. (Image 6)
You can find information on all formulas at support.google.com.
The proceeded data in the formulas shouldn’t be located in different documents, this can be done only with scripts.
If you write the formula incorrectly, you’ll be notified with a comment about a syntax error in this formula (Image 7).
Besides syntax errors you can be notified about math errors, such as dividing by 0 (Image 7) and other (Image 7.1, 7.2, 7.3). To see the notification which shows the occurred error, hover the cursor over the red triangle at the top right corner.
For convenience, let’s mark all cells with formulas purple. To see the formulas ‘live’ click Ctrl+’ or select View>All formulas from the menu above (Image 8).
How to write formulas
The wording on formulas in the handbook and in formulas currently used in work differ. This lies in the fact that currently we use a semicolon instead of a comma in all formulas (changes took place about half a year ago). To see to what the formula links (Image 9), click on the formula bar to the right of the Fx sign (Fx is located under the main menu on the left).
For proper operation of formulas they must be written in Latin letters, Russian (Cyrillic) ‘A’ or ‘C’ and Latin ‘A’ or ‘C’ are two different letters.
We won’t be explaining operations of addition, subtraction, etc. in detail, but they will help understand the basics. You will find a link to the document with all formulas at the end; for now we’ll just focus on the following screenshots.
Addition, subtraction, multiplication, division
- Description: formulas for addition, subtraction, multiplication and division.
- How the formula looks: ‘Cell_1 Cell_2’, ‘Cell_1 – Cell_2’, ‘Cell_1*Cell_2’, ‘Cell_1 / Cell_2’.
- The formula itself: =E22 F22, =E23-F23, =E24*F24, =E25/F25.
We have initial data in range E22:H25 and the result in column D. Image 10 shows the header for all data that’ll be used.
- Description: formula for increasing all subsequent cells per unit (rows and columns).
- How the formula looks: =previous cell +1.
- The formula itself: =D26 + 1.
We have initial data for progression in cell D26 and the result in cells E26:H26 (Image 11). Used for numerating rows and columns.
- Description: formula for rounding numbers in cells.
- How the formula looks: =ROUND (cell with number); counter (how many positions after the comma should be rounded).
- The formula itself: =ROUND(E28;2).
We have initial data in cell E28 and the result in D28 (Image 12).
The ’ROUND’ operation is proceeded according to mathematical laws: if the number after the comma is 5 or more, the whole part is increased by one, if it’s 4 or less, it remains the same. You can round numbers using the above menu Format > Numbers > ‘1000,12’ 2 decimal characters (Image 13). If you need more characters, click Format>Numbers>Customised Decimals> specify the number of characters.
Sum if cells are not sequent.
Probably the most familiar function.
- Description: summing numbers from different cells.
- How the formula looks: =SUM (number_1; number _2; … number _30).
- The formula itself: “=SUM(E30;H30)”. Use ‘;’ if cells are different.
.We have initial data in cells E30 and H30 and the result is D30 (Image 14).
Sum if cells are sequent.
- Description: summing sequent numbers.
- How the formula looks: =SUM(number_1: number _N).
- The formula itself: “=SUM (E31:H31)”. Use ‘:’ if the range is continuous.
We have initial data in cell range E31:H31 and the result in D31 (Image 15).
- Description: the range is summed and divided by number of cells in the range.
- How the formula looks: =AVERAGE (cell with number or number_1; cell with number or number _2; … cell with number or number _30).
- The formula itself: =AVERAGE(E32:H32).
We have the initial data in cell range E32:H32 and the result in D32 (Image 16).
There are many other examples but we’ll move on.
In my opinion the most demanded text formula is for merging text values.
There are several options for its execution:
Concatenating text values (formula)
- Description: concatenating text values (option A).
- How the formula looks: =CONCATENATE (cell with number/text or text_1; cell with number/text or text _2; …, cell with number/text or text _30).
- The formula itself: =CONCATENATE(E36;F36;G36;H36).
If you want to use the range, remember that it’ll sum all cells sequentially and if you want to sum cells in a certain order you need to specify it using ‘;’.
We have initial data in cell range E36:H36 and the result in D36 (Image 17).
Many use Google Forms (Insert > Form) to conduct employee surveys or create opinion polls. After filling the form the data is presented in the table. You can use different formulas for working with data, for example, for merging Name and Surname.
Concatenating numeric values
- Description: merging text values without using special formulas (option B – writing a formula of any difficulty yourself).
- How the formula looks: ‘=cell with number/text_1&’, ‘&cell with number/text_2&’, ‘&cell with number/text_3&’, ‘&cell with number/text_4′ (‘ ‘ – space and & sign mean merging; all text values are written in quotes).
- The formula itself: =E37&" "&F37&" "&G37&" "&H37.
We have initial data in cell range E37:H37 and the result in D36 (Image 18 – merged numbers).
Concatenating numeric and text data
- Description: merging text values without using special formulas (option C – mixed type).
- How the formula looks like: = "text_1 " &cell_1&"text_2"&cell_2&"text_3"&cell_3
- Important: text in quotes will remain unchanged for the formula.
- The formula itself: ="1 more " &E38&" used "&F38&" as WE "&G38.
We have initial data “1 more”, “used”, “as WE” in cell range E38:G38, so it’s wise to use this type of formula with the results in D36 (Image 19).
Merging text and numeric value.
Logical and other
Transferring data from sheets of the same file.
We are going to discuss the most interesting in my opinion functions: logical and other.
One of the most necessary formulas:
- Description: Transferring data from sheets of the same file (in Excel you can transfer from a sheet of one book into its other sheet, as well as, into a sheet of another book).
- How the formula looks like: = "Sheet_Name"!cell_1
- The formula itself:=Data!A15 (Data – sheet, А15 – cell in this sheet).
We have initial data in Data sheet in cell A15 (Image 20) and the result in Formula sheet in cell D41 (Image 20.1).
Most of the programs for working with tables have two types of array formulas: for multiple cells and for one cell. Google Spreadsheets divides these types into two functions: CONTINUE and ARRAYFORMULA.
Array formula for multiple cells allows the formula return multiple values. You can use them even without noticing, simply by entering a formula which returns multiple values.
Array formula for one cell allows to enter formulas using array input instead of output data. Including formula in function =ARRAYFORMULA allows you transmit arrays or ranges to functions and operators that, as a rule, use only arguments that don’t belong to the array. These functions and operators will be applied one by one for each entry in array and return a new array with output data.
If you want to study this issue in detail, visit support.google.com.
Simply speaking, to work with formulas that return data array and avoid syntax errors, you must include them in array formulas.
Summing cells with IF
To operate logical formulas that usually contain large data arrays, they are included in array formula – ARRAYFORMULA.
- Description: summing cells with IF (SUMIF formula).
- How the formula looks: = SUMIF(‘Sheet’!range; criteria; ‘Sheet’!total_range)
To explain the formula, let’s take a look at the following example:
3 people have to buy products according to the list and pay only once. After the products went through the cash register, we got a full list of products (Image 21) in column A and their quantity in column B.
Our task is to find out how the printed fiscal check will look like.
We have initial data in Data sheet (Image 21) and the result in Formula sheet in column D (Image 22). Columns E, F, G show arguments applied to the formula, and column H contains the formula that’s located in D and calculates the result.
The example above show how the formula ‘SUMIF’ works with one condition but usually several conditions are applied.
SUMIF with multiple conditions
Let’s take our task with products to another level.
The party just started and after a phone call from your friends you realize that there’s not enough drinks and you need to buy more. Each guest should bring a drink with them. We must find out the number of beer bottles that our friends need to bring.
- Description: SUMIF with multiple conditions.
- How the formula looks: = SUMIF(‘Data’!range_1′&‘Data’!range_2; criteria_1&criteria_2; ‘Data’!total_range).
- The formula itself:=(ARRAYFORMULA(SUMIF((Data!E:E&Data!F:F);(B53&C53);Data!G:G)))
We have initial data in Data sheet (Image 23).
For example, the Formula sheet in cell B53 (criteria_1=Beer) should contain the name of the drink and cell C53 (criteria_2 = 2) points at the number of friends who’ll bring beer. In the end, cell D53 will contain the result – 15 bottles of beer (Image 23.1).The formula will be defied by two criteria – beer and number of friends.
If there are more positions, rows 16 and 21 (Image 24), the number of bubbles in column G is summed (Image 24.1).
Let’s give a more interesting example.
The party continues and you remember that there should be a cake. But not just some ordinary cake – no, it should be the best cake with different ingredients but, as ill luck would have it, they’re encrypted under numeric values. Now our task is to buy spices that are sold separately in small packages. Our cook encrypted the necessary amount in columns A and B (we calculate in nearby columns). (Image 25.1)
Each spice has an index number: 1,2,3,4. (Image 25).
Our task is to count the number of repeating values, in our case, numbers from 1 to 4 in column B and defy the share of each spice.
- Description: counting the number of repeating values in large arrays with multiple conditions.
- How the formula looks: COUNT IF (‘Formula’!range_A55:А61+’Formula’! range _B55:B61; ConditionА”Spices”+ ConditionB”number from 1 to 4”; Sheet”Formula’! range _B55:B61)/ ConditionB ” number from 1 to 4”)
- Explaining the algorithm: Find cells with ‘Spices’ in column A. In column B sum the number of cells with repeating numbers that match the number in cells E58:E61. The results will be shown in cells F58:F61. Now in front of each spice name you’ll see its number. Column G will contain the percent of each spice.
- The formula itself: =((ARRAYFORMULA(SUMIF(‘Formula’!$A$55:$A$61&’Formula’!$B$55:$B$61; $F$55&$E59;’Formula’!$B$55:$B$61)))/$E59)
We have initial data in range A55:B61, selection criteria in cells F55 and E59:E62, and the results in cell range F59:F62 (counting numeric value repetitions in case of matching conditions).
- Description: counting the percentage of spices.
- How the formula looks like: Number*100% / Total_number
- The formula itself: =F58*$G$56/F$56
In the end we have the sum of repetitions and share.
To write a formula correctly, you must be vivid about what you have and want to get and in what form. Probably, for this you’ll have to change the initial data.
Let’s take another example.
Counting values in merged cells.
If the formulas use values in ‘merged cells’ then you indicate the first cell for merged data, in our case it’s column F and cell F65 (Image 26).
- Description: formula for counting values with symbol ‘@’.
- How the formula looks: COUNT IF (Column F in Formula sheet has text with ‘@’).
- Formula: =COUNTIF(‘Formula’!F65:F68; "*@*").
Finally we came down to the most horrible formulas.
Counting numbers in arguments list
There are several types of such calculations, they are mostly used for large tables where you need to count identical words or value of numbers. With a correct understanding of these formulas you can create great things, such as: count words excluding word exceptions. You’ll find more examples below.
- Description: counting cells containing numbers without text variables.
- How the formula looks like: COUNT(value_1; value_2; … value_30)
- Formula: =COUNT(E45;F45;G45;H45)
We have initial data in cell range E70:H70 and the result in D70 (Image 27 – counting cells containing numeric values in ranges with cells containing text).
Cells with text and numbers are not included.
Counting cells containing numbers with text variables.
- Description: Counting cells containing numbers with text variables.
- How the formula looks: COUNTA(value_1; value_2; … value_30)
- The formula: =COUNTA(E46:H46)
We have initial data in cell range E71:H71 and the result in D71 (Image 28 – counting range values).
The formula also counts cells containing only punctuation marks, tabulations, but doesn’t count empty cells.
Replacing values with conditions
- Description: replacing values with conditions.
- How the formula looks: "=IF(AND((Condition1);( Condition2)); Result equals 0, if conditions 1 and 2 are true; if not, the result equals 1)"
- Formula: "=IF(AND((F73=5);(H73=5));0;1)"
We have initial data in cells F73 and H73 and the result in D73 (If F73=5 and H73 =5 then D73=0, else 1). (Image 29).
Let’s complicate our example.
Count the number of cells that contain time frames excluding words ‘autoresponse’, ‘busy’, ‘-’.
- How the formula looks: "=COUNTA(Range_А)-COUNTIF(Range_А; "autoresponse")-COUNTIF(Range_А; "-")-COUNTIF(Range_А; "busy")"
- Formula: =COUNTA($E74:$H75)-COUNTIF($E74:$H75; "autoresponse")-COUNTIF($E74:$H75; "-")-COUNTIF($E74:$H75; "busy")
We have initial data in cell range E74:H75 and the result in D74 (Image 30).
So, we came to an end of our review of Google Spreadsheets formulas and I hope I managed to help you understand some aspects of analytical work with formulas. Each formula was created in blood, sweat and tears. I hope you liked my articles and stated examples.
Here’s a little bonus for you. I wish the developers will forgive me.
‘Document killer’ formula
If you wish to hide your document from everyone else permanently, this formula is for you.
- How the formula looks: "=(ARRAYFORMULA(SUMIF($A:$A&$C:$C;$H:$H&F$2; $C:$C)))". $H:$H regulates the spreading of the formula. After the formula is launched (Image 31), it’ll continue to multiply the function CONTINUE (cell; row; column) in cells below.
The formula repeatedly adds formulas to columns. To kill the document you need to try, create an N-number of cells and enter the formula to the first cells of N-number of columns. That’s it! Nobody will ever be able to edit or check the document.
Google Help on timeout and limitations.
The promised document was used as a base: http://goo.gl/ctKD7.
See you again!