How to Divide and Multiply in One Excel Formula (4 Ways)

In most cases, Excel functions involve multiplying and dividing different types of data. The question of how to divide and multiply in Excel using one formula will be discussed with adequate examples.


Download Practice Workbook

Download this practice workbook below.


4 Ways to Divide and Multiply in One Excel Formula

In this article, the below dataset is for demonstration purposes. For multiplication and division purposes, we put two separate number columns. We will divide Number 1 by Number 2 and then will multiply the quotient with Number 1 again. And the final result will be presented in the Result column. How we managed to get to this result will be explained with adequate examples.


1. Using Traditional Division and Multiplication Formula

Enforcing the traditional division and multiplication formula we can easily apply division and multiplication in a single cell using a single formula.

Steps

  • First, you need to select the cell D5 and enter the following formula:

=(B5/C5)*B5

Here, asterisk sign (*) performs multiplication operation, and division sign (/) performs division operation.

  • And this operation will proceed following the PEMDAS Where the operations of Summation, Division, Subtraction, and Multiplications are conducted in order of parentheses first then exponential after that Multiplication and Division next. In the formula in cell D5, the division is inside the parentheses, which is cell B5 divided by cell C5, which will work out first, then it will be multiplied by cell B5 again.
  • After completing the formula, you will notice that the value in cell D5 shows the final product of the operations.

Using Traditional Division and Multiplication Formula

  •  Getting the result in cell D5, drag the fill handle icon to cell D10.
  • After doing that, all the cells in the result column are now filled with the desired values from dividing and multiplying of data from one Excel formula.

Using Traditional Division and Multiplication Formula

Read More: How to Multiply One Cell by Multiple Cells in Excel (4 Ways)


2. Combine QUOTIENT with PRODUCT Function in One Excel Formula

In this method we are going to use QUOTIENT and PRODUCT functions to divide and multiply number 1 column values and number 2 values, Here both of these functions will almost have the same role as “/” and  “*” signs do. Implementing helpful functions like QUOTIENT & PRODUCT can easily divide and multiply of data from one Excel formula in a single cell efficiently.

Steps

  • First, select the cell D5 and the following formula:
=PRODUCT(QUOTIENT(B5, C5),B5)
  • After this, you will see the division of value in cell B5 by cell C5 and the multiplication of this division by B5 in cell D5.

Combine QUOTIENT with PRODUCT Function in One Excel Formula

  • After this, drag the Fill Handle icon in the corner of cell D5 and drag it towards cell D10. You will notice that all the range of cells D5:D10 now has the result of the calculations.

Combine QUOTIENT with PRODUCT Function in One Excel Formula

How Does the Formula Actually Work?

  1. QUOTIENT(B5, C5): This function returns the division of value in cell B5 by cell C5.
  2. PRODUCT(QUOTIENT(B5, C5), B5): This function returns the value returned in the QUOTIENT function multiplied by the value in cell B5.

Read More: If Cell Contains Value Then Multiply Using Excel Formula (3 Examples)


Similar Readings

  1. How to Multiply Columns in Excel (9 Useful and Easy Ways)
  2. Multiply Two Columns in Excel (5 Easiest Methods)
  3. How to Use Multiply Sign in Excel (With 3 Alternative Methods)
  4. Multiply a Column by a Number in Excel (4 Easy Methods)
  5. How to Multiply a Column in Excel by a Constant (4 Easy Ways)

3. Implementing Paste Special Command

Paste special command is the most convenient tool to paste data to divide and multiply existing data in the cell. It directly pastes data that will either divide or multiply the originally existent numbers.

Steps

  • First, select the range of cells B5:B10 and copy those to the range of cells E5:E10.

Implementing Paste Special Command

  • Then select the cell values in the range of cells C5:C10, and then right-click in the mouse. From the context menu, click Copy.

Implementing Paste Special Command

  • Next, select the already existing values in the range of cells E5:E10, then right-click, from the context menu, and select Paste Special.

Implementing Paste Special Command

  • After clicking the Paste Special icon, a new window will open. From that window select Divide in the Operation group. After this click OK.

  • After clicking OK, you will notice that all numbers are now being pasted but at the same time, they also divide the previously existed values.

Implementing Paste Special Command

  • Then again select the values in the range of cells D5:D10. Click the right mouse button, from the context menu, and select Copy.

Implementing Paste Special Command

  • Next, select the already existing values in the range of cells E5:E10, then right-click, from the context menu, and select Paste Special.

Implementing Paste Special Command

  • After clicking the Paste Special icon, a new window will open. From that window select Multiply in the Operation group. After this click OK.

  • After clicking OK, you will notice that the values existing before pasting, are now multiplied by the pasted values from the range of cells D5:D10.

Read More: How to Multiply in Excel: Columns, Cells, Rows, & Numbers


4. Embedding VBA Macro to Divide and Multiply in Excel

Using a simple VBA Macro can drastically reduce the time to divide and multiply numbers in a single cell.

Steps

  • First, go to the Developer tab, then click Visual Basic.

Embedding VBA Macro to Divide and Multiply in Excel

  • Then click on Insert > Module.

  • In the Module window editor, enter the following code.
Function Multiply_Division(num1 As Integer, num2 As Integer)

    Multiply_Division = (num1 / num2) * num1

End Function

  • Next, close the module window and enter the following formula in the cell D5:
=Multiply_Division(B5,C5)
  • After entering the formula you will notice that cell D5 will have the desired output.

Embedding VBA Macro to Divide and Multiply in Excel

  • Then drag the Fill Handle icon in the corner of the cell and drag it down to cell D10 to fill up the Result column with output values.

Embedding VBA Macro to Divide and Multiply in Excel

Read More: How to Multiply Multiple Cells in Excel (4 Methods)


Conclusion

To sum it up, the question “how to divide and multiply in one Excel formula” is answered here in 4 different ways which consist of traditional formulas, with QUOTIENT and PRODUCT function continued using Paste Special tool, finally ending up with using VBA Macro. Among all of the methods used here, using QUOTIENT and PRODUCT formulas is the easier to understand and simple one. The VBA process is also less time-consuming and simplistic but requires prior VBA-related knowledge. Other methods don’t have such a requirement.

For this problem, a macro-enabled workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo