Quản lý tài chính với hàm IF trong Excel

• After this lesson, you will be able to use the IF conditional function to solve practical financial management problems.

Using the IF Function

• Previous lessons covered the COUNTIF function (counting based on criteria) and the SUMIF function (summing based on criteria)
• Use these to summarize spending data.
• Assess whether spending is balanced and reasonable based on summarized data.
• Apply financial management rules, such as the 50-30-20 rule.

The 50-30-20 Rule

• Allocate 50% of income to essential needs (food, housing, health).
• Allocate 30% to personal wants (entertainment, fashion).
• Allocate 20% to savings.
• The 50-30-20 ratio can be adjusted based on individual circumstances.
• This lesson guides you in using the IF conditional function to manage spending.
• This helps maintain control over finances.

Activity: Summarizing Expenses According to the 50-30-20 Rule

• Expenses are categorized into three types: A, B, and C.
  • A represents Essential Needs.
  • B represents Personal Wants.
  • C represents Savings.
• The provided expense table includes a "Category" column.
• To evaluate spending based on the 50-30-20 rule, create a data table.
• This table calculates the total amount for each expense category.
• The 'Amount' column totals expenses for each category (column M).
• The 'Percentage' column calculates the percentage of each category compared to the total expenses (cell H11) (column N).
• Determine the formulas needed for the cells in columns M and N of the data table.
• Develop a rule using the category in column to provide feedback on spending.
• This is based on the 50-30-20 financial management rule.

Applying the IF Condition

• Based on the 50-30-20 financial management rule, evaluate spending for each category.
• For 'Essential Needs', the feedback is:
  • "Too Much" if the percentage exceeds 50%.
  • "Less" otherwise.
• To automatically add feedback, use the IF function in cells O3 to O5.
• The general formula for the IF function is: =IF(logical_test, [value_if_true], [value_if_false]).
• logical_test is the condition being checked.
• value_if_true is the result if the condition is true.
• value_if_false is the result if the condition is false.
• To determine if the 'Essential Needs' spending is efficient, the spending percentage is in cell N3.
• The feedback for this category is in cell O3.
• The formula for cell O3 is: =IF(N3>50%, "Too Much", "Less").

Conditional Checks

• Realistically, more detailed feedback may be required.
• For 'Essential Needs', feedback tiers consist of the following conditions:
  • "Far Too Much" if the percentage exceeds 80%.
  • "Too Much" if the percentage is greater than 50%.
  • "Less" otherwise.
• In situations requiring multiple levels of evaluation, nested IF functions can be used.
• To implement the multiple tiers of feedback, the formula in cell O4 is: =IF(N3>80%, "Far Too Much", IF(N3>50%, "Too Much", "Less")).

The IF Function Summary

• The IF function checks a condition.
• If the condition is true, it returns one value; otherwise, it returns another value.
• The formula is: =IF(logical_test, [value_if_true], [value_if_false]).
  • logical_test: The condition to check.
  • value_if_true: The value returned if the condition is true.
  • value_if_false: The value returned if the condition is false.
• Determine the formulas for cells O4 and O5 to evaluate 'Personal Wants' and 'Savings.'
• This should be based on the 50-30-20 rule.