Excel offers several ways to Multiply values in a worksheet. The most basic method is to use the multiplication operator (*), but you can also use the PRODUCT function to multiply multiple values or use the SUMPRODUCT function to multiply and sum at the same time. Here’s a breakdown of each method.

Formulas in Excel for Multiplication

The product of two or more numbers is calculated using Formulas in Excel for Multiplication. The multiplication operator in Excel is denoted by the asterisk (*) sign.

You can use a formula with the asterisk symbol between the values to be multiplied to multiply two or more numbers. As an illustration, the formula =2*3 would multiply the numbers 2 and 3 to produce a result of 6.

25+ Formulas in Excel for Multiplication

Below are a few popular MS Excel formulas in Excel for multiplication with examples.

1. Multiplication Operator

The multiplication operator is represented by the asterisk () symbol in Excel. To multiply two or more values, simply type the values separated by the multiplication operator into a cell. For example, to multiply 5 and 10, type “=510” into a cell, without quotes. When you press Enter, Excel will display the result, which is 50 in this case.

=A1*B1

You can also reference cell values in the multiplication formula. For example, if you have two values in cells A1 and B1 and you want to multiply them, type “=A1*B1” into another cell, such as C1. Excel will multiply the values in A1 and B1 and display the result in C1.

2. PRODUCT Function

The PRODUCT function is used to multiply two or more values together. You can enter the values directly into the formula or reference cell values. Here’s the syntax for the PRODUCT function:

=PRODUCT(value1, [value2], ...)

