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