StudySmarter Excel Crash Course: Day 2 = Excel Functions

Day 2: Excel Functions: The Secrets of Functionlore

Welcome back to our StudySmarter Excel Crash Course. Welcome, old and new hands, to the quick-and-easy run-through of the most common Excel functions. Welcome, and get those aspirins ready.

Excel functions are used to calculate specific values. They differ from formulas because they can perform more complex operations predicated on various arguments and conditions (and even in a specific order). Arguments can be text, numbers, cell references, specific ranges, and logical values like TRUE or FALSE.

To indicate a function, you need to type = followed by the name of the function and relevant arguments. Let’s have a look.

PS If you missed, overslept, forgot, wilfully ignored, or oversaw our introduction to Excel formulas, you can check them out here.

IF Function in Excel

Name: IF (+)

Use frequency: 4/5

Danger: 4/5, definitely a shapeshifter

As the name suggests, the IF function proposes a series of conditions under which certain operations are performed. When writing an IF function, you specify those conditions and what happens if they are fulfilled and what happens if they are not.

=IF(C4>50,”Passed”,”Failed”)

This is a simple example of, say, calculating students’ grades on a Pass/Fail test. Points are written in C4; if they are higher than 50, the results in the selected cell will show that the student has passed, and vice versa.

=IF(E7=”Yes”,F5*0.0825,0)

This means that if the value of E7 is yes, the function needs to multiply values in F5 by 0.0825. The zero at the end of the function refers to what happens if E7 is any other value.

The great news is that newer MS Office updates also have the IFS function in case you have multiple cells you want checked and multiple conditions, such as this:

=IFS(F2=1,D2,F2=2,D3,F2=3,D4,F2=4,D5,F2=5,D6,F2=6,D7,F2=7,D8)

COUNTIF Function in Excel

Name: COUNTIF

Use frequency: 3/5

Danger: 3/5

As we’ve already established, IF is a shapeshifter. It can become IFS and can be added to other formulas and functions or propose a new set of conditions. In this case, cells will be counted if they meet the specified criteria:

=COUNTIF(C1:C5,”books”)

This formula counts cells from C1 through C5, which contain books. (Speaking of books, how about some spooky scary suspense for this Halloween season?)

=COUNTIF(A1:A14″>40″)+COUNTIF(B3:B8″<52″)

In this example, there are multiple conditions for counting that you can mark by + and by repeating the function with relevant arguments. Excel will count everything in the A range that is higher than 40 and those cells that are lower than 52 in the B range.

You can also add multiple criteria to COUNTIF by turning it into COUNTIFS:

=COUNTIFS(B5:D5,”=Yes”,B3:D3,”=Yes”)

SUMIF Function

Name: SUMIF

Use frequency: 4/5

Danger: 3/5

SUM does basic mathematical operations for you: addition, subtraction, multiplication, and division. You can add conditions for calculating by turning it into an IF function:

=SUMIF(A2:A5,”160000″,B2:B5)

Here, Excel will sum the value of cells in the A range if they are equal to 160000; if not, it will sum the B range. Notice that = is not necessary for the equation, as it is understood.

=SUMIF(B5:B9,”<>red”,C5:C9)

The <> refers to values that are not equal to red.

As always, for multiple criteria, you can turn this into SUMIFS.

A Few Other Excel Functions

Excel is not just about setting various criteria for calculations. There are other indigenous species out there. I say indigenous mainly because Excel is used to organise and structure data, perform complex calculations, and spare you the pain of manual mathematical labour. While Excel formulas prefer to deal with numbers, there are a few other examples worth knowing.

MATCH Function in Excel

Name: MATCH

Use frequency: 2/5

Danger: 2/5, possible lifesaver

The MATCH function searches for and matches a specified value in a range of cells and returns the relative position of the said value. IF is used if you need to know where exactly that value is in the Excel sheet (very helpful when you have tons of data).

You can find exact values or those close to them by adding

• 0 at the end of the function to find the exact value,
• 1 to find the largest value that is less than or equal to the specified one,
• and -1 to find the smallest value that is greater than or equal to the specified one.

=MATCH(10574,A2:A6,0). Here the result will be 5 because the selected value, 10574, is the fifth in line.

You can use the wildcard ? if you want to look for a specific value with, say, a few letters:

=MATCH(Bo?, A4:A25,-1)

This function will search for all values that start with BO in the A range or the smallest one close to it.

LOOKUP Excel Function

This function is when you don’t need to know precisely where something is but what it is. It looks up a value in a one-column or one-row range and returns the relative result in the selected cells.

This one looks up the value 4.19 in range A and gives the result of the corresponding item from range B, i.e. oranges.

Excel TEXT Function

Name: TEXT

Use frequency: 1/5

Danger: 2/5

The TEXT function allows you to change the value of numbers or convert them into text.

=TEXT(A1,”dd-mm-yyyy”)

In this basic example, the date from A1 will change into its textual form, e.g. 1 October 2022. You can do the same for days of the week by typing “dddd”, am/pm distinction (“H:MM AM/PM”), or longitude and latitude calculations =TEXT(123456,”##0°00’00””).

=TEXT(1234.567,”$#,##0.00″)

Here, the function will convert the text into currency with a two-decimal separator, i.e. 1,234.57.

Excel Functions for Excellent Functional Logic

When you have one of those super complex multilayer spreadsheets, it can get somewhat challenging to sort through it manually. Imagine if you had to find one single number in about two hundred cells! I know I’d give up.

Excel functions perform a series of complex operations. They can calculate, locate, or convert values based on various conditions. You can combine multiple functions by adding a + sign in the address bar. And, as always, don’t forget the equation sign 😉.

That said, we have reached the end of our crash course on excel functions and formulas. Of course, Excel can do many more things, but these should cover you for many of your spreadsheet endeavours. Stay tuned for our advanced course for more drama in the software world!