Notes 

Somme (Eng : SUM):

=C3 + D3 + E3

=SOMME( C3; D3; E3 )

=SOMME( C3:E3 )

How many variables (parameters)

1 or more = at least 1

Ex: 1 parameter (a range)

=MIN( C3:E3 )

=MAX( C3:E3 )

=MOYENNE( C3:E3 )

Drag or double click on the black cross

1582,645 = FR

1582.645 = EN

Min , Max, Moyenne

function consists of at least 1 part

=MAX(A1:A5)

=MAX(A1;2;C3:D5)

F4 (or + FN) ==> dollar to fix error = to fixe value (figer la valeur (la case))

If we continue to hit F4, we cycle through the 4 possibilities:

1. F4 pressed once: D10 to $D$10 (lock column to D, and lock row to 10)

2. F4 pressed twice: $D$10 to D$10 (column D free to move, lock to row to 10)

3. F4 pressed thrice: D$10 to $D10 (lock column to D, row 10 free to move)

4. F4 pressed four times: $D10 to D10 (both column D and row 10 free to move)
Note: Relative Cell References will always keep the relative position.

Absolute Cell Reference will keep the locked down, absolute, cell position.

Arrondi (Fr) = Round (En)

2 parameters

=ARRONDI( $D$10; C11 )

1582,645
2 decimal places give 1582,65
1 decimal place gives 1582,6
0 decimal places give 1583
-1 decimal places give 1580
-2 decimal places give 1600

( ) math

( ) in a function start of the parameter ==> mandatory

in a function: if the parameter is in [ ] that means it's optional ==> if it's not it's compulsory

NBCAR() of character : 1 parameter

=LEN( ) ==> En

=NBCAR( ) ==> fr

For example:
=NBCAR(A1)
=NBCAR("String to Process")= 17

This returns the number of characters in the specified text string, defined in the first parameter.

If a cell reference is passed, and the cell value is blank, the value 0 is returned.

=SI( K3>8; "Great"; SI( K3>6,15; "Good"; SI( K3<2; "FAILED !!!"; "Bad" ) ) )

=SI( K3>=L11; K11; SI( K3>=L12; K12; SI( K3>=L13; K13; K14 ) ) ) ==> =SI( K3>=$L$11; $K$11;
SI( K3>=$L$12; $K$12; SI( K3>=$L$13; $K$13; $K$14 ) ) )
=SI( G27=VRAI; D30; SI( G28=VRAI; D31; D32 ) )

=IF( G27=TRUE, D30, IF( G28=TRUE, D31, D32 ) )

--------------------------------------

Example of question in mid-term1

--------------------------

1- OU // NBCAR

If weighted Avergae >7

OR LENGTH of Name < 5

Show "Oui"

ELSE Show "Non"

Function is LEN (En) = NBCAR (Fr)

Function OR ==> OU(K3>7;O3<5) in Fr

AND (ET) and OR (OU) Functions

 AND Returns TRUE If all of the conditions are TRUE

OR Returns TRUE If at least one of the conditions are TRUE

==> =SI(OU(K3>7;O3<5);"OUI";"NON")

==> =SI(OU(K3>7;NBCAR(B3)<5);"OUI";"NON")

2- AND

IF sum of Min value and Max value <= 13 AND Weighted Avergae <=7 AND Length of Name >=5

THEN Show "X"

ELSE Show "Y"

==> SI(ET(G3+H3<=13; K3<=7; NBCAR(B3)>=5 ); "X"; "Y")

3- OR inside AND
IF EXCEL Score > French Score

AND (Weighted Average >=8 OR Min <=6 )

THEN Show "yay"

ELSE Show "nan"

==> =SI( ET(E3>C3; OU(K3>=8; G3<=6)); "yay"; "nan")

4- Gauche (text; [optional]) / Droite (LEFT/RIGHT) / STXT (MID)

no

==> =GAUCHE("Car Seat"; 3) ==> Car

5-

IF Weighted Average >7 OR Name BEGINS WITH "B"

THEN Show "LOL"

==> (OU(K3>7; GAUCHE(B3; 1) = "B"); "LOL"; "FMR")

ELSE Show "FML"

6- STXT (Fr) ==> Mid text

=STXT(C25; 5; 3)

7- Concat = &

Space in Excel ;"";

8- Majiscule / miniscule / nompropre(majuscule en 1ere lettre du mot)

=MAJUSCULE(K22)

=MINUSCULE(K22)

=NOMPROPRE(K22)

9- sierreur + rechercheV avec paramètre 4

Matrice or table need to be in alphabet ordre

=SIERREUR(RECHERCHEV(H4; C7:I17; 6;FAUX ); "Client not found")

Approximative : vrai ou faux ==> si on met faux il va nous mettre N/A ou message d'erreur qu'on va
lui demander. Si on met Vrai il va chercher la case et si il ne trouve pas il nous donne celle d'avant

The 4th parameter of the VLOOKUP (RECHERCHEV) Function is called the Range

Total price

=siereur(rechercheV(H4;C7:E12;IF(H5<>"Y";2;3))*H4;0)

