ഒരു Accountant അറിഞ്ഞിരിക്കേണ്ട Top 12 Excel Functions | Excel for Accounting

Main ideas / concepts covered

The subtitles explain how to use Excel functions commonly useful for accounting, focusing primarily on:

1. SUBTOTAL function

   • Used with report-style data (e.g., sales reports).
   • Includes options that control what to aggregate and how nested results are handled.

2. AGGREGATE function

   • A more flexible alternative to SUM-like aggregation.
   • Supports options such as:
     • What to calculate (sum, average, etc.)
     • Whether to ignore errors or other values
     • Handling nested/hidden rows depending on the chosen options

3. IF function (conditional logic)

   • Used to determine outcomes based on one or multiple conditions.
   • Demonstrates:
     • Basic threshold comparisons
     • Combining conditions using AND
     • (Also referenced) use of OR/either-style logic for alternative conditions
   • Illustrated with commission/tier logic based on sales thresholds.

4. Commission/tier calculation example

   • The video uses conditional logic to compute commission as a percentage of sales depending on sales amounts meeting certain criteria.

5. How Excel automatically updates copied formulas

   • Shows that when copying a formula, Excel adjusts cell references appropriately (i.e., “copy/selective copy” behavior).
   • Example concept: using a computed value per row/item, with Excel carrying the logic correctly across rows.

---

Methodology / instructions presented (detailed)

A) Using SUBTOTAL

The subtitles reference a SUBTOTAL function format with:

• Function number: selecting which operation to perform (e.g., sum, average, etc.)
• First argument / range: selecting the range of cells to aggregate
• Mentions options such as:
  • Ignore nested results
  • Notes like ignore hidden / completely selected (behavior may depend on visibility and option settings)

Implied structure:

• SUBTOTAL(function_num, range, ...) (with options discussed)

---

B) Using AGGREGATE

The subtitles describe the AGGREGATE function arguments conceptually:

• Function number: what aggregate operation to use (e.g., sum)
• Options for behavior such as:
  • Whether to ignore errors
  • Whether to ignore values
  • Whether to respect or ignore hidden rows

Implied structure:

• AGGREGATE(function_num, options, range, ...)

---

C) Building conditional commission logic with IF

Uses the pattern:

• IF(logical_test, value_if_true, value_if_false)

The subtitles include:

• Threshold checks like “cell value is more than 1500”
• Examples of nested condition logic:
  • If one threshold fails, check the next condition/tier
• A commission calculation pattern:
  • If sales meet certain conditions → apply a commission percentage to total sales
  • Otherwise → mark as not eligible or return a different outcome

Threshold / tier logic described

References comparisons such as:

• value > 500
• value < 500
• additional comparisons like value > 250

Mentions:

• AND-style multi-condition requirements (both conditions must be true)
• An “either/or” style (one condition can be sufficient), though exact function naming is slightly garbled in the subtitles.

Commission calculation pattern

Commission is computed conceptually as:

• (commission_percentage) * (relevant sales amount / total sales)

The video mentions:

• Two percentage rates (implying different tiers)
• Conditions tied to sales thresholds per product

Conceptual final line:

• total_sales_commission = IF(eligibility_condition, percentage * total_sales, "not eligible")

A message like “not eligible” / “eligible” appears as the IF output text.

---

D) Using logical operators with cell comparisons

The subtitles describe comparisons and logic combinations such as:

• Equal to
• Greater than
• Less than
• AND combination, with example intent like:
  • Require both:
    • total_sales > 50,000 (or similar)
    • and another condition also meeting a similar threshold for any product

Also references a second condition tier where eligibility changes.

---

E) Copying formulas so Excel auto-adjusts references

The subtitles show that after calculating a formula (including commission logic), when copied down:

• Excel automatically updates cell references (e.g., moving from one row’s values to the next row’s values)

A numeric example is referenced conceptually (multiplying a percentage by a value to yield a commission amount).

---

Speakers / sources featured

• No individual speaker is clearly identified in the subtitles.
• Source context: The video is titled “ഒരു Accountant അറിഞ്ഞിരിക്കേണ്ട Top 12 Excel Functions | Excel for Accounting” (language: Malayalam/possibly auto-transcribed).
• No specific named instructor/host appears in the provided subtitle text.

Therefore:

• Speakers found: None explicitly named.