GCD Function in Microsoft Excel
Part 1: Introduce
Definition:
The GCD function in Microsoft Excel returns the greatest common divisor of two or more integers.
Purpose:
To determine the largest integer that divides the given numbers without leaving a remainder.
Syntax & Arguments:
syntax
GCD(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. If any matter is not an integer, it will be truncated.
Return value:
The GCD function will return the greatest common divisor of the provided integers.
Remarks:
- If any argument is nonnumeric, GCD returns the
#VALUE!error value. - If any argument is opposing, GCD returns the
#NUM!error value. - The number one divides any value evenly.
- A prime number only has itself and one as even divisors.
- If a parameter to GCD is greater than or equal to 2^53, GCD returns the
#NUM!error value.
Part 2: Examples
Example 1:
- Purpose of illustration: To find the greatest common divisor of 8 and 12.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data1 | Data2 | Formula | Result |
| 2 | 8 | 12 | =GCD(A2,B2) | 4 |
- Explanation: The greatest common divisor of 8 and 12 is 4.
Example 2:
- Purpose of illustration: To find the greatest common divisor of 15, 25, and 35.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Formula | Result |
| 2 | 15 | 25 | 35 | =GCD(A2,C2) | 5 |
- Explanation: The greatest common divisor of 15, 25, and 35 is 5.
Example 3:
- Purpose of illustration: To find the greatest common divisor of 7 and 49.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data1 | Data2 | Formula | Result |
| 2 | 7 | 49 | =GCD(A2,B2) | 7 |
- Explanation: The greatest common divisor of 7 and 49 is 7, as 7 is a factor of both numbers.
Example 4:
- Purpose of illustration: To find the greatest common divisor of 9 and 15.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data1 | Data2 | Formula | Result |
| 2 | 9 | 15 | =GCD(A2,B2) | 3 |
- Explanation: The greatest common divisor of 9 and 15 is 3.
Example 5:
- Purpose of illustration: To find the greatest common divisor of 21, 28, and 35.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Formula | Result |
| 2 | 21 | 28 | 35 | =GCD(A2,C2) | 7 |
- Explanation: The greatest common divisor of 21, 28, and 35 is 7.
Example 6: Using GCD with IF Function
- Purpose of example: Find the GCD of two numbers if their sum exceeds 50; otherwise, return “Too Small”.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data1 | Data2 | Formula | Result |
| 2 | 35 | 20 | =IF(SUM(A2,B2)>50, GCD(A2,B2), "Too Small") | 5 |
- Explanation: Since the sum of 35 and 20 is 55, greater than 50, the GCD of 35 and 20 is calculated, which is 5.
Example 7: Using GCD with SUM Function
- Purpose of example: To find the GCD of the sum of three numbers and another number.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Formula | Result |
| 2 | 12 | 15 | 18 | =GCD(SUM(A2:C2), 45) | 15 |
- Explanation: The sum of 12, 15, and 18 is 45. The GCD of 45 and 45 is 15.
Example 8: Using GCD with VLOOKUP Function
- Purpose of example: To find the GCD of a value in a table using VLOOKUP and another given number.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Key | Value | Formula | Result |
| 2 | 1 | 24 | =GCD(VLOOKUP(1, A2:B2, 2, FALSE), 36) | 12 |
- Explanation: The VLOOKUP function finds the value corresponding to the key 1, which is 24. The GCD of 24 and 36 is 12.
Example 9: Using GCD with AVERAGE Function
- Purpose of example: To find the GCD of the average of three numbers and another number.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Formula | Result |
| 2 | 10 | 20 | 30 | =GCD(AVERAGE(A2:C2), 25) | 5 |
- Explanation: The average of 10, 20, and 30 is 20. The GCD of 20 and 25 is 5.
Example 10: Using GCD with MAX Function
- Purpose of example: To find the GCD of the maximum value among three numbers and another number.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Formula | Result |
| 2 | 14 | 28 | 42 | =GCD(MAX(A2:C2), 56) | 14 |
- Explanation: The maximum value among 14, 28, and 42 is 42. The GCD of 42 and 56 is 14.
Example 11: Using GCD with MIN Function
- Purpose of example: To find the GCD of the minimum value among three numbers and another number.
- Data sheet and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Data1 | Data2 | Data3 | Formula | Result |
| 2 | 22 | 33 | 44 | =GCD(MIN(A2:C2), 55) | 11 |
- Explanation: The minimum value among 22, 33, and 44 is 22. The GCD of 22 and 55 is 11.
Example 12: Using GCD with CONCATENATE Function
- Purpose of illustration: To concatenate a string with the GCD of two numbers.
- Data sheet and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data1 | Data2 | Formula | Result |
| 2 | 40 | 50 | =CONCATENATE("GCD Value: ", GCD(A2,B2)) | GCD Value: 10 |
- Explanation: The GCD of 40 and 50 is 10. The CONCATENATE function then combines this value with the “GCD Value: “string, resulting in the final text “GCD Value: 10”.
Part 3: Tips and tricks
- Always ensure that the numbers provided are integers. If not, the GCD function will truncate the numbers.
- Use the GCD function to find the highest common factor of two or more numbers.
- Be cautious when providing large numbers as arguments, as values greater than or equal to 2^53 will result in an error.
- The GCD function can be especially useful in mathematical and algebraic calculations where determining common factors is essential.