LCM Function in Microsoft Excel
Part 1: Introduce
Definition:
The LCM function in Microsoft Excel returns the least common multiple of integers.
Purpose:
The primary purpose of the LCM function is to determine the smallest positive integer, which is a multiple of all the provided integer arguments. It’s beneficial when adding fractions with different denominators.
Syntax & Arguments:
syntax
LCM(number1, [number2], ...)
Explain the Arguments in the function:
- number1, number2,…: Number1 is mandatory, while subsequent numbers are optional. You can provide between 1 to 255 values for which you want the least common multiple. If a value is not an integer, it will be truncated.
Return value:
The function returns the least common multiple of the provided integers.
Remarks:
- If any argument is nonnumeric, LCM returns the #VALUE! Error value.
- If any argument is less than zero, LCM returns the #NUM! Error value.
- If the result of LCM(a,b) is greater than or equal to 2^53, LCM returns the #NUM! Error value.
Part 2: Examples
Example 1:
- Purpose of illustration: Find the least common multiple of 5 and 2.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data1 | Data2 | Formula | Result |
| 2 | 5 | 2 | =LCM(A2, B2) | 10 |
- Explanation: The least common multiple of 5 and 2 is 10.
Example 2:
- Purpose of illustration: Find the least common multiple of 24 and 36.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data1 | Data2 | Formula | Result |
| 2 | 24 | 36 | =LCM(A2, B2) | 72 |
- Explanation: The least common multiple of 24 and 36 is 72.
Example 3:
- Purpose of illustration: To find the least common multiple of three numbers.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Formula | Result |
| 2 | 3 | 4 | 5 | =LCM(A2, B2, C2) | 60 |
- Explanation: The least common multiple of 3, 4, and 5 is 60.
Example 4:
- Purpose of example: To find the least common multiple of two numbers, one of which is a decimal.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data1 | Data2 | Formula | Result |
| 2 | 7.5 | 3 | =LCM(A2, B2) | 21 |
- Explanation: The decimal 7.5 is truncated to 7. The least common multiple of 7 and 3 is 21.
Example 5:
- Purpose of illustration: To demonstrate the error when using a negative number.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data1 | Data2 | Formula | Result |
| 2 | -5 | 10 | =LCM(A2, B2) | #NUM! |
- Explanation: Since one of the numbers is negative, the LCM function returns the #NUM! Error value.
Example 6: Using LCM with IF Function
- Purpose of example: Find the LCM of two numbers if both are greater than 5; otherwise, return “Too Small”.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Formula | Result | Message |
| 2 | 6 | 8 | =IF(AND(A2>5, B2>5), LCM(A2, B2), "Too Small") | 24 |
- Explanation: Since 6 and 8 are more significant than 5, the LCM of 6 and 8 is calculated as 24.
Example 7: Using LCM with SUM Function
- Purpose of example: To find the LCM of the sum of three numbers with another number.
- Data sheet and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Data4 | Formula | Result |
| 2 | 3 | 4 | 5 | 6 | =LCM(SUM(A2:C2), D2) | 78 |
- Explanation: The sum of 3, 4, and 5 is 12. The LCM of 12 and 6 is 78.
Example 8: Using LCM with VLOOKUP Function
- Purpose of example: To find the LCM of a value retrieved using VLOOKUP with another number.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Key | Value | Data4 | Formula | Result |
| 2 | 1 | 12 | 8 | =LCM(VLOOKUP(1, A2:B2, 2, FALSE), C2) | 24 |
- Explanation: The VLOOKUP function retrieves the value 12 for the key 1. The LCM of 12 and 8 is 24.
Example 9: Using LCM with AVERAGE Function
- Purpose of example: Find the LCM of the average of three numbers with another number.
- Data sheet and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Data4 | Formula | Result |
| 2 | 6 | 12 | 18 | 3 | =LCM(AVERAGE(A2:C2), D2) | 9 |
- Explanation: The average of 6, 12, and 18 is 12. The LCM of 12 and 3 is 9.
Example 10: Using LCM with MAX Function
- Purpose of example: Find the LCM of the maximum value among three numbers with another number.
- Data sheet and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Data4 | Formula | Result |
| 2 | 5 | 10 | 15 | 4 | =LCM(MAX(A2:C2), D2) | 60 |
- Explanation: The maximum value among 5, 10, and 15 is 15. The LCM of 15 and 4 is 60.
Example 11: Using LCM with MIN Function
- Purpose of example: Find the LCM of the minimum value among three numbers with another number.
- Data sheet and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Data4 | Formula | Result |
| 2 | 9 | 18 | 27 | 6 | =LCM(MIN(A2:C2), D2) | 18 |
- Explanation: The minimum value among 9, 18, and 27 is 9. The LCM of 9 and 6 is 18.
Example 12: Using LCM with CONCATENATE Function
- Purpose of illustration: To concatenate a string with the LCM of two numbers.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data1 | Data2 | Formula | Result |
| 2 | 7 | 14 | =CONCATENATE("LCM Value: ", LCM(A2, B2)) | LCM Value: 14 |
- Explanation: The LCM of 7 and 14 is 14. The CONCATENATE function then combines this value with the “LCM Value: “string, resulting in the final text “LCM Value: 14”.
Part 3: Tips and tricks
- The LCM function is especially useful in scenarios involving fractions, as it helps find a common denominator.
- Always ensure the numbers provided as arguments are positive to avoid the #NUM! Error.
- If you’re unsure about the nature of your data, consider using conditional functions to check for negative values before applying the LCM function.
- Remember that non-integer values will be truncated, so it’s an excellent practice to round or format your data accordingly before using the LCM function.