EXCEL TRAINING: FORMULAS

SébastienBancquart,
21/10/2014

TABLE OF CONTENTS
1 The calculation formulas......................................................................................................3
1.1 The simple formulas
1.1.1 Excel as a calculator............................................................................................3
1.1.2 Modify an existing formula.........................................................................................3
1.1.3 Note on the decimal symbol
1.1.4 Display of results
1.2 Utlisaton cell references...........................................................................5
1.2.1 First attempts...................................................................................................................5
1.2.2 Circular reference
1.2.3 Copy of formulas, movement, insertton,…...................................................................6
1.2.4 Copying of formula............................................................................................................7
1.3 References relatves and absolutes..................................................................................8
1.3.1 A concrete example...........................................................................................................8
1.3.2 The solutionton: the symbol $...................................................................................................8
1.3.3 The four types of references
1.4 Audit the formulas.........................................................................................................9
1.4.1 Background and Dependent Search.....................................................................9
1.4.2 Evaluateton the formulas.................................................................10

1.4.3 Espions.............................................................................................................................12
1.5 Name cases
2 The functionstons..................................................................................................................14

2.1 Backgroundtbasic registrations.......................................................................................................14

2.1.1 The functionton SOMME.................................................................14
2.1.2 Different modes of function inputton...........................................................................15
2.1.3 Average, Product,…

Page 1 of 28
 2.2 Fundton the date and time.....................................................................................17
2.2.1 bases
2.2.2 Functionstspecifics on dates......................................................................................18
2.2.3 Fundtspecifics related to the hours....................................................................................18
2.3 Foncttwe conditiontonnelles..........................................................................................19
2.3.1 Foncton SI........................................................................................................................19
2.3.2 Functionstnested SI
2.3.3 Fundtlogical ones...........................................................................................................20
2.3.4 Fundtwe are interceptington d’erreur............................................................................21
2.4 Fundtresearch center..............................................................................................22
2.4.1 ResearchV
2.4.2 ResearchH......................................................................................................................23
2.4.3 Limittons........................................................................................................................23
2.5 Fonctin mathematicstques..........................................................................................23
2.5.1 Functiontrounding

2.5.2 Logarithms and exponentstthey..........................................................24

2.6 Fonctwe standtstques.................................................................................................24

2.7 Fundtmatrix functions................................................................................................25
2.7.1 Classic calculations.............................................................................................................25
2.7.2 Enter matrix constants in your formulas....................................................25
2.7.3 Some functionstspecific specifications........................................................26
2.7.4 Some Miracles with the funcstmatrix operations..........................................................26
3 And after?
3.1 Others foundedtons: the help of Microsoft and the internet.....................................................27
3.2 Functiontrelated functionalities: pivot tables.........................................27
3.3 Create its own fundstVisual Basic and the macro editor.............................28

Page 2 of 28
1 THE CALCULATION FORMULAS
1.1 SIMPLE FORMULAS

1.1.1 EXCEL AS A CALCULATOR

Open a new Excel workbook. In a cell, type =3+4 then press Enter. Excel will you
display 7.
In another cell, type 3+4, then press Enter. Excel does not calculate anything.

To indicate to Excel that we want it to perform a calculation, we need to start the line with it.
by the symbol =. It is also possible to make it start the line with + or -.
Note that if you want to enter a text starting with one of these characters, Excel will
automatically try to interpret it as a formula, even if it has none
significance, which will cause errors. To avoid this behavior, you can do
start your text with an apostrophe.

Excel is capable of performing all common calculations. It follows the legal order of operations.
operators, namely:
( ) : parentheses take precedence over everything.1
: the power symbol.=4^3 will calculate 43, that is 4x4x4 = 64.
E: indicates scientific notation. 1.5E3 means 1.5 × 10.31500.
*, / : Multiplication and division.
Addition and subtraction

In case of doubt, operators of equal precedence are processed from left to right.

Thus, 3 + 4 * 5 gives the same result as 3 + (4 * 5) but a different result from (3 + 4) * 5.

1.1.2 MODIFY AN EXISTING FORMULA

There are several ways to modify an existing formula:
Double click in the box where your formula is. The result of the formula is
replaced by the formula itself. You can then modify it on the spot.