To use the PRODUCT function, simply type “=PRODUCT(” into a cell, followed by the values or cell references separated by commas, and then close the formula with a closing parenthesis. For example, to multiply the values in cells A1 and B1, type “=PRODUCT(A1, B1)” into another cell, such as C1.

Note that you can enter up to 255 values in the PRODUCT function.

Also Read – SBI Credit Card Reward Points & Redeemption Process

3. SUMPRODUCT Function

The SUMPRODUCT function is used to multiply two or more sets of values together and then sum the results. Here’s the syntax for the SUMPRODUCT function:

=SUMPRODUCT(array1, [array2], ...)

To use the SUMPRODUCT function, type “=SUMPRODUCT(” into a cell, followed by the values or cell references separated by commas, and then close the formula with a closing parenthesis. For example, to multiply the values in cells A1 and B1 and then sum the results, type “=SUMPRODUCT(A1:B1)” into another cell, such as C1.

Note that you can enter up to 255 arrays in the SUMPRODUCT function. Also, the arrays must be of the same size and shape.

4. Using the “*” Operator

You can also use parentheses to group values together and then multiply them using the “*” operator. For example, if you want to multiply the sum of A1 and B1 by the value in C1, you can use the following formula:

=(A1+B1)*C1

Excel will first add the values in A1 and B1, and then multiply the result by the value in C1.

5. Multiplying Range of Cells

If you want to multiply a range of cells, you can use the “*” operator or the SUMPRODUCT function. For example, to multiply the values in cells A1:A5, you can use the following formula:

=PRODUCT(A1:A5)

Or, you can use the SUMPRODUCT function like this:

=SUMPRODUCT(A1:A5)

This will multiply the values in cells A1 through A5 and then sum the results.

6. Cell References in Formulas

When you use cell references in formulas, be sure to use absolute references (with dollar signs) if you want the formula to always reference the same cells. For example, if you want to multiply the value in cell A1 by the value in cell B1, you can use the following formula:

=$A$1*$B$1

The dollar signs in front of the row and column numbers make the cell references absolute, so if you copy the formula to other cells, the references will always point to cells A1 and B1.

7. Rounding the Result

A multiplication formula with multiple decimal places may show the outcome in Excel. Use the ROUND function to round the output to a specific number of decimal places. For instance, you may use the following formula to round the outcome of a multiplication formula in cell C1 to two decimal places:

=ROUND(C1,2)

This will round the value in cell C1 to two decimal places.

8. Multiplying Negative Numbers

When multiplying negative numbers in Excel, be aware of how the negative sign is used. If you multiply two negative numbers, the result will be positive. If you multiply a positive number and a negative number, the result will be negative. For example, if you multiply -5 and -10, the result will be 50, but if you multiply -5 and 10, the result will be -50.

Also Read – Top 30 Apps Refresh Shortcut Key

9. Multiplying Fractions

To multiply fractions in Excel, you can use the multiplication operator or the PRODUCT function. For example, to multiply 1/4 and 3/8, you can use the following formula:

=1/4*3/8

This will display the result, which is 0.09375. Alternatively, you can use the PRODUCT function like this:

=PRODUCT(1/4,3/8)

This will also display the result, which is 0.09375.

10. Multiplying Large Numbers

Excel may make mistakes when multiplying very big numbers because of the quantity of the numbers. Although Excel can only handle numbers with up to 15 digits, you may use the POWER function to divide larger numbers into smaller chunks if you need to multiply them. If you want to multiply 100,000 by 200,000, for instance, you can use the formula below:

=POWER(10,5)2POWER(10,5)*2

This will multiply the numbers by breaking them down into powers of 10, which Excel can handle more easily. The result will be 20,000,000,000.

11. Multiplying Matrices

If you need to multiply matrices in Excel, you can use the MMULT function. The MMULT function multiplies two matrices together and returns the result as a matrix. For example, to multiply the matrices A and B together, you can use the following formula:

=MMULT(A,B)

This will multiply the two matrices together and return the result as a new matrix.

12. Multiplying with Logic

You can also use multiplication with conditional logic in Excel. For example, if you want to multiply the values in column A by 2 if the value in column B is greater than 10, you can use the following formula:

=IF(B1>10,A1*2,A1)

This formula checks if the value in cell B1 is greater than 10. If it is, it multiplies the value in cell A1 by 2. If it’s not, it returns the value in cell A1 without any changes.

13. Multiplying Time Values

You can also multiply time values in Excel. For example, if you want to calculate the total number of hours worked based on the hours and minutes worked, you can use the following formula:

=(A1*24)+(B1/60)

The hours worked in cell A1 are multiplied by 24 to obtain decimal values, and the minutes worked in cell B1 are added and divided by 60 to determine the total number of hours worked. The outcome will be shown in decimal form.

14. Multiplying Array Formulas

Excel’s array formulae can also be used to multiply values. Formulas called arrays can calculate many values simultaneously. You must first choose the range of cells where you want the result to be shown before entering the formula and pressing Ctrl + Shift + Enter to enter it as an array formula. For instance, you can use the array formula shown below to multiply the values in cells A1 to A5 by 2.

{=A1:A5*2}

Note that you need to enclose the formula in curly braces {} to indicate that it’s an array formula.

15. Multiplying Mixed References

Excel allows you to multiply cells using relative, absolute, or mixed references. A mixed reference combines an absolute reference with a relative reference. For instance, you can use the following formula to multiply the value in cell A1 by the value in cell $B$1, which is an absolute reference:

=A1*$B$1

The dollar signs ($) before the column and row references in cell $B$1 make it an absolute reference, which means it won’t change when you copy or fill the formula to other cells.

16. Multiplying Text Strings

Additionally, Excel supports text string multiplication. When a text string is multiplied by a number, the text string is repeated many times. For instance, you may use the following formula to repeat the word “Hello” three times:

="Hello"*3

This will display the text string “HelloHelloHello”.

Also Read – 200+ MS Excel Shortcut Keys for Beginner to Pro

17. Multiplying Cell Ranges

Excel also allows you to multiply cell ranges. Cells are multiplied one by one when you multiply cell ranges. For instance, you can use the formula below to multiply the values in cells A1 through A5 by the values in cells B1 through B5.

=A1:A5*B1:B5

This will multiply the values in cells A1 and B1, A2 and B2, A3 and B3, and so on, and return the result in a new range of cells.

18. Multiplying SUMPRODUCT

You can also use the SUMPRODUCT function to multiply cell ranges in Excel. The SUMPRODUCT function multiplies the corresponding values in two or more arrays and then adds the results together. For example, if you want to multiply the values in cells A1:A5 by the values in cells B1:B5, and then sum the results, you can use the following formula:

=SUMPRODUCT(A1:A5,B1:B5)

This will multiply the values in cells A1 and B1, A2 and B2, A3 and B3, and so on, and return the sum of the results.

19. Multiplying Power Function

You can use the power function in Excel to raise a number to a power. The power function is represented by the “^” symbol in Excel. For example, if you want to raise the value in cell A1 to the power of 2, you can use the following formula:

=A1^2

This will raise the value in cell A1 to the power of 2 and return the result.

20. Multiplying PI Function

Excel’s PI function returns pi to the provided number of decimal places, or 3.14159265358979. This function can be used to determine a circle’s diameter or area in addition to multiplication. For instance, you may use the following formula to determine the circumference of a circle with a radius of 3:

=2*PI()*3

This will multiply the value of pi by 2 and by the radius of the circle (3) to give you the circumference of the circle.

21. Multiplying EXP Function

The EXP function in Excel returns the value of e (2.71828182845904) raised to a power. You can use this function in conjunction with multiplication to calculate exponential growth or decay. For example, if you want to calculate the value of an investment after 10 years with an annual interest rate of 5%, you can use the following formula:

=1000EXP(0.0510)

This will multiply the initial investment (1000) by e raised to the power of the annual interest rate (0.05) multiplied by the number of years (10) to give you the final value of the investment.

22. Multiplying SUM and Array

To multiply numbers in Excel, you can also utilise the SUM function in conjunction with array formulas. A unique kind of formula called an array formula has the ability to process calculations on several cells simultaneously. You can use the following array formula, for instance, if you wish to multiply the values in cells A1 through A5 by the values in cells B1 through B5, then add the results.

{=SUM(A1:A5*B1:B5)}

To enter an array formula, you need to press Ctrl + Shift + Enter instead of just Enter.

23. Multiplying MMULT Function

The MMULT function in Excel is used to multiply two matrices together. A matrix is a rectangular array of numbers. For example, if you have a matrix of values in cells A1:B2 and another matrix of values in cells C1:D2, you can use the following formula to multiply them together:

=MMULT(A1:B2,C1:D2)

This will multiply the two matrices together and return the result in a new matrix.

24. Multiplying LINEST Function

The LINEST function in Excel is used to calculate the slope and y-intercept of a linear regression line for a set of data. You can use this function in conjunction with multiplication to make predictions based on the data. For example, if you have a set of data in cells A1:B10 and you want to predict a value of x, you can use the following formula:

=LINEST(B1:B10,A1:A10)*x+LINEST(B1:B10,A1:A10,2)

This will multiply the slope of the regression line (which is returned by the LINEST function with no arguments) by the value of x and add the y-intercept of the regression line (which is returned by the LINEST function with a second argument of 1).

25. Multiplying QUOTIENT

The QUOTIENT function in Excel is used to divide two numbers and return the integer portion of the result. You can use this function in conjunction with multiplication to calculate the product of two numbers rounded down to the nearest integer. For example, if you want to multiply the values in cells A1 and A2 together and round the result down to the nearest integer, you can use the following formula:

=QUOTIENT(A1*A2)

This will multiply the values in cells A1 and A2 together and return the result rounded down to the nearest integer.

26. Multiplying MROUND

The MROUND function in Excel is used to round a number to the nearest multiple of a specified value. You can use this function in conjunction with multiplication to calculate the product of two numbers rounded to the nearest multiple of a specified value. For example, if you want to multiply the values in cells A1 and A2 together and round the result to the nearest multiple of 10, you can use the following formula:

=MROUND(A1*A2,10)

This will multiply the values in cells A1 and A2 together and round the result to the nearest multiple of 10.

27. Multiplying RANDBETWEEN

The RANDBETWEEN function in Excel is used to generate a random integer between two specified values. You can use this function in conjunction with multiplication to generate a random number within a specified range. For example, if you want to generate a random number between 1 and 10 and then multiply it by the value in cell A1, you can use the following formula:

=RANDBETWEEN(1,10)*A1

This will generate a random integer between 1 and 10 and then multiply it by the value in cell A1.

28. Multiplying with Statements

In Excel, conditional statements can be used to multiply integers under specific circumstances. You can use the following formula, for instance, to multiply the values in cells A1 through A10 by 2 if they are larger than 5 and by 3 if they are less than or equal to 5:

=IF(A1:A10>5,A1:A102,A1:A103)

This will multiply the values in cells A1:A10 by 2 if they are greater than 5 and by 3 if they are less than or equal to 5.

Frequency Asked Questions

Hope you enjoy this information. Please tell us your thought and suggestions about this article. Keep reading more SEO tips & how-to guides and do follow and subscribe to our social media platforms for more latest updates.