Formulas are the workhorses of complex calculations and data manipulation in Excel. Here's a breakdown of how to use them for powerful tasks:

1. Building the Formula:

- Start with the equal sign (=): Every formula in Excel begins with an equal sign, which signals to Excel that you're entering a calculation.
- Functions vs. Operators: You can use built-in functions (e.g., SUM, AVERAGE, VLOOKUP) or mathematical operators (+, -, *, /) within your formula. Functions provide pre-defined calculations, while operators perform basic arithmetic.
- Cell References: Include cell references (e.g., A1, B2) to tell Excel which cells to use in the calculation. You can reference single cells or ranges of cells (A1:A10).
- Nesting Functions: Formulas can become more complex by nesting functions within each other. For example, you could use the AVERAGE function inside a SUM function.

2. Examples of Complex Calculations:

- Conditional Calculations with IF Function: The IF function allows you to perform calculations based on specific conditions. For instance, `=IF(A1>100, A1*0.1, A1*0.05)` checks if the value in A1 is greater than 100. If true, it multiplies by 0.1; otherwise, it multiplies by 0.05.
- Date and Time Calculations: Functions like DATE, YEAR, MONTH, DAY, and TIME can be used to manipulate dates and times for calculations. Imagine calculating the difference between two dates or finding the age based on a birthdate.
- Text Manipulation with Functions: Functions like LEFT, RIGHT, MID, and CONCATENATE allow you to extract, combine, or modify text data. You can use these to clean up messy data or create new text strings based on existing information.
- Statistical Analysis: Functions like COUNTIF, SUMIF, AVERAGEIFS, and VAR.P can be used for advanced statistical analysis. You can count specific items based on criteria, calculate conditional averages, or find the population variance.

3. Data Manipulation Techniques:

- Sorting and Filtering: Combine formulas with sorting and filtering to focus on specific data subsets for further calculations. For example, you can filter for products with a specific price range and then calculate the total sales for those items.
- Lookup Functions: Functions like VLOOKUP and INDEX MATCH allow you to efficiently retrieve data from another location in your spreadsheet based on a specific value. This is helpful for combining data from multiple sheets or referencing large datasets.
- Data Tables: Create data tables to perform "what-if" analysis. By changing a single input value in a formula, you can automatically see how the entire formula output changes across various scenarios.

Tips:

- Function Wizard: Utilize the Function Wizard (fx button) to explore available functions and get guidance on arguments and syntax.
- Error Handling: Use functions like ISERROR or IFERROR to handle potential errors in your formulas, ensuring your calculations remain robust.
- Relative vs. Absolute References: Understand the difference between relative and absolute cell references. Relative references adjust automatically when copied, while absolute references lock onto specific cells.

By mastering formulas and combining them with other techniques, you can unlock the true power of Excel for complex data analysis and manipulation.