Last Updated on 2021-09-23

Functions are predefined formulas in Excel. A formula is an expression that calculates values in cells.

To start a function, you have to start with “=”. And once we input some parameters, it will show us the result.

Before we get started, try to familiarize yourself with HOTKEYS.

## Basic Hotkey

- Insert columns or rows : Ctrl + “+”
- Delete columns or rows : Ctrl + “-”
- Look for and replace : Ctrl + F
- Inconsecutive select : Ctrl + mouse
- Fill in downward with forluma : Ctrl + D
- Fill in rightward with forluma : Ctrl + R
- Select all : Ctrl + A
- Save file : Ctrl + S
- Close file : Ctrl + W
- Zoom in and out : Ctrl + mouse wheel
- Switch between
**Relative**and**Absolute**reference : F4

## Common Function

### SUM

You can write like this : B3+B4+B5+B6+B7. Use the mouse to select the cells in range like **“=SUM( selected cells )”**

Another way to do it is “=SUM( B3 : B7 )”

### MIN

Purpose : To find out the minimum value within selected cells. Use the mouse to select the cells in range like **“=MIN( selected cells )”**

Another way to do it is “=MIN( B3 : B7 )”

### MAX

Purpose : To find out the maximum value within selected cells. Use the mouse to select the cells in range like **“=MAX( selected cells )”**

Another way to do it is “=MAX( B3 : B7 )”

### COUNT

Purpose : To count how many cells are there. Use the mouse to select the cells in range like **“=COUNT( selected cells )”**

Another way to do it is “=COUNT( B3 : B7 )”

The result will be 5(B3 to B7) in this case.

### COUNTA

Purpose : To count how many cells are there ( Only cells with a value that counts ). Use the mouse to select the cells in range like **“=COUNTA( selected cells )”**

Another way to do it is “=COUNTA( B3 : B7 )”

### AVERAGE

You can write like this : (B3+B4+B5+B6+B7)/5. Use the mouse to select the cells in range like **“=AVERAGE( selected cells )”**

Another way to do it is “=AVERAGE( B3 : B7 )”

### RANK

Purpose : To find out what is the ranking of the value in the selected cell. Use the mouse to select the cells in range like **“=RANK( the selected cell, reference range, ascending / descending sort )”**

Another way to do it is “=RANK( B3, $B$3 : $B$7, 0 / 1 )”

PS. Ascending sort : 1 / Descending sort : 0

## Advanced Function

### IF

Purpose : If it meets the condition, output A result, otherwise output B result. **“= IF ( Judgement/Condition , meet the condition output A result , otherwise output B result )”**

For example :

=IF( B3>=90 , “Grade A” , “Grade B” )

Then, the student is getting grade A if the number is bigger than or equal to 90, otherwise is grade B. Grade A and Grade B can also be replaced with cells that are filled with formula or even empty(it shows blank then).

However, it seems a bit unreasonable to give Bs to all the students who get grades under 90, doesn’t it? We can try to solve this by using multiple layers of IF. For example :

=IF( B3>=90 , “Grade A” , IF( B3>=80 , “Grade B” , “Grade C” ) )

And it can go on and on and on for infinity.

### IFS

Now we’re thinking that there are so many IF. Is there a way to solve this formula and make it easier?

Here comes IFS :

Purpose : If it meets the condition, output A result, otherwise output B result. It’s a clean and easy function for humans to read. **“= IF ( Condition A , Output result A , Condition B , Output result B , Condition C , Output result C , otherwise result D )”**

For example, we can rewrite IF to IFS as below :

= IF ( B3>90, “Grade A”, IF ( B3>80, “Grade B”, IF ( B3>70, “Grade C” , “Grade D” )))

= IFS ( B3>90, “Grade A” , B3>80 , “Grade B” , B3> 70 , “Grade C” , “Grade D” )

### SUMIF

Purpose : Sum up the values among selected cells with a designated condition such as the brand’s sales number.**“= SUMIF ( Condition Range , Designated Condition , Calculate Range )”**

For example :

= SUMIF ( $B$2 : $B$6 , “HP” , $C$2 : $C$6 )

Refer to the below chart, the result of the above formula will be 2600+2400=5000.

A | B | C | |

1 | Date | Brand | Sales |

2 | 10 | Dell | 8000 |

3 | 11 | HP | 2600 |

4 | 15 | HP | 2400 |

5 | 16 | Lenovo | 6000 |

6 | 22 | ASUS | 9000 |

### AVERAGEIF

Purpose : Calculate the average among selected cells with a designated condition such as the brand’s sales number.**“= AVERAGEIF ( Condition Range , Designated Condition , Calculate Range )”**

For example :

= AVERAGEIF ( $B$2 : $B$6 , “HP” , $C$2 : $C$6 )

Again, refer to the below chart, the result of the above formula will be (2600+2400)/2=2500.

A | B | C | |

1 | Date | Brand | Sales |

2 | 10 | Dell | 8000 |

3 | 11 | HP | 2600 |

4 | 15 | HP | 2400 |

5 | 16 | Lenovo | 6000 |

6 | 22 | ASUS | 9000 |

### COUNTIF

Purpose : Count the number of cells among selected cells with a designated condition such as the brand’s sales number.**“= COUNTIF ( Condition Range , Designated Condition )”**

For example :

= COUNTIF ( $B$2 : $B$6 , “HP” )

Refer to the below chart, the result of the above formula will be 2.

A | B | C | |

1 | Date | Brand | Sales |

2 | 10 | Dell | 8000 |

3 | 11 | HP | 2600 |

4 | 15 | HP | 2400 |

5 | 16 | Lenovo | 6000 |

6 | 22 | ASUS | 9000 |

### IFERROR

Purpose : To beautify the error message due to any reason but mostly caused by something went wrong in the formula. **“= IFERROR ( Under normal condition showing the correct result , When error messages occur showing the way you wanted )”**

Referring to the below chart, D6 cell is showing an error message. We could do this for example :

= IFERROR ( B2 * C2 , “Invalid Data” ) D6 will be showing “Invalid Data”

= IFERROR ( B2 * C2 , ” ” ) D6 will be showing ” ” – empty blank

A | B | C | D | |

1 | Item | Qty | Price | Invoice |

2 | Dell | 3 | 1200 | 3600 |

3 | HP | 5 | 1000 | 5000 |

4 | HP | 2 | 1500 | 3000 |

5 | Lenovo | 6 | 1300 | 7800 |

6 | ASUS | 7 | #DIV/0! |

### VLOOKUP

Purpose : To find out the specific values we wanted from a large amount of raw data and place them in our arrangement/will.**NOTE : It is very important to know how to switch between Relative and Absolute references by using “F4”.** **Absolute reference : $ was shown in front of it. Relative reference : There is no $ sign.**

Since Vlookup function is kind of complicated, I’ve found a video that is very easy to understand with his explanation.