Google Sheets Formulas and Basic Functions Flashcards

A user has a list of sales figures in cells C2 through C50. Some cells are blank, indicating no sales for that day. To calculate the average sales for only the days where a sale was made, which function should be used?

Answer: =AVERAGE(C2:C50)

The AVERAGE function in Google Sheets calculates the arithmetic mean of a range of cells, and importantly, it ignores any blank or text-filled cells. This makes it the correct choice for finding the average of only the cells that contain numeric sales data. AVERAGEA would count text as zero, and dividing by a fixed number would be inaccurate.

In Google Sheets, what is the primary difference between the `COUNT` and `COUNTA` functions?

Answer: `COUNT` only counts numeric values, while `COUNTA` counts both numeric and text values.

`COUNT` is designed specifically to count cells that contain numeric values (including dates and times). In contrast, `COUNTA` counts all cells that are not empty, regardless of whether they contain numbers, text, booleans, or error values.

A project manager wants to combine a project name from cell A2 with a task description from cell B2, separated by a hyphen. Which of the following formulas will correctly achieve this?

Answer: =A2 & " - " & B2

While `CONCATENATE` or `CONCAT` can be used, the ampersand (&) operator is a common and straightforward way to join text strings and cell values. The formula `=A2 & " - " & B2` correctly takes the value from A2, appends a string literal consisting of a space, a hyphen, and another space, and then appends the value from B2.

You are creating a report and need to display the current date and time, which should automatically update every time the spreadsheet is opened or modified. Which function should you use?

Answer: =NOW()

The `NOW()` function returns the current date and time as a date serial number. This value is volatile, meaning it recalculates whenever the sheet changes or is reopened. The `TODAY()` function, by contrast, only returns the current date without the time.

A sheet contains a list of products in column A and their corresponding sales revenue in column B. To calculate the total revenue for only the products named 'T-Shirt', which formula is most appropriate?

Answer: =SUMIF(A:A, "T-Shirt", B:B)

The `SUMIF` function is designed for this exact scenario. It sums the values in a specified range (`sum_range`, which is B:B) based on a single condition applied to another range (`range`, which is A:A). The formula `=SUMIF(A:A, "T-Shirt", B:B)` correctly tells Sheets to look for "T-Shirt" in column A and add up the corresponding values from column B.

Which of the following formulas correctly uses the `VLOOKUP` function to find the price of a 'Laptop' (located in cell E1) from a table in the range A2:C10, where prices are listed in the third column of the range?

Answer: =VLOOKUP(E1, A2:C10, 3, FALSE)

The correct syntax for `VLOOKUP` is `=VLOOKUP(search_key, range, index, [is_sorted])`. In this scenario, `E1` is the search_key, `A2:C10` is the range to search within, `3` is the index (the third column, which contains the price), and `FALSE` specifies an exact match for the search_key.