Spreadsheet Formulas Quiz

Spreadsheet Formulas

• VLOOKUP: Searches a table's first column for a value and returns a value from a specified column. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• SUM: Adds values in a range of cells. Syntax: =SUM(cell_range)

• IF: Performs a logical test and returns different values based on the test result. Syntax: =IF(logical_test, value_if_true, value_if_false)

• COUNT: Counts numeric cells in a range. Syntax: =COUNT(cell_range)

• AVERAGE: Calculates the average of a range of cells. Syntax: =AVERAGE(cell_range)

• SUMIF: Adds cells based on a specified criterion. Syntax: =SUMIF(range, criteria, [sum_range])

• INDEX: Returns the value in a specific cell of a range, based on row and column numbers. Syntax: =INDEX(array, row_num, [column_num])

• MATCH: Finds the relative position of a value within a range. Syntax: =MATCH(lookup_value, lookup_array, [match_type])

• CONCATENATE/CONCAT: Joins multiple text strings together. Syntax: =CONCATENATE(text1, [text2],...) or =CONCAT(text1, [text2],...)

• COUNTIF: Counts cells meeting a specified criterion. Syntax: =COUNTIF(range, criteria)

• IFERROR: Handles errors from a formula by returning a specified value. Syntax: =IFERROR(value, value_if_error)

• LEFT/LEFTB: Extracts characters from the left side of a text string. Syntax: =LEFT(text, num_chars) or =LEFTB(text, num_bytes)

• RIGHT/RIGHTB: Extracts characters from the right side of a text string. Syntax: =RIGHT(text, num_chars) or =RIGHTB(text, num_bytes)

• MID/MIDB: Extracts a specific number of characters from within a text string, starting at a specified position. Syntax: =MID(text, start_num, num_chars) or =MIDB(text, start_num, num_bytes)

• LEN: Returns the number of characters in a text string. Syntax: =LEN(text)

• DATE: Creates a date serial number. Syntax: =DATE(year, month, day)

• NOW: Returns the current date and time. Syntax: =NOW()

• INDIRECT: Converts text to a cell reference.Syntax: =INDIRECT(ref_text)

• TRIM: Removes spaces from a text string. Syntax: =TRIM(text)

• SUBSTITUTE: Replaces specific text in a string. Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])

• RANK: Determines the rank of a number within a range. Syntax: =RANK(number, ref, [order])

• MAX/MIN: Finds the largest/smallest value in a range. Syntax: =MAX(cell_range) or =MIN(cell_range)

• ROUND: Rounds a number to a specified number of decimal places. Syntax: =ROUND(number, num_digits)

• NETWORKDAYS: Calculates the number of weekdays between dates. Syntax: =NETWORKDAYS(start_date, end_date, [holidays])

• TEXT: Formats a value as text. Syntax: =TEXT(value, format_text)

• RAND: Generates a random number between 0 and 1. Syntax: =RAND()

• IFNA: Handles #N/A errors by returning a specified value. Syntax: =IFNA(value, value_if_na)

• HLOOKUP: Searches for a value in the first row of a table and returns a value from a specified row. Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• TODAY: Returns the current date. Syntax: =TODAY()

• OR/AND: Combine multiple logical tests. Syntax: =OR(logical_test1, [logical_test2],...) or =AND(logical_test1, [logical_test2],...)

• DATEVALUE: Converts date text to a date serial number. Syntax: =DATEVALUE(date_text)

• ROUNDUP/ROUNDDOWN: Rounds a number up/down, respectively. Syntax: =ROUNDUP(number, num_digits) or =ROUNDDOWN(number, num_digits)

• ISNUMBER: Checks if a value is a number. Syntax: =ISNUMBER(value)

• IFBLANK: Checks if a cell is blank. Syntax: =IFBLANK(range, value_if_blank)

• PROPER: Capitalizes the first letter of each word in a text string. Syntax: =PROPER(text)

• MEDIAN/MODE: Calculates the median/most frequent number in a dataset. Syntax: =MEDIAN(number1, [number2],...) or =MODE(number1, [number2],...)

• DAY/MONTH/YEAR: Extracts the day, month, or year from a date. Syntax: =DAY(date) or =MONTH(date) or =YEAR(date)

• TRANSPOSE: Reverses the rows and columns of a range. Syntax: Enter the formula, select a range, and press Ctrl + Shift + Enter.

• PMT: Calculates a loan payment. Syntax: =PMT(rate, nper, pv)

• PRODUCT: Multiplies values in a range. Syntax: =PRODUCT(number1, [number2],...)

• STDEV: Calculates the standard deviation. Syntax: =STDEV(number1, [number2],...)

Test your knowledge of key spreadsheet formulas including VLOOKUP, SUM, IF, and AVERAGE. This quiz covers essential functions that enhance your ability to analyze data effectively using spreadsheets. Perfect for students and professionals looking to master spreadsheet techniques.