Here’s a comprehensive list of Google Sheets formulas organized by category. This list includes common functions and their descriptions to help you understand their uses.
1. Basic Mathematical Functions
- SUM(range): Adds all the numbers in a range.
- AVERAGE(range): Calculates the average of a group of numbers.
- MIN(range): Returns the smallest number in a range.
- MAX(range): Returns the largest number in a range.
- COUNT(range): Counts the number of cells with numeric entries.
- COUNTA(range): Counts the number of non-empty cells.
- COUNTIF(range, criteria): Counts the number of cells that meet a specific condition.
2. Text Functions
- CONCATENATE(string1, string2, …): Joins two or more text strings into one.
- TEXTJOIN(delimiter, ignore_empty, string1, string2, …): Joins text strings using a delimiter, optionally ignoring empty strings.
- LEFT(string, num_chars): Returns the leftmost characters from a string.
- RIGHT(string, num_chars): Returns the rightmost characters from a string.
- MID(string, start_num, num_chars): Returns a specific number of characters from a string, starting at a specified position.
- UPPER(string): Converts text to uppercase.
- LOWER(string): Converts text to lowercase.
- TRIM(string): Removes leading and trailing spaces from a string.
3. Date and Time Functions
- TODAY(): Returns the current date.
- NOW(): Returns the current date and time.
- DATE(year, month, day): Returns a date based on year, month, and day.
- DATEDIF(start_date, end_date, unit): Calculates the difference between two dates in specified units (e.g., days, months, years).
- DAY(date): Returns the day of the month from a date.
- MONTH(date): Returns the month from a date.
- YEAR(date): Returns the year from a date.
- WEEKDAY(date, [type]): Returns the day of the week as a number.
4. Logical Functions
- IF(condition, value_if_true, value_if_false): Returns one value if the condition is true and another value if it’s false.
- AND(condition1, condition2, …): Returns TRUE if all conditions are true.
- OR(condition1, condition2, …): Returns TRUE if at least one condition is true.
- NOT(condition): Reverses the value of a logical expression.
5. Lookup Functions
- VLOOKUP(search_key, range, index, [is_sorted]): Searches for a value in the first column of a range and returns a value in the same row from a specified column.
- HLOOKUP(search_key, range, index, [is_sorted]): Searches for a value in the first row of a range and returns a value in the same column from a specified row.
- INDEX(reference, row_num, [column_num]): Returns the value of a cell in a specified row and column within a range.
- MATCH(search_key, range, [match_type]): Returns the relative position of a specified value in a range.
- FILTER(range, condition1, [condition2, …]): Filters a range based on specified conditions.
6. Array Functions
- ARRAYFORMULA(array_formula): Enables the use of array formulas to process multiple rows and columns at once.
- TRANSPOSE(array): Transposes the orientation of a range (converts rows to columns and vice versa).
- SPLIT(string, delimiter): Splits a text string into separate values based on a specified delimiter.
7. Statistical Functions
- STDEV(range): Estimates the standard deviation based on a sample.
- VAR(range): Estimates variance based on a sample.
- CORREL(range1, range2): Calculates the correlation coefficient between two ranges.
- MEDIAN(range): Returns the median of a set of numbers.
8. Financial Functions
- PMT(rate, nper, pv, [fv], [type]): Calculates the payment for a loan based on constant payments and a constant interest rate.
- NPV(rate, value1, [value2, …]): Calculates the net present value of an investment based on a series of cash flows and a discount rate.
- IRR(cash_flows, [guess]): Calculates the internal rate of return for a series of cash flows.
9. Miscellaneous Functions
- IMPORTRANGE(spreadsheet_url, range_string): Imports a range of cells from a specified spreadsheet.
- GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date], [interval]): Fetches current or historical market data from Google Finance.
- SPARKLINE(data, [options]): Creates a miniature chart within a single cell.
Conclusion
These formulas are just a starting point, as Google Sheets offers many other functions and combinations to suit different needs. Understanding how to use these formulas effectively can greatly enhance your productivity and data management skills in Google Sheets.
