Excel Formulas Revision Sheet
1. SUM Function
Description: Adds up a range of numbers.
=SUM(A1:A10)
2. AVERAGE Function
Description: Calculates the average of a range of numbers.
=AVERAGE(B1:B5)
3. IF Function
Description: Performs a logical test and returns one result if true, and another if false.
=IF(A1>10, "Yes", "No")
If the value in A1 is greater than 10, it displays "Yes"; otherwise, "No".
4. COUNT Function
Description: Counts the number of cells that contain numbers in a range.
=COUNT(A1:A10)
This counts the number of cells containing numbers in the range A1 to A10.
5. COUNTIF Function
Description: Counts the number of cells that meet a specific condition.
=COUNTIF(B1:B10, ">5")
This counts the number of cells in B1 to B10 with values greater than 5.
6. VLOOKUP Function
Description: Looks for a value in the first column of a table and returns a value in the same row from
another column.
=VLOOKUP(A2, A1:B10, 2, FALSE)
Searches for the value in A2 in the first column of the range A1
and returns the corresponding value from the second column (B).
7. CONCATENATE or & Function
Description: Combines multiple text strings into one.
=CONCATENATE(A1, " ", B1)
This combines the text from A1 and B1 with a space in between.
8. MAX Function
Description: Finds the highest value in a range of cells.
=MAX(A1:A10)
This returns the highest value in the range A1 to A10.
9. MIN Function Description: Finds the lowest value in a range of cells
=MIN(A1:A10)
This returns the lowest value in the range A1 to A10.
10. INDEX Function
Description: Returns the value of a cell at the intersection of a specified row and column within a range.
=INDEX(A1:C10, 2, 3)
This returns the value in the 2nd row and 3rd column of the range A1
.
11. MATCH Function
Description: Returns the position of a value within a range.
=MATCH(10, A1:A10, 0)
This returns the position of the first occurrence of the value 10 in the range A1 to A10. The 0 indicates an
exact match.
12. HLOOKUP Function
Description: Similar to VLOOKUP, but searches for a value in the first row of a table and returns a value in
the same column from another row.
=HLOOKUP(A1, A1:F3, 2, FALSE)
Searches for the value in A1 in the first row of the range A1
and returns the corresponding value from the 2nd row.
13. TODAY Function
Description: Returns the current date.
=TODAY()
This returns the current date.
14. NOW Function
Description: Returns the current date and time.
=NOW()
This returns the current date and time.
15. SUMIF Function
Description: Adds the values in a range that meet a specific condition.
=SUMIF(A1:A10, ">10", B1:B10)
This adds the values in B1 to B10 where the corresponding values in A1 to A10 are greater than 10.
16. IFERROR Function
Description: Returns a custom result if a formula evaluates to an error, otherwise returns the result of the
formula.
=IFERROR(A1/B1, "Division error")
This will display "Division error" if the formula results in an error (e.g., if B1 is 0).
Excel Tips:
Relative vs Absolute Reference: Use $ to lock rows or columns in formulas ($A$1 locks the cell, A$1
locks the row, $A1 locks the column).
Nested Formulas: You can combine functions to create more complex formulas, for example:
=IF(SUM(A1:A10)>100, "Large Total", "Small Total").
Error Checking: Use IFERROR to avoid showing errors like #DIV/0! in your results.
Excel history:
-excel is a table
-excel fishier extension is xls/xlsx
-a classer is the ensemble of calculus sheet
-les donnes sont classes dans les cellules
1. une formule ( sum, average…)
2. une constante numérique ( 1,2,3…,date)
3. une constante textuelle (« bounjour », »123 »)
- L'utilisateur encode =3*4 dans la cellule A1. Quelle sera la valeur stockée A1 ? 12
=COUNTA(A1): Counts the number of non-empty cells in a range (includes text, numbers, etc.).
=COUNTIF(A1, "criteria"): Counts the cells that meet a specific criterion.
=COUNTIFS(A1, "criteria1", B1, "criteria2"): Counts the cells that meet multiple criteria.
=SUMIF(A1, "criteria", B1): Sums values in a range if they meet a specific criterion.
=SUMIFS(A1, "criteria1", B1, "criteria2"): Sums values in a range if they meet multiple criteria.
2. Logical Functions
=IF(condition, value_if_true, value_if_false): Tests a condition and returns one value if TRUE
and another if FALSE.
=IFERROR(value, value_if_error): Returns a result or a value if the expression generates an
error.
=AND(condition1, condition2, ...): Returns TRUE if all conditions are true.
=OR(condition1, condition2, ...): Returns TRUE if any of the conditions are true.
=NOT(condition): Returns the opposite of the condition.
3.date
=TODAY(): Returns the current date.
=NOW(): Returns the current date and time.
=DATE(year, month, day): Returns a date based on the year, month, and day arguments.
=YEAR(date): Extracts the year from a date.
=MONTH(date): Extracts the month from a date.
=DAY(date): Extracts the day from a date.
=DAYS(end_date, start_date): Returns the number of days between two dates.
=NETWORKDAYS(start_date, end_date, [holidays]): Returns the number of working days
between two dates, excluding weekends and optionally, holidays.
=WORKDAY(start_date, days, [holidays]): Returns the date that is a specified number of
working days before or after a given date.
=WEEKDAY(date, [return_type]): Returns the day of the week for a date.
Références Absolues vs. Relatives
Référence relative (A1) : Lorsque vous copiez une formule, Excel ajuste la référence en fonction
de la position de la cellule.
Référence absolue ($A$1) : Vous "verrouillez" la référence sur une cellule spécifique, même si
vous copiez la formule ailleurs.
Référence mixte (A$1 ou $A1) : Vous verrouillez soit la ligne, soit la colonne, mais pas les deux
