Functions are pre-built formulas that perform specific calculations. Instead of writing out a long arithmetic expression, you call a function by name and provide the data it needs. Learning a small set of everyday functions covers the vast majority of common data tasks.
What Is a Function?
A function has a name and accepts one or more arguments — the inputs it needs to do its work. Arguments are placed inside parentheses, separated by commas (or semicolons, depending on your software's language settings).
For example: =SUM(A1:A10) adds all values in the range A1 to A10. The function name is SUM; the argument is the range A1:A10.
Essential Functions for Data Work
SUM — adds all values in a range.
Example: =SUM(B2:B50)
AVERAGE — calculates the arithmetic mean of a range.
Example: =AVERAGE(C2:C50)
COUNT — counts cells that contain numbers.
Example: =COUNT(D2:D50)
MIN — returns the smallest value in a range.
Example: =MIN(E2:E50)
MAX — returns the largest value in a range.
Example: =MAX(E2:E50)
IF — returns one value if a condition is true, another if it is false.
Example: =IF(F2>100,"Over budget","Within budget")
Function names and argument separators can differ across software and language settings. In some configurations, AVERAGE is called MOYENNE, SUM is SOMME, and arguments are separated by semicolons rather than commas. The concepts are identical — only the syntax differs. Always check the function reference for the software and language setting you are using.
Text Function Concepts
Text functions let you work with string data — cleaning, combining, or extracting parts of text values. Common operations include:
- Converting text to uppercase or lowercase
- Removing extra spaces from the beginning or end of a value
- Combining values from multiple cells into one
- Extracting a portion of a text string
These are especially useful when cleaning data imported from other systems.
Date Function Concepts
Date functions let you work with date and time values. Common operations include:
- Extracting the year, month, or day from a date
- Calculating the number of days between two dates
- Returning today's date
Dates stored as text rather than as date values will not work correctly with date functions. Always verify that date columns are recognized as dates by your software.
Lookup Concepts
Lookup functions find a value in one table and return a related value from another column. This is useful when you need to match records across two datasets — for example, looking up a category name based on a code, or finding a price based on a product ID.
The concept is straightforward: you provide a value to look for, a range to search in, and a column number to return from. The exact function name and syntax varies by software.
Handling Errors
Functions can return error values when something goes wrong — a division by zero, a lookup that finds no match, or a reference to an empty cell. Common error indicators include values like #DIV/0!, #N/A, #VALUE!, and #REF!. Each error type points to a specific problem. Investigate errors rather than hiding them.
Selecting the Simplest Suitable Function
Use the simplest function that correctly solves your problem. Complex nested functions are harder to read, harder to audit, and more likely to contain errors. If a simple SUM or COUNT answers your question, use it.
Key Takeaways
- Functions are named formulas that accept arguments and return results.
- SUM, AVERAGE, COUNT, MIN, MAX, and IF cover most everyday data tasks.
- Function names and separators may differ across software and language settings.
- Investigate errors — do not hide them.
- Use the simplest function that correctly solves the problem.
- spreadsheet functions
- SUM AVERAGE COUNT IF spreadsheet
- spreadsheet lookup function
- spreadsheet date functions