The powerful Microsoft Excel spreadsheet processor easily multiplies any of your numbers and ranges. When can it really be useful in this regard? There are many options:
- There is no calculator at hand.
- Large ranges of numbers are multiplied, and you need to see which numbers are involved, and maybe replace some of them (in a conventional calculator without the function of saving the line, you will have to start the calculation again).
- Multiplication is only part of a complex formula.
As you can see, multiplication in Excel is a good thing. Virtually no formula can do without a multiplication operation.
Multiplication options in Excel
"Excel" allows you to multiply each other:
- Concrete numbers (positive, negative, integer, fractional).
- Numbers from the specified cells.
- Ranges of numbers.
- Boolean values
How is multiplication performed? Multiplication formula in Excel starts with an equal sign (=). As in any formula, the numbers (or links to cells containing them) connected by an asterisk (*) are indicated one after the other. Such a formula will look like this: "= 0.22 * -5 * 16" (when specific numbers are multiplied), or "= G4 * I5" (when cell references are specified as arguments). This is the first variant of multiplication.
Multiplication formula in Excel can be written using the built-in function "PRODUCTION":
Function name | Arguments | Description |
PRODUCTION | Number / Range 1; Number / Range 2; ... Number / Range 255; | The result of the function is the product of the arguments. In total, you can use up to 255 specific numbers or ranges (the size of the range itself is not limited). |
The advantage of using this particular function to perform multiplication in Excel is the ability to multiply not only numbers, but also ranges.
We proceed to consider specific examples.
Multiplication with an asterisk
Try to run the following examples on the Excel worksheet:
| Value 1 | Value 2 | Formula | Result |
A | B | C | D |
2 | 2 | 3 | = A2 * B2 | 6 |
3 | 0.7 | -6 | = A3 * B3 | -4.2 |
4 | TRUE | TRUE | = A4 * B4 | 1 |
5 | TRUE | FALSE | = A5 * B5 | 0 |
6 | FALSE | FALSE | = A6 * B6 | 0 |
Using them, you will see that it is very easy to multiply in Excel. Note that the result of the multiplication of logical quantities is 0 (FALSE) or 1 (TRUE).
The only drawback of this method of multiplication is that it cannot accept a range of numbers as an argument. Surely the maximum that any user will have enough will be determined somewhere at the end of the second dozen numbers or cells, sorted in order and alternated by "asterisk" signs.
The only way around this drawback is to use the “PRODUCT” function.
Multiplication using the “PRODUCT” function
Let's see how to do multiplication in Excel using the built-in function.
In the Excel workbook, create a similar plate and check the examples for it. Enter several values in one column, and enter several values in another column. Try to apply the “PRODUCT” function to your ranges, as well as perform manual multiplication through the asterisk.
| H | I | J | K |
| Value Range 1 | Value Range 2 | Result | Formula |
12 | 2 | 6 | 9378270.72 | = PRODUCT (H12: H16; I12: I16) |
thirteen | 7 | 14.2 | 9378270.72 | = PRODUCT (H12: I16) |
14 | 5 | -2.7 | 9378270.72 | = H12 * H13 * H14 * H15 * H16 * I12 * I13 * I14 * I15 * I16 |
fifteen | 0.26 | 4 | | |
16 | -10 | 56 | | |
The “Formula” column of this table clearly shows that if the ranges of values are nearby, then they can be used as one range. And also from the table you can see that the “PRODUCT” function multiplied all the numbers of the indicated ranges, and the result was the same as if we manually multiplied the values from these cells one at a time. As you can see, multiplication in Excel is a snap.
Interesting fact! Try now to remove one value from any cell in the range, let the cell become completely empty. You will see that in this case, the PROJECT function performed the calculation by simply releasing an empty cell. But the result of "manual" multiplication turned to zero. This is another clear advantage of the built-in function over the asterisk multiplication option - it does not take empty cells as zero, it just lets them out of the calculation.
There are situations when the calculations are carried out on several worksheets of the book, and a formula is included in the final sheet, which includes the product of the values from other sheets. If at least one of them turns out to be empty, a simple asterisk multiplication will set all your results to zero.
From this we can conclude that when conducting complex calculations on several sheets, it is better to use the “PRODUCT” function as a priority.
Multiplication table
Let's look at the end, how to create a multiplication table in Excel. This is completely uncomplicated.
| A | B | C | D | E |
1 | | 2 | 4 | 6 | 8 |
2 | 3 | = $ A2 * B $ 1 | 12 | 18 | 24 |
3 | 5 | 10 | 20 | thirty | 40 |
4 | 7 | 14 | 28 | 42 | 56 |
5 | 9 | 18 | 36 | 54 | = $ A5 * E $ 1 |
In the tradition of standard multiplication tables, we add one set of numbers to a column and another to a row. To be able to copy formulas, you need to use mixed links — use the “dollar” ($) sign to fix the column number with vertically spaced data and the row with horizontally spaced data: “= $ A2 * B $ 1”.