/
Excel Function Formulas
Save to my account
Sign up
Report Bug
Excel Function Formulas
Excel Function Formulas
Study
Question
What is a cell reference in Microsoft Excel, and why is it preferable to use references instead of direct values in formulas?
Answer
A cell reference identifies a cell or group of cells in a worksheet (e.g., D7 or A1:E5). Using references in formulas is preferable because it allows the formula results to update automatically when the data changes, ensuring results remain accurate without rewriting formulas.
Question
What is a relative cell reference in Excel, and how does it behave when copied across cells?
Answer
A relative reference (like C4 or F7) tells Excel to find a cell relative to the position of the formula's cell. When copied, it adjusts automatically depending on the direction: row numbers change when copying vertically, column letters change when copying horizontally.
Question
Explain what an absolute cell reference is and how it differs from a relative reference.
Answer
An absolute reference (like $D$9) refers to a fixed cell location that does not change when copied to other cells. It uses the $ sign before the column letter and row number to lock both the column and row, unlike relative references which adjust based on the formula's new location.
Question
How do mixed references work in Excel? Give examples illustrating different types of mixed cell references.
Answer
Mixed references combine relative and absolute parts. For example: $A$1 locks both column and row (absolute); A$1 locks only the row; $A1 locks only the column; A1 is fully relative. This lets you fix either column or row while allowing the other part to change when copying formulas.
Question
In what situations would you use absolute references instead of relative references?
Answer
Use absolute references when you want to always refer to the same fixed cell, regardless of where the formula is copied. This is useful for constants or parameters like a fixed tax rate or percentage stored in a single cell.
Question
What is the syntax of the IF (SI) function in Excel, and what are its three arguments?
Answer
The syntax is =SI(test_logique; valeur_si_vrai; valeur_si_faux). The three arguments are: 1) test_logique – the condition to test; 2) valeur_si_vrai – the value/action if the condition is true; 3) valeur_si_faux – the value/action if the condition is false.
Question
List the six comparison operators used in Excel conditional functions and their purposes.
Answer
= (equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), <> (not equal to). They compare cell contents or values in logical tests within formulas.
Question
How do you correctly write a formula in Excel to check if a student’s grade is at least 10 and display 'Passage' if true or 'Double' if false?
Answer
=SI(A2>=10;"Passage";"Double"). Text results must be enclosed in quotation marks.
Question
What are nested IF functions and when are they used?
Answer
Nested IFs occur when the action for one condition includes another logical test (an IF inside an IF). They are used to check multiple conditions sequentially by embedding one IF function within another.
Question
Describe how the ET (AND) and OU (OR) functions work and when you would use them in conditional formulas.
Answer
ET returns TRUE only if all conditions are true; OU returns TRUE if at least one condition is true. They are used to combine multiple logical conditions within IF statements to make decisions based on multiple criteria.
Question
Provide an example formula using ET (AND) to decide if a student passes a class based on a minimum average of 10 and minimum math score of 11.
Answer
=SI(ET(A2>=10;B2>=11);"PASSAGE";"REFUS") — This means the student passes if both conditions are met; otherwise, they fail.
Question
What is the purpose of the SOMME.SI function in Excel and what are its three main arguments?
Answer
SOMME.SI sums values in a specified range that satisfy a given condition. Its arguments are: 1) plage-à-analyser (range to test the condition), 2) condition (the criteria), 3) plage-à-additionner (range containing values to sum).
Question
How do you write the SOMME.SI formula to sum sales amounts for salespersons with individual sales greater than 10,000 EUR?
Answer
=SOMME.SI(B2:B7;">10000";C2:C7) — It sums C2:C7 where corresponding B2:B7 values exceed 10,000.
Question
Explain the NB.SI function and its arguments, and what it is used for in Excel.
Answer
NB.SI counts the number of cells in a range that satisfy a specific condition. Arguments are: 1) plage-à-analyser (range to check), 2) condition (criteria). It is commonly used to count how many entries meet a criterion.