📊 Curso Google Sheets ✅ Referencias absolutas y relativas

Google Sheets: Understanding Absolute and Relative References

Introduction to Formulas and References

• The video focuses on working with formulas and references in Google Sheets, specifically absolute and relative references.

• A new sheet named "Referencias" is created to continue the learning process from previous videos.

Setting Up the Spreadsheet

• The speaker demonstrates how to add a new sheet by clicking on the "+" icon and renaming it through a double-click.

• Content from an existing sheet is copied into the new "Referencias" sheet, adjusting column widths for better visibility.

Calculating Gross Price

• In column D, the gross price (precio bruto) is calculated as the product of price (B2) and quantity (C2). This mirrors operations discussed in earlier videos.

• The formula is entered using multiplication, allowing users to drag down or double-click to fill subsequent rows automatically.

Handling Empty Cells in Formulas

• If there are empty cells below when dragging down formulas, they will stop copying once they reach an empty cell in column C. This highlights efficient ways to copy formulas quickly.

Calculating Net Price with Taxes

• The net price (precio neto) calculation includes adding taxes (21%) to the gross price; this involves creating a reference for tax rates in cell A14.

• The formula combines gross price with 21% of itself, demonstrating how percentages can be applied within calculations effectively.

Issues with Formula Copying

• When copying formulas downwards, issues arise if references change unexpectedly due to relative referencing; adjustments are needed for accurate calculations across rows.

Using Absolute References

• To prevent changes in specific cell references during formula copying, absolute referencing using dollar signs ($) is introduced; this keeps certain values constant regardless of where the formula is dragged or copied.

Practical Application of Absolute References

• An example shows how placing a dollar sign before either row or column locks that reference while allowing others to change dynamically as needed when dragging formulas across cells.

Conclusion on Reference Techniques

Understanding Excel Formulas and Discounts

Introduction to Formula Behavior

• The behavior of formulas in Excel changes based on the position of the dollar sign; it locks rows or columns. For example, using a formula with a dollar sign before the row keeps that row constant while allowing column movement.

• A new column titled "descuento" (discount) is added next to "impuestos" (taxes), initially set at 10%. Formatting is applied to ensure consistency between tax and discount displays.

Calculating Discounts

• To calculate discounts, multiply the net price by the discount percentage. The formula references cell 14 for the discount value, emphasizing absolute referencing for rows but not columns.

• After entering the formula, dragging down from the corner of the cell applies it across multiple rows efficiently. This method confirms that calculations are correct as they reflect a consistent 10% discount.

Final Price Calculation

• The final price is calculated by subtracting the discount from the net price without needing absolute references since these values should change as you move through rows.

• Prices are formatted in local currency (euros), ensuring clarity and uniformity across all displayed prices.

Dynamic Updates with Formulas

• Changes to discount percentages automatically update related calculations throughout the spreadsheet, showcasing one of Excel's powerful features: dynamic data updating.

Summarizing Total Prices

• A thick border is added above total prices for visual distinction. The SUM function is used to aggregate prices from specified cells effectively.