1
Note that one can use nested parentheses. Excel, like in mathematics, will calculate first
the content of the deepest parentheses. To create nested parentheses, use only
parentheses, and not, as in mathematics, brackets or braces. These symbols have a different
signification.

Page 3 of 28
 You can also use the bar located above your Excel sheet, which
It is particularly useful for editing long formulas.

If you are working on VERY long formulas that do not fit on one line, you
You can still improve readability, thanks to the arrow that is at the end.
right of the editing bar.

1.1.3 NOTE ON THE DECIMAL SYMBOL

According to the language preferences set on your computer, the decimal separator may
to be the point or the comma.
To simplify our lives, Excel modifies the behavior of the point of the keypad accordingly.
numeric to adapt it to this symbol.
As a result, when you enter decimal numbers, you can enter them with the
numeric keypad without worrying about your PC settings. However, the . key that is
finding two keys to the right of N will always give a point.

1.1.4 DISPLAY OF RESULTS

A simple formula like =1/3 can give an unpleasant display. You can
however easily adjust how many digits after the decimal point you want to display. In

On the Home tab, you can click on the following two buttons: to increase
or decrease this number.
This setting does not affect the calculation results; it is just a display. The content of the box
is always the actual result, with the hidden figures.

If, for any reason, you want to use the rounded results in a calculation,
you can use the rounding function that we will study later, or change this
behavior in Excel options.2

Main menu / options / Advanced options / Calculation of this workbook / Define calculation with precision
2

in the displayed format

Page 4 of 28
1.2 USE OF CELL REFERENCES
The benefit of working in a spreadsheet is the ability to perform calculations on data.
stored in the sheet.
1.2.1 FIRST TESTS
Enter value 3 in A1, 4 in A2.
In cell B1, type =A1+A2

Excel recognizes the boxes you have entered: it automatically colors the text and
frame the corresponding boxes in the same color. It's practical for orientation.
The framework is of course temporary: as soon as you leave box B1, it will
disappear.
Validate. The calculation is being carried out.

Vous pouvez maintenant modifier la valeur des casesA1etA2: le calcul se met

automatically updated.3

The formula itself is obviously modifiable as we already know how to do it.

Une autre méthode existe : placez-vous dans une autre case. Tapez=. Cliquez sur la caseA1.
Type+. Click on the box A2. Press Enter.

You can also create formulas using boxes that themselves contain
formulas.

Here, modifying the content of A1 will trigger the automatic recalculation of B1, and then that of C1.

3
If this is not the case, your Excel has switched to manual calculation mode. The option is disabled through the tab.
Formulas / Calculation Options / Automatic Calculation.

Page 5 of 28
1.2.2 CIRCULAR REFERENCE.

Erase everything4

In B1, enter the formula=A1+1.

In C1, enter the formula =B1+1.
In A1, enter the formula =C1+1.
You are getting an error:

Indeed, Excel cannot solve this calculation. A1 depends on C1, which depends on B1, which
depends on A1... It indicates to you that there is a problem. As long as this issue is not
eliminated, Excel will not perform ANY calculations related to these cells.

A quick way to find the cells involved in a circular reference is

to use the menu Formulas / Error Checking / Circular References:

1.2.3 COPY OF FORMS, MOVEMENT, INSERTION,…

Let's erase everything.

Let's recreate the example of the1.2.1Copy

the three relevant cells and paste them elsewhere in the
leaf. Observe the formula obtained.

Excel automatically adjusts the formulas.

It is also true when you move cells, insert or delete rows or
columns,…

If you prefer, you can also create a new empty tab.

4

Page 6 of 28
Excel adapts to all the changes you can impose on it, without ever destroying.
your formulas. Provided, of course, that you do not delete the cells that
intervene in your formulas.

1.2.4 COPYING OF FORMULAS

Let's quickly create a table of this type: (the exact numerical values do not have any
importance

In cell C2, we enter the formula =A2+B2.

The goal is to apply this formula to the entire column without having to retype it manually.

The simplest method: select the C2 box. In the bottom right corner of this
There is a small black square handle. Take this handle.5and slide it towards the
bas. Excel takes care of everything.

Here, in computing, is called Gizmo.

5

Page 7 of 28
1.3 RELATIVE AND ABSOLUTE REFERENCES
1.3.1 A CONCRETE EXAMPLE

Let's build the following table:

InB4, enter the corresponding formula :=A4*(1+B1)

