Google Sheets: Nested Functions

If you are looking for how to use basic functions, I’ll be your huckleberry.

If you’re looking for a google sheet to practice on, here you go.

(Once open, click “File” in the top left corner, then “Make a Copy”.)

This tutorial will demonstrate how to combine multiple functions. This will allow you to perform multiple calculations within a single cell. Let’s get straight to it y’all!

_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-

As a refresher, here are the parts of a regular ‘ole IF() function:

  • logical_expression
  • value_if_true  
  • value_if_false (This part is optional)

 

Here is how the IF() function is set up:

=IF( logical_expression,  value_if_true,   value_if_false )

 

Here’s an example of an IF() function:

=IF( D3 = 83, “The cell’s value is 83“,  “The cell’s value is not 83“)

So, if the cell you are evaluating has the number “83” in it, this formula will return the text “This cell’s value is 83”. If the cell you are evaluating doesn’t have the number “83” in it, the function will return “This cell’s value is not 83”.

_________________________________________________________________

 

Now that we have the syntax (the fancy word for organization) for an if statement, we can begin combining if statements together into one formula. This is called “nesting”. 

Here is an example of a nested IF:

=IF(B6 = 4,The cell IS FOUR,IF(B6 = 5,The cell IS FIVE,none apply))

Nested  functions evaluate the innermost function first. So, “B6 = 5” is evaluated first, and then   “B6 = 4“.  If none of the logical expressions are true, the cell will contain the value of “none apply“. 

Below is a video of using this function:

Here is a more advanced nested formula:

=IF(E2 >89,A,  IF(E2>79,B,  IF(E2>69,C,  IF(E2>59,D,F))))

This may seem like an incomprehensible jumble of garbage, but it actually is quite easy to understand if it is organized differently. Here is the same formula horizontally:

=IF(E2 >89,A,  

IF(E2>79,B,  

IF(E2>69,C,  

IF(E2>59,D,

F))))

Not so bad is it? This formula looks to see what number is in cell E2 and assigns a letter to whatever cell you put this formula in. Here is an example of using this formula:

_________________________________________________________________

 

That was nice. However, it gets better. It has been reported that you can nest any number of functions together, and they don’t even have to be the same one! If you wish to combine an AVERAGE() function inside of an IF(), go ahead! As long as you know what the functions do, you can tailor your logic to accommodate any situation. Just remember, the formula begins from the innermost parenthesis, and works it’s way out. Take everything step-by-step and don’t be afraid to Google any questions you have.

I hope this opened up a new doorway in Sheets for you and, as always, thanks for reading!

_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-

 

 

File_000

Bio:

Cole Davis (@Cole_Davis64) is a student at Ash Grove High who attends classes at OTC for Computer Information Science. He dabbles in 3D animation in his spare time, and likes to help people make the most out of their computers. It’s not uncommon to find him browsing spicy memes, or making some internet.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s