5 Spreadsheet Formulas I Wish I Knew Before Attending College
Over half a billion users worldwide spreadsheets every year. Contrary to popular belief, anyone can use spreadsheets; you do not need to be a big shot professional. Read on to find out five simple spreadsheet formulas that can make your life easier in college and into your professional career.
If you haven’t even used a spreadsheet before, let’s take a quick look at what they are. A spreadsheet is an electronic document that manipulates data arranged in rows and columns.
There are many spreadsheet software applications that you can easily access on your computer; the most popular ones are Microsoft Excel and Google sheets.
Spreadsheet formulas make life easier by automatically manipulating mathematical data for you. Math can be intimidating for some but don’t worry; the spreadsheet does all the calculations for you.
-
SUM Function
The SUM is the most basic function in spreadsheets, and the function calculates the total of a range of values.
Use
=SUM(value1, [value2, …])
Example
You will probably use this function more than any other. Say, for instance, you want to know your aggregate test scores in your exams or list all your scores. The SUM function will work out the totals for you.
In this example, we just had to click on the cell that we want the total in, C7 then type;
=SUM(C2:C6)
This formula adds up the range of values in the cells C2 to C6
If you still don’t have the results of one of your courses, like literature, you can populate it later, and the total will be updated
-
AVERAGE Function
As the name suggests, the AVERAGE function calculates the average of a range of values.
Use
=AVERAGE(value1, [value2], …)
Example
As shown in the example below, you will find the AVERAGE function helpful in computing your average test score.
=AVERAGE(C2:C6) gives the average of all the values in the range in a similar way to the SUM function.
You may use this function to predict your scores or to help you study. If you aim to score a specific average value, you will know which areas you need to improve.
-
MAX and MIN Function
These functions can help you find the minimum and maximum values in a range of values.
Use
=MAX(value1, [value2], …)
=MIN(value1, [value2], …)
Example
As a student, there are many ways you can use this function, especially during assignment season and you want to compare numerical values. In the example below, assume you want to know your best and not-so-good scores.
=MAX(C2:C6) will display your highest score
=MIN(C2:C6) will display your lowest score
-
COUNT Function
If you have a large dataset, counting all of them can be pretty tedious. Luckily, you have the Count Function to help you; it counts all the numerical values in a range of cells.
Note the specification: only cells with numerical values.
Use
= COUNT(value1, [value2], …)
Example
Say you want to find out the number of items in your food budget, but you don’t know the price of each item yet.
= COUNT(B2:B10) will count the number of cells with (exaggerated to represent what it feels like to be a student) numerical values in the range.
COUNT will only give you the number of items with a price, and that is why for the first photo, the count result is 5. When you put numerical data in the rest of the cells, the result is updated to 9.
Luckily, there is another type of COUNT function that counts all the cells regardless of their value. Cool right? Just replace COUNT with COUNTA, as shown below.
= COUNTA(value1, [value2], …)
-
IF Function
The IF function manipulates your data using your predetermined logic. The advantage of this function is that it lets you set a condition for your logic.
Use
=IF(logical_test, [value_if_true], [value_if_false])
Example
As shown in the example below, you may want to find out what papers you will need to retake and which ones you have passed. In this case, we will set your pass mark for each test to be 35.
=IF(C6>35, “PASS,” “RETAKE”)
The logical condition was that the value in the cell should be greater than our pass mark of 35.
If the logical condition is true, then the cell value will be PASS. Otherwise, the value will be RETAKE.
You may also use the IF Function to test several logical criteria, referred to as Nested IF. In addition, you can combine the IF function with other functions, for instance, SUMIF, COUNTIF, Etc. Follow the link to find out some different ways of using the IF Function in Google Sheets.
Moving on From the Basics
These are some of the most simple formulas available for Google Sheets and Excel but they will help you get acquainted with spreadsheets to move onto more complex formulas throughout your college years.