Let's apply this formula to the entire column.
The results obtained are fanciful. Why? Let's look at the formula from line 5, by
example :=A5*(1+B2). Excel has successfully shifted the cells, as usual. However, in
In our case, as much as A4 should be shifted to A5, B1 should not move.
How to make Excel understand this difference?
1.3.2 THE SOLUTION: THE SYMBOL$
Cancel the last action to be left with just the first input formula.
We will modify this formula, thanks to the symbol $. This symbol can be interpreted
like "Don't move."

Page 8 of 28
Modify the formula to =A4*(1+B$1). Copy the formula down the entire column. Check that
Everything is OK.

1.3.3 THE FOUR TYPES OF REFERENCES

In the previous case, we placed the $ in front of the line number. This means:
When you transfer the formula, do not change the line number. You can modify the
column letter
Other solutions exist, depending on the position of the $. Here is the complete list:
B1: When copying, you are free to change the row and the column.
B$1: You can change columns. The row will always remain 1.
You can change the line. The column will always be column B
Do not change anything.

A tip: when editing the formula, the F4 key allows you to toggle between types.
the other

In exercise, create a multiplication table.

1.4 AUDIT OF FORMS

1.4.1 RESEARCH ON ANTECEDENTS AND DEPENDENTS

When working with complex formulas, the coloring of the cells is not always
sufficient to help us understand how a formula works, in particular
when we are not the author.
An interesting outlet is the search for backgrounds.
Let us place ourselves in the case studied in the chapter1.3.2Position yourself on square B4 and click, in

the Formulas tab, on Identify the precedents: :

Page 9 of 28
The arrows directly indicate the cells involved in our formula. If these
cells also contain a formula, so a second click on the same button will me
give the antecedents of the antecedents.

When you have completed your audit, to erase the arrows: click on 'Delete'

arrows:

A variant is the function 'identify dependents', which is right next to it. Let's test it.
for example starting from cell B1:

As before, a second click shows the dependents of the dependents if they

existent.
Remove the arrows once your audit is completed.
1.4.2 EVALUATION OF FORMULAS
To illustrate this function, we are going to create a more complicated page.
From a blank page, fill in column A as below:

In B1, enter the formula =A1+A2

Copy this formula from B1 to B4.
In C1, enter this formula: =B1+B2

Page 10 of 28
Report from C1 to C3

In D1 enter =C1+C2
Report from D1 to D2
Finally, in E1, enter =D1+D2. Phew!

It must contain the value 48. How did we arrive at this result?

Click on E1, then on Evaluate the formula The following window

opens:

We find the formula we entered, namely D1+D2. D1 is underlined. This

signifies that it is through him that Excel will begin its calculation evaluation.
Click on Evaluate. It replaces D1 with its numerical value, which is 20. D2 is in turn
underlined because it is the next step of the calculation.

We can click on Evaluate, as before, but we can also click.

on a detailed step-by-step basis. Excel will then show us how it calculates D2. This way we can
descend to the level of complexity that interests us:

Page 11 of 28
The Step Out key allows us to go up one level.

In another example, you can enter a formula of the type =3 + 5* 8 / 2 – 4^3 * 2 – 1 in order to
check the order of precedence of operators.

1.4.3 SPYING
An interesting last audit feature is the spy window. It can be utle.
when working on multiple tabs or large tables, to have permanently
under the eyes, the result of one or more precise formulas, wherever one is
is found in the binder.
This is the purpose of the "Spies Window" function. Open it from the Formulas tab.

Page 12 of 28
The 'Add a spy' button allows you to select a cell, the content of which will be
always visible in this folder. You can have as many spies as needed at the same time.
1.5 NAME BOXES
Which of the two following formulas is the most understandable?
A10*(1+$B$1)*(1-$A$2)
=A10*(1+VATRate)*(1-DiscountPercentage)

These two formulas, calculating a net price based on a gross price, are completely
identities. One of the two is just easier to read. How to create second formulas
by naming certain cells in our sheet.
The tool that allows us to do this is once again in the Formulas tab: it is

the name Manager outlet.

Make 'New':

Page 13 of 28
This window allows you to give a Name to a cell (which can be found in Refers to
a). This name will be valid in the indicated Zone. We can thus restrict if necessary.
the use of a name for a tab or for the entire workbook.
Comments are optional.
Once the name is defined, it can be used in formulas in place of the address of
cell.

