LO 2 Flashcards
What does every Excel formula begin with?
An equals sign (=).
What is BODMAS?
BODMAS stands for Brackets, Order (Power), Division, Multiplication, Addition, and Subtraction, dictating the order of operations in Excel.
How does Excel prioritize operations in a formula?
Excel follows the BODMAS rule, executing operations in this order: Brackets, Order, Division, Multiplication, Addition, and Subtraction.
What does the error value #DIV/0! indicate in Excel?
It appears when you are dividing by 0 or if the cell is empty.
What is a relative reference in Excel?
A relative reference changes when a formula is copied to another cell (e.g., A1 becomes A2 when copied down).
What is an absolute reference in Excel?
An absolute reference is fixed and does not change when a formula is copied (e.g., $A$1).
How do you make a cell reference absolute?
By adding dollar signs before the column letter and row number (e.g., $A$1).
What is a mixed cell reference in Excel?
A mixed reference locks either the row or the column (e.g., $A1 locks the column, A$1 locks the row).
How can you use the Insert Function button in Excel?
By clicking the Insert Function button (fx) in the Function Library group of the Formulas tab to search and insert functions.
What is the purpose of naming cells and ranges in Excel?
Named ranges can be used in formulas for easier reference and to make formulas more understandable.
How do you create a named range using the Name Box?
Select the range, then type the desired name into the Name Box and press Enter.
What is the syntax for the LEFT function in Excel?
LEFT(text, num_chars) returns the specified number of characters from the start of a text string.
What is the syntax for the MID function in Excel?
MID(text, start_num, num_chars) returns a specific number of characters from a text string, starting at the specified position.
How do you isolate the first two characters of the text in cell A1 using the LEFT function?
=LEFT(A1, 2).
What is the syntax for the RIGHT function in Excel?
RIGHT(text, num_chars) returns the specified number of characters from the end of a text string.
What formula can you use to calculate VAT for a product priced in cell B3 with a VAT rate in cell B12?
=B3*B12. Use absolute referencing for B12 if copying the formula (e.g., =B3*$B$12).
How can you calculate the monthly profit/loss for Forth Valley Fitness in a spreadsheet?
Use the formula Total Income - Total Expenses for each month.
What function calculates the total sales for a range named “Sales”?
=SUM(Sales).
How do you use the IF function to check if a value in cell A1 is greater than 100 and return “Yes” or “No”?
=IF(A1 > 100, "Yes", "No").
How can you calculate the profit/loss percentage for a specific month?
=Monthly Profit / Total Profit formatted as a percentage.
What is the formula to calculate the price plus VAT if the VAT amount is in cell D3?
=Price + D3.
How can you use absolute references to copy the VAT calculation correctly in a list of products?
Use the formula =B3*$B$12 to ensure the VAT rate in cell B12 does not change when copied.
What does the error value #NAME? mean in Excel?
It appears when a formula contains text that Excel does not recognize, such as a misspelled function name or missing quotation marks.
What does the #VALUE! error indicate?
It indicates that an incorrect type of argument or operator has been used in a function.
How can you fix the ##### error in a cell?
Widen the column to fit the data.
