Page 7 / 13

Functions

On this page, we'll learn more about the concept of function. What are functions, and what do they do? In the following pages, we will look closely at a few functions that will allow you to develop your use of Excel significantly. The functions handled are IF, VLOOKUP and XLOOKUP.

In the case of functions, we proceed as follows: In this chapter, we go over things related to functions in general and practice them only after that.

What is a function?

The function concept in Excel is similar to function concepts used in mathematics and programming but is more clearly related to spreadsheets. A Function is a calculation formula that determines the contents of a cell based on various data, especially the contents of other cells. Applying a function means using a calculation formula to certain data.

In Excel, a function consists of the function name and the arguments in parentheses that determine which data the function is applied. Examples of functions:

=SUM(A1:A10)	An invocation of a summation function, its argument being the cell area from A1 to A10. The function sums the values of the cells: A1, A2, A3, ..., A9* and A10. The Sum Function is one of the most popular functions in Excel.
=NOW()	An invocation of a function that returns the current date and time. The function does not include arguments, but you need to add the parentheses so that Excel recognizes the formula as a function.
=COS(2.5)	An invocation of the cosine function, the argument being the value 2.5. This function is common in mathematics but more rarely used in Excel.

If there are several arguments, they are separated from each other with a comma, as in mathematics and programming. For example, SUM(A1, A3, A5), which calculates the sum of the cells A1, A3 and A5. However, if the comma is used as a decimal separator, due to settings in Windows, arguments are separated with a semicolon, for example SUM(A1; A3; A5).

You can use one or more function calls in a formula that you start with the symbol =, for example =SUM(A1:A10). Confirm the formula in the normal way with Enter.

A function is added to the formula by either typing it in directly or selecting the function from the menu and creating arguments to it as instructed by Excel. Let's get acquainted with these two methods next.

Guide

### Do I use upper or lower case letters?

It's common to write Excel Functions using upper case letters, but lower case letters work just fine too (cos, sum, now).

Video

Creating a function by writing

When you start writing with a = and a letter, Excel will offer you function names that begin with this letter.

You can move through the function names with the arrow keys, which give you information about the function simultaneously.

Press Tab or double-click on the name of the function to select it. Then continue by writing the arguments or selecting the desired cells as arguments with the mouse.

Käyttäjä on kirjoittanut =L, ja Excel ehdottaa L-kirjaimella alkavia kaavoja. Niistä viides on LASKE.JOS

Käyttäjä valitsi kaavan LASKE.JOS ja kaavarivin alapuolella Excel auttaa kertomalla, mitä kaavaan kirjoitetaan seuraavaksi (alue; ehdot)

Below the formula, you can see which arguments will be added to the formula, and type , (comma) between the arguments.

Accept the formula with `Enter'.

The finished function can look like this:

=IF(B4=C4,""."Check")

Video

Adding a function from the Library

Funktiokirjasto löytyy Kaavat-välilehden vasemmasta reunasta

On the left of the Formulas sheet, there is the Insert Function button, which opens the function library.

When you add a function from the function Llbrary, a window opens where the arguments of the formula are written.

Käyttäjä on valinnut funktion kirjastosta, ja on auennut ikkuna, johon kaavan arvot syötetään

Add all necessary arguments and accept the formula with the OK button. The formula will automatically include , (comma) between arguments.

The finished function could look like this:

**=VLOOKUP(C11,$C$3,$D$7,2,TRUE)**

Editing a function

Regardless of how you added the function to the sheet, you can double-click on the cell and edit the formula by writing in it.

Käyttäjä on kaksoisnapsauttanut solua, ja muokkaa kaavaa

Alternatively, you can click on the Insert Function button in front of the formula bar.

Lisää funktio -painike sijaitsee myös kaavarivin vasemmalla puolella

The button opens a window where you can change the function information.

Kuva ikkunasta, joka aukeaa, kun napsautetaan Lisää funktio -painiketta

Function categories

In the Function Library group on the Formulas tab, the functions are arranged into categories.

Lisää funktioita -painike sijaitsee Funktiokirjaston oikeassa reunassa

The most commonly used classes are logical, search, mathematical and statistical functions.

You will find the functions you have recently used in the Most Recently Used list.

Moving on

Next, we can start looking at using functions in practice. We'll start with the IF function.

Are you ready?

Mark as complete:

=SUM(A1:A10)	An invocation of a summation function, its argument being the cell area from A1 to A10. The function sums the values of the cells: A1, A2, A3, ..., A9* and A10. The Sum Function is one of the most popular functions in Excel.
=NOW()	An invocation of a function that returns the current date and time. The function does not include arguments, but you need to add the parentheses so that Excel recognizes the formula as a function.
=COS(2.5)	An invocation of the cosine function, the argument being the value 2.5. This function is common in mathematics but more rarely used in Excel.