It is a good habit to name the cells containing fixed values. The gain in
Legibility is generally not negligible.
Overall, it's a good idea to put these constants in cells.
rather than typing them in plain text in a formula.6

2 THE FUNCTIONS

Even if it is possible to do a lot of things with sums and products,

we have not seen, far from it, the range of possibilities of Excel in terms of calculation and
information processing. Excel has a very large number of functions, which will
allows us to perform significantly more complex calculations.
Some of these functions are shortcuts to operations that can be performed more or less.
simply with the tools already at our disposal, such as addition (some additions), the
average (one more division), or the standard deviation (more complicated, already...)

Others introduce new mathematical functions (trigonometric functions,

logarithms).
Others even allow directing a calculation based on the answer to a question.
‘functions if, errors,...) or to automatically select an object from a list in
function of a particular criterion (search functions).
Others are finally able to fill several cells at once (matrix functions).

It would be tedious and pointless to make an exhaustive list of all the available functions.
The Excel help file is there for that. We will just take a look at some of the most useful ones.
2.1 BASIC FUNCTIONS
2.1.1 THE SUM FUNCTION
In a new sheet, let's fill the first 5 rows of column A with some
numbers. We want to calculate the sum and place it in cell A6.
The first method would be to type this :=A1 + A2 + A3 + A4 + A5. It works. But
What do we do if we have several hundred cells to sum?

6
These numbers are called 'magic numbers in computer science'. At first glance, =A10 * 1.2 seems to be a
simpler formula to calculate VAT, for example. But what happens on the day when the rate changes? We need to
modify all the formulas in the calculation sheet, hence a source of errors. If you have a formula of the type
=A10 * (1 + VATRate), you just need to update the value in the VATRate cell and you're done.

Page 14 of 28
The solution is to make use of Excel's built-in functions.
The formula to type is :=sum(A1:A5)
Notice how Excel suggests various functions when you start typing.
It's very handy when you can't remember the exact spelling.
Likewise, once=sum(typed, Excel reminds you of the list of the function's parameters:

Here, it's simple: you need a number (the one called number1), and possibly others.
names, separated by ;. The brackets always indicate that the argument is optional.
We could thus have a sum of several ranges, such as for example
=sum(A1:A5;B1:B7)
2.1.2 DIFFERENT MODES OF FUNCTION ENTRY
The most immediate way to enter a function is to type its name, as we have
and you.
We can also, in the case of common functions, use the function 'sum'
automatic

This function automatically sums the selected range of data.

also allows, via the small arrow in the bottom right, to access some other functions of
base.

You can also edit a function with the function editor (sic !). We access it
with the FX button located to the left of the editing line:

A click on this button will open the following editor:

Page 15 of 28
From here, you can search with more or less ease (it is not the easiest tool)
efficace d’Excel…) la foncton qui vous intéresse. Vous pouvez aussi la sélectonner dans les
provided lists.
Below the list of functions, you have a short description of the selected function,
with the list of parameters. If the description is not clear, below you have
a blue link to online help. It is rather well designed and should provide you with the
necessary explanations.

Click OK. A setup wizard window opens.

Page 16 of 28
This one describes the function to you and allows you to edit each argument one by one.
The argument is described in detail. It is a very good entry point when you do not know.
not the syntax of a function.
Once your arguments are entered, press OK. The system writes the formula by itself.

Note that you can re-enter this assistant from existing formulas.
even if they were not created by this method. Edit your formula, put it
cursor between the parentheses and click the fx button again.
2.1.3 AVERAGE, PRODUCT,…
When one masters sums, other similar functions are available:
AVERAGE(): calculates the average of selected cells, and its variants:
GEOMETRIC.AVERAGE() (root nth you product of the terms) and
HARMONIC.AVERAGE()(inverse of the average of the inverses).
PRODUCT(): the product of all selected terms.
MIN(), MAX(): returns respectively the smallest and largest term of the series.
2.2 DATE AND TIME FUNCTIONS
2.2.1 BASES
Excel internally stores dates and times as numbers. This represents the
number of days since 1900.
It is therefore possible to perform calculations on dates and times.
For example, if A1 contains a date, =A1 + 1 gives the next day. =A1 + 1/24 adds a
hour.
Subtracting two dates gives the number of days/hours separating them.

