Exam Preparation Notes

IF Function

• The IF function tests a condition and displays one value if the condition is TRUE and another if it's FALSE. It is a fundamental tool for decision-making in spreadsheet software.

Basic Structure

• Syntax: IF(logical_test, value_if_true, value_if_false)

  • logical_test: The condition to evaluate. This can be a comparison between two values, a check of whether a cell is empty or not, or any other expression that can be evaluated as TRUE or FALSE.

  • valueiftrue: The value displayed if the condition is true. This could be text, a number, a formula, or even another IF function (nested IF).

  • valueiffalse: The value displayed if the condition is false. Similar to value_if_true, this can also be text, a number, a formula, or another nested IF function.

Example 1: Text Comparison

• Check if the cell F5 contains \"Isabel\".

  • If TRUE, display \"It's me\".

  • If FALSE, display \"It's not me\".

  • Formula: =IF(F5=\"Isabel\", \"It's me\", \"It's not me\")

  • Text must be in double quotes to be recognized as a string.

  • Excel is not case-sensitive in these comparisons, meaning \"isabel\" would also match.

Example 2: Number Comparison

• Check if the value in cell F9 is greater than 50.

  • If TRUE, display \"Large number\".

  • If FALSE, display \"Small number\".

  • Formula: =IF(F9>50, \"Large number\", \"Small number\")

  • Greater than or equal to 50: IF(F9>=50, \"Large number\", \"Small number\")

  • Smaller than or equal to 50: IF(F9<=50, \"Small number\", \"Large number\")

  • Important to pay attention to \"greater or equal to\" in exams and real-world applications to ensure correct results.

Using Formulas in IF Function

• Example: Delivery date calculation based on the state.

  • If the state is Alaska, add 5 days to the current date.

  • Otherwise, add 2 days to the current date.

  • Use the TODAY() function to get the current date.

  • Formula: =IF(F6=\"Alaska\", TODAY()+5, TODAY()+2)

  • TODAY() function returns the current date and is updated automatically.

Example 3: Meeting Expected Sales

• Check if total sales meet the minimum expected sales of $13,000.

  • If TRUE, display \"Yes, meeting expected\".

  • If FALSE, display \"No, it's not meeting our expected amount\".

  • Formula: =IF(J10>=H24, \"Yes, meeting expected\", \"No, it's not meeting our expected amount\")

  • Always refer to the cell containing the value (e.g., $13,000) instead of typing the number directly into the formula to allow for easy updates.

Absolute and Mixed References

• When using the fill handle, references may change, leading to incorrect results. This is especially important when copying formulas across multiple cells.

  • Use absolute references ($) to prevent rows and columns from changing: $H$24

  • Use mixed references to fix either the row or the column, depending on the needs.

  • Press F4 to toggle between reference types quickly.

PMT Function (Payment Function)

• Used to calculate loan payments or savings deposits. This is crucial for financial planning and analysis.

Basic Structure

• Syntax: PMT(rate, nper, pv, [fv], [type])

  • Rate: Interest rate per period. Ensure this is consistent with the payment frequency (e.g., monthly rate for monthly payments).

  • Nper: Total number of payments or periods.

  • Pv: Present value (loan amount or initial investment).

  • Fv: Future value (for savings goals; if omitted, it defaults to 0).

Loan Payment Example

• Calculate monthly payments for a loan of $34,000.

  • Annual rate: 5.66%.

  • Total payments: 48.

  • Payments per year: 12.

  • Formula: =PMT(E11/E13, E12, -E10)

  • Divide the annual rate by the number of payments per year to get the monthly rate. This is a common mistake, so be careful.

  • Refer to the cell containing the number of payments per year (e.g., 12) instead of typing it directly for flexibility.

  • The loan amount can be entered as a negative value to display the payment as a positive number.

Savings Deposit Example

• Calculate monthly deposits to save $10,000 in 24 months.

  • Annual rate: 3%.

  • Current savings: $0.

  • Formula: =PMT(E20/12,24,0,-10000)

Car Loan Example with Down Payment

• Car value: $19,000.

  • Down payment: $2,800.

  • Loan term: 2 years (24 months).

  • Annual interest rate: cell E26 (e.g 2%).

  • Calculate the loan amount after the down payment: Car Value - Down Payment.

  • Formula: =PMT(E26/E28,E27*12,-(E24-E25))

Important Reminders

• Pay attention to whether the interest rate is annual or monthly to avoid calculation errors.

  • Always refer to cells instead of typing in numbers directly, unless specifically instructed. This makes your spreadsheet dynamic and easy to update.

  • Use cell references for the number of payments per year to ensure accuracy.

  • Be mindful of positive and negative signs for