EXCEL

Question 1

To create a chart table

Answer 1

On the right of charts -> Chart and table Pivot Chart)

Question 2

Sort a column as I want ?

Answer 2

Select data –> Data –> Sort –> Choose which column –> Then the way I’ll sort it

Question 3

Insert subtotals

Answer 3

Select the table then Data / Subtotals then logical

Question 4

Equiv = ?

Answer 4

MATCH (Don’t forget the last argument which number of cell shifting (0))

Question 5

Function INDEX gives the cell of a table

Answer 5

INDEX (Table ; Row ; Column)

Question 6

Function SIERREUR ?

Answer 6

Function IFERROR

Question 7

Fonction pour choisir comment arrondir et combien de chiffres conserver après la virgule

Answer 7

ROUNDUP or ROUNDDOWN (Cell in which figure; number of figure I want after the coma)

Question 8

Function sum-product = ?

Answer 8

The function I already know = SUMPRODUCT

Question 9

Generate a Random number between [0;1]

Answer 9

RAND()

Question 10

Generate a Random number between [0;100]

Answer 10

RAND() * 100

Question 11

Generate a Random number between [a;b]

Answer 11

RANDBETWEEN(a;b)

Question 12

Valeur nette présente pour des dates annuelles

Answer 12

NPV

Question 13

Valeur nette présente mais pour différents moments dans l’année où je peux alors inclure des dates

Answer 13

XNPV

Question 14

To find the internal rate of return which is the rate at which the positive amounts discounted at this rate equal the negative amount (the sum invested)

Answer 14

IRR

Question 15

How to compute the mensualities of a loan at a rate r

Answer 15

PMT (Rate ; Number of payments ; Present value or amount) be careful the rate is montly then if 4% I put 4%/12

Question 16

I want to create a function which takes the value 1 with likelihood 10%, 2 with 30% etc …

Answer 16

I take If() in which I put Rand() and the good intervals et j’imbrique

Question 17

Function to count the number of cells filled with numbers in a range

Answer 17

Count()

Question 18

Function to count the number of blank cells in a round

Answer 18

COUNTBLANK()

Question 19

Count cells without blank

Answer 19

COUNTA

Question 20

Statistical question : Maximum in cells

Answer 20

MAX()

Question 21

Statistical question : Minimum in cells

Answer 21

MIN()

Question 22

Statistical question : k th largest in cells

Answer 22

LARGE (Array; k)

Question 23

Statistical question : k th smallest

Answer 23

SMALL (Array ; k)

Question 24

Statistical question : Average

Answer 24

Average (Array)

Question 25

Obtenir classement valeur parmi Array

Answer 25

RANK (the value; the array; 0 is décroissant, 1 if croissant)

Question 26

Variance of array

Answer 26

VAR(Array)

Question 27

Ecart type of array

Answer 27

STDEV(Array)

Question 28

NB.SI.ENS = ?

Answer 28

COUNTIF

Question 29

SOMME.SI.ENS = ?

Answer 29

SUMIF()

Question 30

To trace cells the cells to which the result of a cell results

Answer 30

Select the active cell then Formula / Trace precedents or dependents (don’t actually know the ≠)

Question 31

Recherchev = ?

Answer 31

VLOOKUP

Question 32

To add a comment next to a cell to explain sth ..

Answer 32

Active cell / Review / Add Coment

Question 33

Add Shapes ?

Answer 33

Insert / Shapes / Circles, lines, squares

Question 34

To make some data validation I go to data validations … :p

Answer 34

Data validation

Question 35

If I want to remove negative values and put them in a 0 I put

Answer 35

=If of Max then 0 etc..

Question 36

If I want to have a message set if a value overpasses a definite threshold I put a

Answer 36

If (B4>3; It went beyong etc…)

Question 37

Un peu spécial mais je peux créer des scenarios et en gros je mets les noms des scénarios et les valeurs en question ensuite qui correspondent à ces scénarios (un nombre de ventes par exemple)

Answer 37

Je vais dans data validation je prends “list” puis dans les cases dynamiques qui servent de base aux calculs je fais vlookup et utilise la cellule dynamique comme premier argument of course

Question 38

To insert a button

Answer 38

Developer / Spinner then click right format control cell link

Question 39

To go faster in cumputing a few cells

Answer 39

Sum (Jan : May!B5) in which Jan, Feb, Marc are different sheets)

Question 40

Former un tableau rapidos avec couleurs et sorting tools

Answer 40

Select cells then CMD + T