Page 17 of 28
Technically, it is possible to multiply or divide a date by a number, or even
two dates between them. Excel will give you a result, which however will not be very
signification...

2.2.2 DATE-SPECIFIC FUNCTIONS

Here are some interesting functions:
=TODAY(): returns today's date. Don't forget the parentheses, even if there aren't any.
of argument.
=YEAR(), =MONTH(), =DAY(): respectively returns the year, month, and day of the month of
the date.
=WEEKDAY(): returns a number between 1 and 7 representing the day of the week
corresponding to the date. This function takes 2 arguments. The first is the date
considered. The second is a number indicating the calculation method:
1 : dimanche = 1, lundi = 2,…samedi=7
2 : lundi = 1, mardi = 2,…dimanche=7
3 : lundi = 0, mardi = 1,…dimanche=6
=WEEKNUM(): returns the week number corresponding to the date. Once again, the
the second parameter is the calculation method:
The week containing January 1st is the first week of the year; it is
numbered week 1.
The week containing the first Tuesday of the year is the first week.
of the year; it is numbered week 1. This system corresponds to the methodology
specified in the ISO 8601 standard, commonly called numbering system of
European weeks.7
14: the numbering that ACTUALLY corresponds to the ISO 8601 standard, with week 1
being the week containing the first Thursday.
2.2.3 SPECIFIC FUNCTIONS FOR HOURS
Similarly, certain specific functions can facilitate working with hours. Thus:
=NOW(): returns the current date AND time. Don't forget the parentheses. A
Note: this function is neither a clock nor a stopwatch, in the sense that you do not
you will not see its value change over time. However, its value is updated every
once a calculation is executed in the workbook.
=HOUR(), =MINUTE, =SECOND(): respectively return the hour, the number of
minutes and seconds of a date.
TIME(): takes three parameters: hours, minutes, seconds, and returns a
date.

7
This who correspond also directly à the function =WEEKNUM.ISO(). Cf :
hthttp://fr.wikipedia.org/wiki/ISO_8601#Num%C3%A9ro_de_semaineThe standard implementation of Excel contains
an error, it is based on the first Tuesday and not the first Thursday.

Page 18 of 28
To add or subtract hours, minutes, seconds from a date, you just need to
souvenir
A day has 24 hours. An hour has 60 minutes and a minute has 60 seconds.
A day has 1440 minutes and 86400 seconds.
To add a second, you need to add 1/86400.
For example :=A1 + 3/24 + 8/1440 + 32/86400 adds 1 hour, 32 minutes and 8 seconds to the
date contained in cell A1.
It is regrettable that there is no function on Excel like in other programming languages.
simple du type AjouteHeure(1,32,8).

2.3 CONDITIONAL FUNCTIONS

The objective of these functions is to be able to answer questions such as: 'the price of
The item costs €10. If the customer buys more than 5, they will only pay €8 each.
2.3.1 FUNCTIONS
In French, the question is: "If we have 5 objects or fewer, then the price equals the number"
of objects multiplied by 10. Otherwise, the price is equal to the number of objects multiplied by 8.

In Excel, we will use a function that allows us to make this choice automatically.
It is the function IF.
The syntax is as follows: :=IF(Condition; calculation if true; calculation if false)
An example: in A1, enter the value 3.
In B1, the following formula:
IF(A1 <= 5, A1 * 10, A1 * 8)
The result obtained is 30 (3 * 10, because 3 is less than 5).
In A1, type now 10. You should get 80 in B1.

The condition can involve as many cells and intermediate calculations as

necessary. It is always a comparison, which results in TRUE or FALSE.
The comparison operators used are:
equal
< : strictement inférieur
strictly greater than
<= : less than or equal to
>= : greater than or equal to

different

Page 19 of 28
When comparing numbers or dates, the result is the one that is logically
waiting.
When comparing text, it is the alphabetical order, character by character, that
give the result.8
A curiosity: if we compare text to numbers, by convention, the text is always
higher than the figures.

In order to understand how this function works, it may be helpful to practice.

with the step-by-step evaluation of formulas.
2.3.2 EMBEDDED FUNCTIONS

Let's take a concrete example: We want to validate a number between 1 and 10. If this number
is less than 1 or greater than 10, we want the system to respond that the
The number is too large or too small. Otherwise, let him tell us OK.

