### Excel Formulas and Functions

*Formulas* and *functions* are very popular topics in **Microsoft Excel** assessment test. They’re also used very frequently in Excel interview. If you look at the types of questions being asked, a lot of them are related to fundamentals of Excel *formulas* and *functions*. In this tutorial, I am going to introduce you to *excel formulas* and *excel functions*, and we'll do some basic *operations* with both learning about key *functions* that are used as part of excel interview in this part of Excel assessment test. Based on the job candidate applies for, there are typically three levels of Excel assessment.

There are:

*Basic Excel Assessments**Intermediate Excel assessments**advanced assessment*

In this tutorial, we're going to focus on the basic level assessment questions which is typically a screening process for entry level or even experienced candidates which includes administrative assistants, retail store managers, sales representative, marketing representative, accountants, entry level people and a lot of other categories of job interviews which require Excel skills. We'll look at some questions asked as part of Excel interview or Excel assessment test. And I'll show you a couple next steps how you can learn more.

One of the fastest ways to find information about excel topics is to use Microsoft Excel built-in help by just pressing f1 button on the keyboard and then search for the topics you're looking for. A lot of articles in Microsoft help even have built-in videos which help you learn the fastest way possible.

# Excel Formula

Excel formula is used to perform basic calculations.

Let's perform simple Excel calculation:

**Put**cursor into the*Cell*E4**Type***equal*sign**Select**2**Add**+**Select**54- Hit
**Enter**

The result is 56.

You can use Excel formula to perform basic mathematical functions, for example, addition, subtraction, division, multiplication and a lot more.

Let's perform **Multiplication** using Excel formula:

**Put**cursor into the*Cell*E5**Type***equal*sign**Select**2**Add *****Select**3- Hit
**Enter**

The result of this mathematical operation is 6.

# Excel Functions

Function is a little bit different from formulas but they have a lot of similarity. You still need to type equal sign then the name of the function. Typically, you have mandatory arguments and it could be either cell reference, could be value, could be arranged and then you have list of optional values.

## SUM Function

SUM functions, you can trigger it in multiple ways. Let's start with the simple and fundamental ways to use SUM function.

**Type***Equa*l sign**Type***SUM***Open**parenthesis**Select**2**Add***Plus*sign**Select**3**Close**Parenthesis- Hit
**Enter**

It shows me the value 5 which is exact result of summing 2 & 3. That’s one way to use SUM function.

Another way is;

**Type***Equal*sign**Type***SUM***Open**Parenthesis**Hold***Ctrl*button and select values B4, B6, B8**Close t**he parenthesis- Hit
**Enter**and

Now we have the sum of three values and the end result of this is 17.

Another way is:

**Type***Equal*sign**Open**The Parenthesis**Select**by dragging the cursor to select the range of values B4 through B10**Close**the Parenthesis- Hit
**Enter**

Now, *SUM* of all values in the range B4 through B10 is 166.

So summing up values manually and typing everything manually is very cool but as you well aware, excel is all about productivity. Excel has couple of shortcuts, tips and tricks that it creators implemented in the tool. Because *SUM* is so popular, they created the whole *autosum* function on the Home tab.

**Select**the*Cell*E6**Click***Autosum***Highlight**the range B4 through B10- Hit
**Enter**button

Another way as you probably well aware there is a *formulas tab* in **Excel **which specifically designed to work with functions. In the *insert function* button, you can research functions. A lot of times you don't know which function you would like to use. For example, if you type *SUM* and click go, it shows *SUM* and all related functions like *SUMIF, SUMIFS* and some other ones which you probably will be able to research, it shows the syntax and shows the descriptions. And you can also click *Help on this function* and if you do that, it will bring you to Microsoft's Office website where you can a lot of times, watch video, read a lot about the function. So basically, it's the way for you to learn more about particular function.

## MIN Function

**Type***Equal*sign**Type***MIN***Open**the Parenthesis**Select**the range B4 through B10**Close**the Parenthesis- Hit
**Enter**

The minimum value is 2. That's the lowest value in this range.

## MAX Function

**Type***Equal*sign**Type***MAX***Open**the Parenthesis**Select**the range B4 through B10**Close**the Parenthesis- Hit
**Enter**

We have 85 which is the largest value in this range.

## COUNT Function

*Count* basically counts number of values in the range.

**Type***Equal*sign**Type***COUNT***Open**the Parenthesis**Select**the range B4 through B10**Close**the Parenthesis- Hit
**Enter**

## AVERAGE Function

**Type***Equal*sign**Type***AVERAGE***Open**the Parenthesis**Select**the range B4 through B10**Close**the Parenthesis- Hit
**Enter**

# 3 Excel Interview Questions

**SUM is used to calculate total number of values in the range?**

So it's a tricky question. They use the word *total* which is part of the description of the *SUM *function because it's a total number of values. The answer is **false** because *SUM functions* is used to calculate sum of values not number of values in the range.

**When using Excel functions you have to provide optional arguments?**

The answer is false again. As you are well aware that we have two types of arguments; we have mandatory arguments which typically presented in the first part of the function and then we have optional arguments that are typically presented after. So you do not have to supply optional arguments but you have to supply mandatory arguments. So that's why the answer is *false.*

**SUM function supports mathematical operations, cell references and ranges as arguments?**

The answer here is *true*. If you go back in this tutorial, you will see that you can type *SUM* and then cell reference B4+B6 or you can type *SUM* B4,B6 or you can type *SUM* B4:B6. That would represent all of these three conditions; mathematical operations, *cell* references and *cell* ranges as arguments.