--------------------------------------

HLOOKUP 1 = RechercheH ==> chercher une valeur horizontal

215, Franklin Court - ( Liam KEELER )

=SIERREUR( RECHERCHEH(G4; D7:M13; 4; FAUX) & ", " & RECHERCHEH(G4; D7:M13; 5; FAUX) & " -
" & "(" & " " &RECHERCHEH(G4; D7:M13; 2; FAUX) & " " & MAJUSCULE(RECHERCHEH(G4; D7:M13; 3;
FAUX)) & " )"; "Client Not Found")

-----------------------------------

=20 & STXT(D7;2;2)

= "20" & STXT(D7;2;2)

=2000 + STXT(D7;2;2)

-------------------------------------

Use 911 if you cant find the number of charac or don't find

SEARCH (CHERCHE) Function

Ex: =CHERCHE("n","imprimante")  =8 car « n » est le 8ème caractère

-------------------------------

Droit ==> =DROITE(D7; NBCAR(D7) - CHERCHE("-"; D7;1))

INDEX EQUIV

EQUIV (MATCH)

 recherche un élément spécifique dans une colonne ATT  select only the column or the line

, puis renvoie la position relative de l’élément dans la plage.

Look for a value in the left of the table (we can't use VLOOKUP=rechercheV because we can't look for
a value before

EQUIV(H6; E8:E18; 0) ==> use 0 to ask for the exact position

La fonction INDEX

renvoie une valeur ou une référence à une valeur provenant d’un tableau ou d’une plage.

ATT  select only the column or the line

==> =INDEX(C8:C18; EQUIV(H6; E8:E18; 0))

We can use INDEX also for a matrice  give

VLOOKUP INDEX MATCH

1. Size of Cell Range to perform Requires whole table array Only requires lookup column and
Function return column

2. Choice of Lookup Column Must be first column in the table array Can be any column

3. Direction from Lookup Must be the Lookup Column or any Can be any column
Column column to the right

4. Result from Inserting Columns Breaks return cells If Relative Cell References are used,
won't break return cells
--------------------------------------------------

Propose data in a case: select from a list

Clic on data: données

Validation des données

Autoriser data: liste

=INDEX(C8:I18;EQUIV( I20; E8:E18; 0 ); EQUIV( I21; C8:I8; 0 ))

-------------------------------------------------------------

generate random number between 2 parameters

=ALEA.ENTRE.BORNES

-----------------------------------

C* = give me everything starting with C

?C = must start with something (no empty) and after find "C"

- : hyphen

------------------------------------------------------

Named range ==> select a table or a group of celles and name it as you want in the left top of the
excel

Format conditionnel ==> accueil ==> Mise en forme conditionnelle

- to compare a column to another column (grades for ex) ==> select all celles of one column (last
column for ex) ==> select the first celle (value) in the 2nd column ==> the value need to be with 1£
before the celle: $A1 (2 times F4) ==> first value in the column

Afficher les valeur en barres ==> mise en forme conditionnelle ==> barres de données ==> autres
règles ==> select afficher la barre uniquement et changer la couleur

Pour mettre des couleur en comparant des serie ==> 2 méthodes

==> mise en forme cond. ==> Règle de mise en surbrillance ==> autres règles ==> utiliser une formule
pour determiner pour quelles cellules le format sera appliqué ==>mettre la formule cellule $D3 (F4 &
F4) and put the logical equation (> or < or = ) ==> clic on format ==> remplissage et choisir une
couleur
----------------------------------------------------------

delete deplicate ==> données ==> two barres bleu (supprimer les doublons)

Ordre data ==> données ==> A to Z

--------------------------------------------------------------------------

Jeux d'icone (1 & 2 & 3)

=SI( Z5>Z6; 3; SI( Z5=Z6; 2; 1 ) )

=INDEX( FirstName; ALEA.ENTRE.BORNES(1;10) ) & " " &

MAJUSCULE( INDEX( Surname; ALEA.ENTRE.BORNES(1;10) ) )

1 Add a drop down list containing valid Work Order ID values in cell N3

2 If Work Order ID ( N3 ) is found, display the Discount Band ( N4 ) and Date ( P4 ) using a
LOOKUP function

3 If Work Order ID ( N3 ) is found, display the corresponding Segment ( N5 ) and Sales Price ( P5
), using INDEX MATCH

4 If Work Order ID ( N3 ) value is not found, display '=:=' for Discount Band, Date and Segment (
N4, P4, and N5 ), and 0 for Sales Price ( P5 )

5 Add a drop down list in cell N7, containing the 5 valid Country values from column E

6 In cell N8, display the number of Work Orders matching the Country ( N7 )

7 In cell N9, display the total of all Units Sold of the work orders matching the Country ( N7 )

8 In cell N10, display the number of Work Orders where Sale Price < 100€ and USA or Units
Sold > 999 and VTT

9 In cell N11, display the number of Low or Medium or High Work Orders

10 In cell N12, display number of Work Orders where Date is between 15/09/2022 and
15/10/2022 NB. do NOT include the dates