In French: if the number is too small, display too small. Otherwise, if it is too large, display
too large, otherwise display OK
In Excel, with two IF statements:

Enter a number in A1
In B1, the following formula: =IF(A1 < 1, "TOO SMALL", IF(A1 > 10, "TOO LARGE", "OK"))
taking good care not to forget any parentheses and being careful with the punctuation.
Test with different values of A1
A variant: modify the formula so that the system returns 1 for numbers that are too
pets, 10 for the too big ones, and the number itself when in the range.

Compare with the following formula:

=MAX(10,MIN(0,A1))
2.3.3 LOGICAL FUNCTIONS

How to simply respond to this question: If A1 is positive and if A2 is positive, then

Display OK in B1. Otherwise, display NOT OK?
Thanks to the functionET(). This function takes two conditions as arguments and returns TRUE.
when both conditions are true. Otherwise, it returns FALSE.
The formula is: =IF(AND(A1>0, A2>0), "OK", "NOT OK")
Similarly, we can display OK if either one is positive, thanks to the OR function.

8
Thus, it is important to understand that 11 < 1001 is true, while "11" < "1001" is false! Similarly, 11
"11" is false.

Page 20 of 28
Here are the main logical functions available:
AND(): true if both conditions are true
OR(): true if at least one of the two conditions is true
XOR() : exclusive or : true if one of the two conditions is true and only one.
NON(): takes a single argument. Switches true for false and vice versa.

Complete data on Boolean algebra, which allows for easy manipulation of these
concepts are easily found on the internet.9Here are just the main tables to
to know10

A1 A2 =ET(A1 ;A2) =OU(A1 ;A2) =OUX(A1 ;A2)

TRUE TRUE TRUE TRUE FALSE
TRUE FALSE FALSE TRUE TRUE
FALSE TRUE FALSE TRUE TRUE
FALSE FALSE FALSE FALSE TRUE

2.3.4 FUNCTION ESTET ERROR INTERCEPTION

Excel provides some useful test functions. These are the functions whose names start
by EST. They return TRUE or FALSE, and can therefore be used in a test in a
IF function
An example: type in A1 the following formula: =0/0. It obviously returns a
error.
In B1, for example, try this :=IF(ISERROR(A1),"Error","Correct calculation")

This type of function allows for an easy clean display.

Other interesting functions of this type:

EST.PAIR(), EST.IMPAIR(): returns true when the concerned number is respectively an
enter even or odd.
ESTNUM(), ESTTEXTE(): returns true when it refers to a numeric value or
to some text.

9
For example here:hthttp://en.wikipedia.org/wiki/Boolean_algebra_(logic)

Boolean algebra pros will see that two are missing, curiously absent from Excel. EQV, which is true
10

when both are true or both are false. Can be replaced by=NOT(OR(A1;A2)); and IMP, which is false
uniquement quand le premier terme est vrai et le second faux. Peut être remplacé par=OU(NON(A1) ;A2).

Page 21 of 28
2.4 SEARCH FUNCTIONS
These functions allow you to easily perform this kind of action: Returns the name of a
product knowing its code. They ask Excel to go through a table (which can
to be very tall) in order to find a very specific piece of data. Master these functions
allows finally to do without complex tools like Access when one has a database
simple data to build.
These functions come in two flavors: SearchVetSearchH depending on whether
search is conducted in the vertical or horizontal direction.
2.4.1 VLOOKUP
Let's start with a concrete example. Copy this table:

In cell B11: type the formula :=VLOOKUP(A11, A2:B9, 2, FALSE)

In A11, enter a numerical value.

Explanation: the function asks Excel to search for the first argument (A11) in the
table given in the second argument (A2:B9). Once found, return the value
which is located on the same line, but in the column whose number is given by the third
argument (2).
The fourth argument (FALSE) indicates whether to accept an approximate value.
2.4.1.1 APPROXIMATE VALUE: FALSE
With this parameter, the code must match exactly what is found in the list.
This is the most instinctive behavior, and very often the one we would expect. However,
it is not the one defined by default by Excel. If you omit this argument, you
You will therefore not have the expected behavior!

