Excel Measurement Conversion Tutorial

Introduction

• Covered conversions between different units of measurement in Excel: gallon to liter, Fahrenheit to Celsius, pound to kilogram, and vice versa.
• Usage of the CONVERT function in Excel.
• Advanced techniques using VLOOKUP combined with CONVERT.

Basic Conversion

• Example: Gallon to Liter
  • Formula: =CONVERT(number, from_unit, to_unit)
  • Gallon: Select or hardcode the value.
  • Liter: CONVERT(1, "gal", "ltr") → 1 gallon = 3.7854 liters
  • Optional: Use ROUND function for cleaner results.

Important Notes

• Capitalization Matters
  • Fahrenheit to Celsius Example:
    • Correct: =CONVERT(80, "F", "C")
    • Incorrect: =CONVERT(80, "f", "c") results in an error.
• Special Units
  • Some units have unique capitalization: e.g. horsepower (hP).

Metric Prefixes

• Example: Pound to Kilogram
  • Formula: =CONVERT(10, "lbm", "kg")
  • Use prefixes for metric units (e.g., k for kilo).
  • Microsoft documentation provides a list of all prefixes.
  • Common prefixes include kilo (k), mega (M), giga (G), etc.

Bits to Bytes

• Conversion example for digital storage:
  • =CONVERT(10, "bit", "byte") → 10 bits = 1.25 bytes

Combining with VLOOKUP for Conversion

• Basic VLOOKUP Setup
  • Lookup table with distances (example: miles to kilometers).
  • VLOOKUP formula: Ensure it returns the correct distance unit.
• Using CONVERT with VLOOKUP
  • Combine VLOOKUP and CONVERT functions.
  • Example formula: =CONVERT(VLOOKUP(value, table, index, FALSE), "mi", "km")

Complex Table with Mixed Units

• Use helper columns to manage mixed units in a table:
  • Helper 1: Extract numerical value using the LEFT function.
  • Helper 2: Extract unit abbreviation using the RIGHT function.
  • Helper 3: Convert using an IF statement and the CONVERT function.
    • Example: =IF(unit="km", value, CONVERT(value, "mi", "km"))
    • Ensure numeric output using NUMBERVALUE function.

Summary and Conclusion

• Thorough explanation and examples of using the CONVERT function.
• Advice on handling capitalization and unique unit names.
• Tips for integrating VLOOKUP with measurement conversions.
• Encourage experimentation with helper columns for complex data sets.

Call to Action

• Download the tutorial file, subscribe to the YouTube channel, like the video, and leave comments/questions.