Notes on Formulas and Functions in Microsoft Excel

Introduction

• Instructor: Kevin
• Focus: Formulas and functions in Excel
• Objective: To prepare for job interviews or tests on Excel
• A workbook is available for following along
• Timestamps provided to jump through the video

Formula Fundamentals

1. Basic Calculation

   • Example: Calculate total revenue from different cookies at Kevin Cookie Company.
   • Highlight cells to see sum directly in status bar.
   • Right-click status bar for other metrics: average, count, min, max.

2. Entering Formulas

   • Start with an equal sign (=).
   • Example: Calculating 10 + 8 + 5.
   • Edit formulas by clicking the cell or double-clicking.
   • Hard-Coding Issues: If you enter specific numbers (hard-coding), they won't update if values change.
   • Use cell references (e.g., B2, C2) instead of hard-coded values for dynamic calculations.

3. Using the Fill Handle

   • Click the fill handle to copy a formula down or across cells, updating references automatically.

Basic Operations

1. Addition and Subtraction
   • Example: Use = to enter formulas for profits: Revenue - Cost.
   • Use fill handle to apply formulas across rows/columns.
2. Multiplication for Tax Calculation
   • Tax calculation example: multiply profit by tax rate using * (asterisk).
3. Absolute References
   • Lock cell references using $ (absolute reference) for fixed values during formula copy.
   • Use F4 shortcut to toggle between absolute and relative references.

Functions vs. Formulas

• Functions: Built-in commands that can take arguments and return results.
  • Example of functions: SUM, AVERAGE, COUNT, MAX, MIN, IF

Inserting Functions

1. SUM Function

   • INSERTING: Click on formulas or use Shift + F3.
   • Example: Sum a range by highlighting cells.

2. Auto Sum Feature

   • Quickly sums the nearest cells, also accessible via Home Tab.

3. Average and Other Functions

   • Utilize functions like AVERAGE, MIN, MAX through similar insertion methods.

Conditional Functions

1. SUMIF
   • Sums values based on criteria
   • Example: Sum cookies sold in the United States.
2. Multiple Criteria - SUMIFS
   • Similar to SUMIF but allows for multiple conditions.

Text Functions

• Functions for manipulating text:
  1. PROPER(), TRIM(), CONCAT(), TEXTJOIN() to join text.
  2. LEFT(), RIGHT(), MID(), LEN() for extracting text segments.
  3. FIND() and SEARCH() to locate characters.

Logical Functions

1. IF Statement
   • Basic structure: IF(condition, true_value, false_value).
   • Example comparing cookie sales.
2. Using AND and OR in IF
   • Combine multiple conditions within IF statements.
3. Nested IF Functions
   • Handle multiple conditions without making the formula too complex.

Lookup Functions

1. VLOOKUP
   • Used to search for a value in a table and return associated data.
2. XLOOKUP
   • A newer function with more flexibility than VLOOKUP.
   • Allows return of multiple columns easily.

Date and Time Functions

• Easy to use functions to get current date and time: TODAY(), NOW().

Bonus Tips

• Use Ctrl + Tilde (~) to toggle between displaying formulas and values.
• Find and select formulas for visual representation.
• Trace precedents and dependents to troubleshoot formulas.

Conclusion

• The goal is to become proficient at using formulas and functions in Excel.
• Encouragement to subscribe for more tutorials.