The GCD and LCM functions in Excel are powerful mathematical tools for working with multiple numbers. This tutorial will guide you through their usage with clear examples and practical applications.
GCD Function
The GCD function returns the Greatest Common Divisor (also called Greatest Common Factor) of two or more integers. This is the largest positive integer that divides all the numbers without a remainder.
Syntax
GCD(number1, [number2], …)
- number1: The first number (required).
- number2, …: Additional numbers (optional). You can specify up to 255 numbers.
GCD Function Examples
Example 1: Basic GCD of Two Numbers
To find the GCD of 24 and 36:
=GCD(24, 36)
This will return 12 (the largest number that divides both 24 and 36).
Example 2: GCD of Multiple Numbers
To find the GCD of 48, 72, and 120:
=GCD(48, 72, 120)
This will return 24 (the largest number that divides all three numbers).
Example 3: Simplifying Fractions
To simplify the fraction 24/36, find the GCD and divide both numerator and denominator:
=24/GCD(24,36) & “/” & 36/GCD(24,36)
This simplifies to 2/3 (both divided by their GCD of 12).
Example 4: GCD with Cell References
To find the GCD of values in cells A1:A5:
=GCD(A1:A5)
This will return the GCD of all values in the range.
Interactive GCD Demo
LCM Function
The LCM function returns the Least Common Multiple (also called Lowest Common Factor) of two or more integers. This is the smallest positive integer that is divisible by all the numbers.
Syntax
LCM(number1, [number2], …)
- number1: The first number (required).
- number2, …: Additional numbers (optional). You can specify up to 255 numbers.
LCM Function Examples
Example 1: Basic LCM of Two Numbers
To find the LCM of 4 and 6:
=LCM(4, 6)
This will return 12 (the smallest number divisible by both 4 and 6).
Example 2: LCM of Multiple Numbers
To find the LCM of 3, 4, and 5:
=LCM(3, 4, 5)
This will return 60 (the smallest number divisible by 3, 4, and 5).
Example 3: Scheduling Problem
If three events occur every 6, 8, and 9 days, when will they all occur together?
=LCM(6, 8, 9)
This will return 72 days (when all three events align).
Example 4: LCM with Cell References
To find the LCM of values in cells B1:B4:
=LCM(B1:B4)
This will return the LCM of all values in the range.
Interactive LCM Demo
Relationship Between GCD and LCM
For any two numbers a and b: GCD(a,b) × LCM(a,b) = a × b
This means if you know the GCD of two numbers, you can calculate their LCM, and vice versa.
Example: Using the Relationship
For numbers 12 and 18:
=12*18/GCD(12,18)
This calculates the LCM as 36, which equals =LCM(12,18).
Excel GCD and LCM functions – Practical Applications
GCD Applications:
- Simplifying fractions to their lowest terms
- Dividing quantities into equal groups
- Finding common measurements or units
- Reducing ratios to simplest form
- Tiling or pattern design calculations
LCM Applications:
- Scheduling recurring events (when do they align?)
- Finding common denominators for fraction addition
- Production planning and synchronization
- Rotation and cycle calculations
- Inventory restocking schedules
- Project timeline coordination
Excel GCD and LCM functions – Tips and Tricks
- Non-integer values: Both GCD and LCM truncate decimal values to integers automatically.
- Range inputs: Both functions accept cell ranges, making it easy to work with multiple values.
- Zero handling: GCD treats 0 as having a GCD equal to the other number(s). LCM of 0 always returns 0.
- Single number: GCD and LCM of a single number both return that number.
- Performance: These functions can handle up to 255 numbers at once.
- Combine functions: Use GCD to simplify fractions before further calculations to improve accuracy.
- Validation: Use the relationship GCD × LCM = Product to verify your calculations.
Common Use Cases in Business
Use Case 1: Packaging Optimization
You have 48 apples and 36 oranges. What’s the largest number of identical gift baskets you can make using all the fruit?
=GCD(48, 36)
Answer: 12 baskets (each with 4 apples and 3 oranges).
Use Case 2: Meeting Schedules
Team A meets every 4 days, Team B every 6 days. When will both teams meet on the same day?
=LCM(4, 6)
Answer: Every 12 days both teams will meet together.
Use Case 3: Simplifying Data Ratios
Your data shows 150 successes and 225 attempts. What’s the simplified ratio?
=150/GCD(150,225) & “:” & 225/GCD(150,225)
Answer: 2:3 ratio (simplified from 150:225).