10 Google Sheets Functions for Regular Use
Google Sheets is one excellent alternative to MS Office. People who make reports can get the most benefit from Google Sheets Functions. Those who create dashboards and business reports, like people who do analytics for marketing or online casinos like vulkan-vegas.pl, will be more efficient and more effective if they use Google Sheets for logging, tracking and reporting data. For those who want to become more efficient at calculations and reports, one must include some basic formulas in their arsenal of codes. Below are the 10 Google Sheets formulas for regular use.
The IF function is a conditional formula where you are telling the sheet to return a value of a condition that is met.
For example, you can tell the sheet that if the value of one cell is less than 10, the cell where the formula is on will return the value of “Less Than 10.” As such, you are looking at words and not numbers. You can also use this column for your pivot reports to show frequency statistics.
Choose the cell where you want to execute the query, double click and ENTER =IF(statement, what if true, what if false).
You use COUNT to count the number of cells that have content in an array or group of cells.
It is useful if:
- You want a cell to show how many data points you have in a group of arrays.
- You want to count how many cells have content to ensure that you did not leave anything blank.
While you can also count manually, it is better to use this formula if you count data points all the time.
The COUNTIF function only counts a cell as one value if it meets a condition. For example, take a look at the list below.
Supposing you have thousands of cells that have these labels, and you want to show how many are green, blue, red and pink, it is not practical to count them one by one. What you do instead is use the COUNTAIF function so the sheet will show you something like this:
- Green – 2
- Blue – 2
- Red – 1
- Pink – 1
VLOOKUP means vertical lookup. It is a function you use to look for a corresponding value in a set of columns that match your criterion.
For example, let us say you have two columns. The first column is an ID number, and the second one is a name, and you have more than 1,000 employees listed.
Now, what you have on another sheet is a list of 10 ID numbers, and you need the names. Instead of sifting through 1,000 names, you use VLOOKUP, and the sheet will return the corresponding name for each of your 10 IDs.
The SUM function is what you use to get the sum of a set of numbers. Typically, people would type each number on a calculator, then press the plus sign and then continue. What if you have 1,000 numbers to add?
If you want to get the total value of numbers in a group of cells, the SUM function is what you want. It takes only seconds, and you get an answer to what you need.
You use the SUMIF function if you want to add numbers only if their corresponding data meet a certain criterion. Below is an example (just imagine there are thousands of them):
- Green – 11
- Red – 12
- Green – 15
- Red 6
- Pink – 12
If you only want to sum the numbers of GREEN, then you need to use the SUMIF. The sheet will only get the sum of the numbers of that number met a criterion. In this case, the criterion is GREEN.
You use these Google Sheets Functions if you want to get the average of some data set, but you do not have the raw data. Instead of getting the average of the average, which is inaccurate, you get the sumproduct.
Below is an example data of sales (each entry is one sale):
- 1. Team A – $1,000
- 2.Team A – $1,000
- 3. Team A – $1,000
- 4. Team A – $1,000
- 5. Team A – $1,000
- 6. Team B – $3,000
- 7. Team B – $3,000
- 8. Team B – $3,000
- 9. Team B – $1,000
- 10. Team B – $1,000
The thing is, sometimes you do not get this report. What you see is this:
- Team A | 5 Sales | Average $1,000
- Team B | 5 Sales | Average $2,200
If these are the only two data points you have, and you want the average sale of the whole group, you want to use SUMPRODUCT. If you do, you will know that the average is $1,600.
It is a simple formula that puts together several cells to show a single value. If one column has a first name on each cell, and then another column has the last names for each cell, use CONCATENATE to show both names on only one cell.
You append the IFERROR in an existing formula to show you a specific text or number if the original formula yielded an error.
The AVERAGE Google Sheets Functions is what you use to get the mathematical average of specific data points in your cell. Like SUM, it is more efficient to use this formula than to calculate the average manually from a calculator.