The 7 Basic Google Sheets Formulas
Google Sheets is a spreadsheet software. Formulas work various calculations in cells. Here are seven basic Google Sheets formulas for the absolute beginner.
Google Sheets works from Drive on your computer or phone. From your computer:
- Log in to your Drive.
- Right-click anywhere and click “Google Sheets”.
From your phone:
- Log in to the Drive app.
- Click the plus (+) icon at the bottom right
- Click “Google Sheets” and “Blank spreadsheet”.
Follow the syntax for every formula; always start a formula with the equals sign (=). Seven basic Google Sheets formulas you need to know:
The syntax for the SUM function: SUM(value1, [value2, …]).
SUM adds numbers and returns the result. The added numbers could be hardcoded—SUM(1, 2, 3)—or come from one or many cells—SUM(A1, B1, C1) or SUM(A:C, 100). Dividing by zero gives the error “#DIV/0!”, and using this value as a perimeter for SUM returns “#DIV/0!”.
The first parameter is a value (e.g., 23) or a range (e.g., “A1:A99” or “A1:C3”). The second onwards are extra values or ranges to add to the first parameter.
The syntax for the AVERAGE function: AVERAGE(value1, [value2, …]).
AVERAGE calculates the mean of hardcoded numbers—AVERAGE(1, 2, 3)—or dynamic or fixed values from cells—AVERAGE(A1, B1, C1) or AVERAGE($A$50:$A, $B, $C$50:$C) ignoring text. It fails with undefined values. AVERAGE works well with FLOOR and ROUND, to be discussed soon.
The parameters for SUM and AVERAGE are the same: values or ranges. Scroll to the end for the use of the dollar sign ($) to fix cells in formulas and other tips for beginners.
The syntax for the COUNT function: COUNT(value1, [value2, …]).
COUNT returns the number of numerical values in a dataset. For instance, COUNT(1, 2, 3) is equal to three as there are three values. If the cell A1 contains a number, COUNT(A1) is equal to one; otherwise, it is zero. If there is a date, a number, and some text in A1, A2, and A3, COUNT(A1:A3) is equal to one—for the number.
Here, value1 is the first value or range to consider, and so forth.
The syntax for the MAX function: MAX(value1, [value2, …]).
MAX returns the highest numerical value in the given dataset. For instance, MAX(1, 2, 3) is equal to three. Likewise, MAX(A1:A) will return the maximum value in the column A. Again, MAX(A1:1) will return the highest value in the first row. This is useful to get the highest score while grading, check the most lucrative deal, or view the highest-rated product.
The input for MAX is the same as that of SUM, COUNT, etc. according to syntax.
The syntax for the MIN function: MIN(value1, [value2, …]).
MIN returns the lowest value in the provided dataset. For instance, you may want to choose the lowest-costing travel package from your spreadsheet. Suppose the total cost for each trip is in the sixth column. Then MIN(F1:F) should return the one that will save you your money.
The parameters of MIN are a value(s) and/or array(s). Like all the previous ones, MIN will not work with undefined values, so when A1 = 1/0, MIN(A1:A) returns “#DIV/0!”.
The syntax for the ROUND function: ROUND(value, [places]).
ROUND “rounds” a number to a certain number of decimal places by standard rules. Integers such as 5, 10, and 45 are round numbers, are they not? However, 9.23 is not round. For it, we use the round function. ROUND(9.23) gives nine. Moreover, ROUND(9.55, 1) gives 9.6, but ROUND(9.55, 2) gives 9.55.
The first parameter of ROUND is a value or cell. The second is a numerical value that represents the number of decimal places to which to round.
The syntax for the FLOOR function: FLOOR(value, [factor]).
FLOOR rounds down a number to the nearest integer multiple of the significance factor. In short, it works similarly to ROUND but in the opposite direction. For instance, FLOOR(4.6) gives four. Changing the factor, you may get a fresh result. FLOOR(4.6, 3) returns three; FLOOR(16, 4) gives 16; and FLOOR(16, 5) gives 15.
The first input is an ordinary number or cell. The second is the factor whose multiples are to floor to. It may affect output.
Some detailed bonus tips to give you a head start in Google Sheets:
- Every formula starts with an equals-to sign, e.g. “=SUM(…)”, “=TODAY()”, etc.
- The dollar sign goes before the column ($A1) or row (A$1) number or both ($A$1) and fixes it. Otherwise, autofill and copy-paste change the cell—we will talk about them in the next segment.
- You can nest functions within other functions, e.g., FLOOR(ROUND(4.6), 6) is zero and SUM(AVERAGE(1,2,3), ROUND(FLOOR(SUM(1, 2, 3), 4)))
Thank you for reading. Stay with me for the next Google Sheets article with autofill, formatting, and conditional formatting.