22/08/2024
Data Preparation Spreadsheet Functions
Both Excel and Sheets provide functions that allow us to work with the data to generate meaningful insights. Here is a list of some of the more helpful and common functions and how to use them.
# Function Explanation
1 SUM SUM allows you to sum any number of columns or rows by selecting them or typing them in, for example, =SUM(A1:A8) would sum all values from cell A1 to cell A8.
2 COUNT / COUNTA COUNT counts the number of cells in an array of cells (range of cells) that have a number value in them. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20). In this example, if five of the cells in the range contain numbers, the result is 5. COUNTA counts the number of cells that are not empty in a range. Eg. =COUNTA(A2:A7) Counts the number of nonblank cells in cells A2 through A7.
3 AVERAGE AVERAGE does exactly what it sounds like and takes the average (arithmetic mean) of the numbers you input. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.
4 IF The IF function allows you to make logical comparisons between a value and what you expect. In its simplest form, the IF function says: IF(If something is True, then do something, otherwise do something else). It allows you to output text if a case is valid. So an IF statement can have two results. The first result is if your comparison is True, and the second_ if _your comparison is False. For example, you could write =IF(A1>A2, “GOOD”, “BAD”), where A1>A2 is the case, “GOOD” is the output if true and “BAD is the output if false.
5 SUMIF, AVERAGEIF These functions are a combination of the SUM, AVERAGE functions with the attachment to IF statements. All of these functions are structured the same way, being =FUNCTION(range, criteria, function range). So in SUM, you could input =SUMIF(A1:A15, "GOOD”, B1:B13). This would add B1 through B13 if the values of A1 through A15 all said GOOD. Watch a short video showing how to use SUMIF here. Watch a short video showing how to use AVERAGEIF here.
6 COUNTIF Use COUNTIF to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list. In its simplest form, COUNTIF says: =COUNTIF(Where do you want to look?, What do you want to look for?) For example, =COUNTIF(A2:A5,“apples”) Counts the number of cells with apples in cells A2 through A5. The result is 2. =COUNTIF(A2:A5,A4) Counts the number of cells with peaches (the value in A4) in cells A2 through A5. The result is 1. More details and a video showing how to use COUNTIF can be found here.
7 VLOOKUP This function allows you to search for something in the leftmost column of a spreadsheet and return it as a value. An example of how to use this would be as follows: =VLOOKUP(lookup value, the table being searched, index number, sorting identifier). This function is a little more complicated so you can read an in-depth explanation of how it works here.
8 CONCATENATE CONCATENATE is not only a fantastic word to say, but it is also a useful function if you need to combine data into one cell. Say for example you had a first and last name, in cells A1 and A2 respectively. You would type =CONCATENATE(A1,“ ”,B2), which would combine the names into one cell, with the “ ” adding a space in between.
9 MAX & MIN These functions are very simple, just type in the column or row of numbers you want to search following the function and it will output the MAX or MIN depending on the function you use. For example, =MAX(A1:A10) would output the maximum numerical value in those rows. You can find more info on MAX here and on
