Have you ever needed to find the lowest number that is divisible by two (or more) given numbers in your spreadsheet? Google Sheets has a formula that will allow you to do this very easily. In this lesson I will show you how to use the least common multiple functions (LCM), to find the smallest number that two given numbers can be divided into.
"Least common multiple" is also referred to as "lowest common multiple", and "smallest common multiple".
The least common multiple, is the smallest positive integer that is divisible by each of the given numbers. This means finding the smallest number that the given numbers can evenly divide into, or in other words finding the smallest number that is a multiple of each given number. For example, consider the numbers 4 and 6.
The multiples of 4 are the following:
4, 8, 12, 16…
The multiples of 6 are the following:
6, 12, 18, 24…
The smallest number that appears in both lists is 6, which means that the least common multiple of 4 and 6 is equal to 12.
To find the least common multiple (LCM) in Google Sheets, follow these steps:
- Type =LCM( to begin the formula
- Type a number or the address of the cell that contains a number
- Type a comma, and then type the second number or the cell that contains the next number
- If you are using more than 2 numbers, repeat the previous step
- Press "Enter" on the keyboard. The final formula will look like this: =GCD(4,6). The formula will look like this when using cell references: =GCD(A1,B1)
After following these steps, the formula will display the highest number that and divide into both / all of the given numbers.
Finding the least common multiple of numbers
Let's start with a basic example of using the LCM function, where we will enter the numbers that we want to find the least common multiple of, directly into the formula.
In this example, we want to find the least common multiple of the numbers 6 and 4, which means that we want to find the lowest number that the numbers 6 and 4 can both divide into.
We will type these numbers directly into the criteria for the LCM function, by typing the first number, then typing a comma, and then typing the next number, as shown in the formula below.
=LCM(6,4)
As you can see in the image above, cell A1 displays the number "12", which is the least common multiple of the numbers 6 and 4. In other words, the number "6" is the lowest number that is divisible by both 6 and 4.
Finding the least common multiple by using cell references
If you want, you can use cell references to specify the numbers in the LCM function. This makes it easy to change the values that the formula refers to / calculates, where you can change the numbers in the cells instead of having to change the numbers in the formula itself.
In this example, we want to find the least common multiple of the numbers 6 and 4, but in this case we are entering the numbers into cells instead of directly into the formula. The number 6 is entered into cell B1, and the number 4 is entered into cell B2. The formula in cell B3 finds the least common multiple of the numbers in cells B1 and B2.
To use cell references when using the LCM function, type the address of the cell that contains the first number, type a comma, and then type the address of the cell that contains the next number, as shown in the formula below.
=LCM(B1,B2)
As you can see in the image above, cell B3 displays the number "12", which is the least common multiple of the numbers that are entered into cells B1 (6) and B2 (4).
Finding the least common multiple of more than two numbers
If you want, you can use the LCM function to find the least common multiple of more than two numbers.
In this example, we will find the least common multiple of three numbers (9, 6, and 4). This means that we will find the lowest number that is divisible by all three of these numbers.
To do this we will follow the same steps as in the previous example, except we will enter another number by typing another comma, and entering the third number, as shown in the formula below.
=GCD(9,6,4)
As you can see in the image above, cell A1 displays the number "36", which is the least common multiple of the numbers 9, 6, and 4. In other words, the number "36" is the lowest number that is divisible by all three numbers.