Page 22 of 28
2.4.1.2 APPROXIMATE VALUE: TRUE
This parameter can be useful when the entered value is a numeric value.
for example, modify your formula to set this parameter to TRUE. And look for the value
4,5. You do not get an error.
The system will scan the array in search of the value closest to the value
sought after. However, this flexibility comes at a cost. The input data must
Absolutely must be arranged in ascending order, under penalty of returning information
Erroneous.

2.4.2 RESEARCH
The HLookup function works exactly like the VLookup function, with the
same parameters and the same limitations. Just swap the roles of the rows and the
columns: Research does its search horizontally instead of doing it
vertically.

2.4.3 LIMITATIONS
These two functions have one real flaw: when you select the source table, the
the column where the search is performed is always the first one. Thus, it is impossible with
SearchV to return results to the left of the search column. Same with
ResearchH, it is impossible to return results located above the line.
sought after.
2.5 MATHEMATICAL FUNCTIONS
The number of mathematical functions available in Excel is staggering. We are
we will limit ourselves to the most useful for chemists (spare us, for example the
hyperbolic trigonometry
2.5.1 ROUNDING FUNCTIONS
We have previously seen how to force the display of results with a certain
number of digits after the decimal point. We also saw that it was only about display, and
that the result of the calculation remained accurate.

Pour faire un vrai arrondi, nous pouvons utliser la fonctonARRONDI, qui prend deux
parameters.
The first is the value that must be rounded. This can be a numerical value,
reference to another cell, or a more or less complicated calculation.
The second parameter is the number of decimal places to keep.

Thus, =ROUND(10/3, 2) gives 3.33.

We can verify that this is a true rounding :=ROUND(10/3, 2) * 3 does not give
10...

Page 23 of 28
To round to the nearest whole number, simply round to zero decimal places: =ROUND(10/3, 0)

It is also possible to round to the nearest ten (by indicating -1 digit after the decimal point), the
hundred (-2 digits...) or more.

The rounding is done to the nearest value, according to commonly accepted rules.
If necessary, it is possible to round down to the strictly lower value (function
=FLOOR(), same parameters) or higher (function=CEILING(), same).

2.5.2 LOGARITHMS AND EXPONENTIALS

Used notably in pH formulas...
Three usable formulas:
=LN(): calculates the natural logarithm (base e), also known as the natural logarithm, of the argument
given, which must be positive.11
=LOG10(): calculates the decimal logarithm of the given argument. This is the one used in
the pH formulas.
=LOG(): if needed, takes two arguments, and calculates the logarithm of the first
argument in the database as a second argument.12
A reminder to avoid any mistakes: logarithms are only defined for numbers
positfs.
2.6 STATISTICAL FUNCTIONS
Excel includes an impressive number of statistical and scientific formulas.
financial.
To find them, the easiest way is to call on the formula assistant.

Among these, we can mention:

CORRELATION COEFFICIENT: it is the R factor that we obtain from linear regressions.
function takes two data series as input.
ECART.MOYEN calculates the average of the deviations.

There are two standard deviations: STDEV.P and STDEV.S13The difference between
the standard deviation hypothesis assumes that the values
Entries make up only a part of the elements of the population. Its value is therefore more
greater than Pearson's standard deviation.

Reminder: if y = ln(x), then x = ey.

11

12
And we recall the formula: logy(x) = ln(x)/ln(y)
13
Note that there is only one T in ECARTYPE...

Page 24 of 28
Specialists will find their happiness in formulas like F.TEST (gives the result
the Fisher test) or others.
In a more classic way, you also know that AVERAGE.MODE is available.

2.7 MATRIX FUNCTIONS

These functions have a very special place in the list of Excel functions: they are
capable of filling multiple cells at once, in a single formula.
As a result, a matrix formula must be entered somewhat differently than a function.
classical: first, you need to select all the boxes concerned by the
formula. Then, it is necessary to write the formula as usual. Finally, it must be validated, not
with the Enter key, but with CTRL + SHIFT + ENTER.
Once this action is performed, the formula will appear surrounded by curly braces, in order to show
What is a matrix formula.
Attention: it is not enough to write brackets to write a matrix function. It is necessary to
absolutely validate by CTRL + SHIFT + ENTER
Here are some examples

2.7.1 CLASSICAL CALCULATIONS

Enter numerical values in cells A1 to A7 and in cells B1 to B7.

Select C1:C7 and enter the formula =A1:A7*B1:B7 then confirm with CTRL+SHIFT+ENTER

Another interesting variant: place yourself in an empty box and type.

=SUM(A1:A7*B1:B7). Confirm with CTRL+SHIFT+ENTER. You can do without
intermediate results of column C!
ATTENTION: even if your formula only touches one cell, internally, it
work on matrices. You MUST validate as an array formula.

2.7.2 ENTER MATRIX CONSTANTS IN YOUR FORMULAS

In a classic formula, you can use numbers instead of references.
cell. In matrix calculation, you can enter matrices. The data is entered between
accolades. They are separated by . when writing a horizontal table and by ;
for vertical arrays. For example, try this :=SUM({1.2.3.4}*{5.6.7.8})

You can obviously mix the notations :=SUM({1.2.3.4}*B1:B4)

Attention for users of software in the English language: the dots become
commas and the points commas !!!

Page 25 of 28
2.7.3 SOME SPECIFIC FUNCTIONS
Some functions are specific to matrices.
For example: TRANSPOSE will transpose a table. Thus, a table of 4 rows by 3 columns.
columns will become a table of 3 rows and 4 columns. Almost to mix some
results from different sources, when they are not presented as we
would like...

Another interesting function is the function LINEST:

This function takes several arguments: the Y column, the X column,
possibly FALSE if one does not want it to calculate the constant (in this case the line
passes through the origin). It returns a two-row, one-column array containing
regarding the slope and the y-intercept.
Other variants exist (consult the help) for performing multivariable regression or
to directly extract the associated standard deviations.
Similarly, if the regression you are interested in is not linear, other models are available.
for example LOGREG (logistic regression).

2.7.4 SOME MIRACLES WITH MATRIX FUNCTIONS

The advantage of these formulas is essentially, when mastered, to save some
intermediate calculations.

For example: this formula calculates the average of the elements of an array, excluding the
null terms:
=AVERAGE(IF(A1:A10<>0, A1:A10))

This counts the number of different cells between columns A and B:

SUM(1*(A1:A10<>B1:B10))

A final example (from Excel 2013 Bible): displaying a calendar through a "simple"
formula:
Select B2:H2, and merge and center
Enter a date in this box
Select B4:H9 and enter this formula:
IF(MONTH(DATE(YEAR(B2),MONTH(B2),1))<>MONTH(DATE(YEAR(B2),MONTH(B2),1)-
(JOURSEM(DATE(YEAR(B2), MONTH(B2), 1))-1)+{0;1;2;3;4;5}*7+{1.2.3.4.5.6.7}-
1);"";DATE(YEAR(B2), MONTH(B2), 1-DAYOFWEEK(DATE(YEAR(B2), MONTH(B2), 1))-
1 + {0;1;2;3;4;5} * 7 + {1.2.3.4.5.6.7} - 1)) (phew !!!)
- Apply a custom format: j

Page 26 of 28
 December-14

29 30 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29

3 ET AFTER?
3.1 OTHER FUNCTIONS: MICROSOFT HELP AND INTERNET
We have not covered the full range of Excel's functions, far from it.
For more information, it is possible, via the function assistant, to access the help of
Microsoft. This help is also available via the F1 key.
Microsoft's help is quite well designed. For each function, you will have a
complete description of the effect, the list of mandatory and optional parameters, along with their
description, and especially, concrete examples that are rather speaking.

The Internet is an almost inexhaustible source of information, provided you know where and
what to look for.
Among the links interesting, Notes:
htp://www.excel-pratwhat.com/fr/index_of_functionstons.phpwhich contains a dictionary of
all the functions of Excel, and also a tool to translate the functions into a
English version of the software, which can be useful.

Finally, the paper literature, although not delivered with the software for many years,
still exists. I keep a copy of the 'Excel Bible' in my office.
for those who need it.
3.2 RELATED FEATURES: PIVOT TABLES

Another method to quickly process a large amount of data is the

generation of pivot tables. They allow to create almost instantly
summary tables, with partial sums, ...
A specific training support is available.
3.3 CREATING YOUR OWN FUNCTIONS: VISUALBASIC AND THE EDITOR
MACROS
We are stepping outside the scope of this training. It is simply interesting to note that it is possible.
in Excel to create your own functions if the existing functions are not sufficient. Visual
Basic is the subject of another training.

Page 27 of 28
Page 28 of 28