Google Sheets Formulas and Basic Functions Questions and Answers 1 — Questions and Answers
Question 1: 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?
- =AVERAGE(C2:C50) (Correct answer)
- =AVERAGEA(C2:C50)
- =SUM(C2:C50)/50
- =MEDIAN(C2:C50)
Correct 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.
Question 2: In Google Sheets, what is the primary difference between the `COUNT` and `COUNTA` functions?
- `COUNT` only counts numeric values, while `COUNTA` counts both numeric and text values. (Correct answer)
- `COUNT` counts all cells in a range, while `COUNTA` excludes blank cells.
- `COUNT` is used for single columns, while `COUNTA` can be used for multiple columns.
- `COUNT` is an older function, and `COUNTA` is the newer, more efficient version.
Correct 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.
Question 3: 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?
- =JOIN("-", A2, B2)
- =CONCAT(A2, "-", B2)
- =COMBINE(A2, "-", B2)
- =A2 & " - " & B2 (Correct answer)
Correct 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.
Question 4: 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?
- =DATE()
- =TODAY()
- =NOW() (Correct answer)
- =CURRENT_DATETIME()
Correct 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.
Question 5: 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?
- =SUM(B:B)
- =VLOOKUP("T-Shirt", A:B, 2, FALSE)
- =SUMIF(A:A, "T-Shirt", B:B) (Correct answer)
- =IF(A:A="T-Shirt", SUM(B:B))
Correct 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.
Question 6: 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?
- =VLOOKUP(E1, A2:C10, 3, FALSE) (Correct answer)
- =VLOOKUP(A2:C10, E1, 3, TRUE)
- =VLOOKUP(E1, 3, A2:C10, "Exact")
- =VLOOKUP(C2:C10, E1, 1, FALSE)
Correct 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.
Google Sheets Formulas and Basic Functions Questions and Answers 1
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?