Last updated on March 5th, 2023 at 04:17 pm
If you are new to spreadsheets or Microsoft Excel, you may now be learning what a spreadsheet function or formula is. This article is an introduction to spreadsheet functions and will clarify the following:
- What a function is;
- The difference between a spreadsheet function and a formula;
- When to use formulas vs. functions; and
- How to use some of the most common functions in Excel.
If you are interested in other spreadsheet functions, visit Excel conditional functions to learn about the IF, COUNTIF, and SUMIF functions.
Are you a student preparing for CSEC and need help with your grammar, spelling, and punctuation? Get introduction to spreadsheets to explore these terms further.
So, What is a spreadsheet function, exactly?
A function is a predefined formula. What this means is that the software comes packaged with information about what is to be done when certain pre-programmed requests are made. This means that a user does not have to know the small details of how certain calculations are done in order to perform them. For instance, =MAX(A4:A6) will print out the highest number in cells A1, A2, A3, A4, A5, and A6.
Mixing a spreadsheet function and formula
Sometimes when using spreadsheets, we mix functions and formulas to bring about a desired output. For example, when finding the average, you may use a combination of the sum function and formula like this:
=sum(A3:E3)/4
Even though it uses a function in part, when used this way, it is considered a formula.
How to use common spreadsheet functions
When using spreadsheets, there are some functions that you will encounter on a regular basis. For the most part, they are quite similar to the functions covered in your math class. We will explore several of these functions and how to use them correctly in the upcoming sections.
Using the sum function in excel (sum functions in spreadsheets)
In mathematics, the word sum means to add. In a spreadsheet, we use the sum function to do exactly that. Like formulas, functions always begin with a = sign. When working with the sum function, we write it like this.
=SUM(C3:F3)
This function will add the content of cells C3, D3, E3, and F3. See the table below for clarifications.
Have you ever heard of absolute and relative cell addressing? Visit what is absolute and relative cell reference to learn more about when to use each in a spreadsheet.
The average function
In mathematics, average means to add all the numbers and divide by the total number of numbers. The same concept applies in Excel. Writing the average function tells the computer to take all the numbers in the identified range, add them together and divide by the number of numbers. We write the average function like this:
=AVERAGE(C3:F3)
This is much simpler than doing the formula. In fact, what is very nice about this is that you do not need to know the details of how to actually work averages in mathematics in order to use this average function.
The max function
This function finds the highest value in a range of cells. For instance, if we wish to find the highest grade for mathematics in the table above, we would use the formula:
=MAX(C3:C9)
This would return the answer 99. Try it on your computer. It is easy.
The min function
The min function is the opposite of the max function above. It checks the values in a range of cells and identifies and prints the lowest value. When we wish to find the lowest value, we write =min followed by the one bracket, the name of the range that contains the values followed by a closed bracket. So if we wished to find the lowest score for English in the table above, we would write:
=MIN(D3:D9)
This function returns the value 23.
The count function
The count is the function we use in spreadsheets when we wish to arrive at the total number of items in a row or column. We are able to count all of the items in a spreadsheet using different variations of the count function. The first count function we will look at allows us to count the values in a range.
Imagine that you have been asked to count the grades for Social Studies. This function looks like this:
=COUNT(E3:E9)
The result of this function, if used with the spreadsheet above, would be 7.
However, if you were to count the names of each child in the class, this count function would not work. This function works on values only. To count other types of data, we use the COUNTA function. So if you were asked to count the last names of all the students in the class, our function would look like this:
=COUNTA(A3:A9)
To explore more advanced use of the count function, visit advanced spreadsheet functions. Additionally, be sure to visit the introduction to spreadsheet formulas to learn about the difference between functions and formulas as well as how to create simple formulas in sreadsheet.
Before you go
We try our best to give you the most detailed and accurate information. However, if you have any questions or comments, be sure to leave a comment in the section below.