Excel Multiplication Formula – For students, students, especially office workers, Microsoft Excel is a lifesaver that makes assignments and work completed faster. The formula (formula) in it facilitates the difficult, simplifies the difficult. One of the most widely used is the multiplication formula.
Therefore, the multiplication formula is usually taught from the start because everyone must understand it. Moreover, how to implement it is simple, easy, and also fast. With this formula, we can process data automatically.
So, in this article, we will discuss the excel multiplication formula in full and the problems that often arise when applying it. Listen to the end, OK!
Get to know Formulas (Formulas) in Microsoft Excel
Before learning the multiplication formula in Microsoft Excel, you must first understand what is meant by a formula or formula. Because the formula is a very important thing in excel.
In general, the formula (formula) in the Ms. Excel is an equation that is used when you do calculations and also process certain values or data to get the results you want.
So if you want to do multiplication, you can write =3*2 and then press enter.
You can also add brackets “()” or other auxiliary signs in a table or range that has a specific address, alias cell reference. Well, generally writing formulas always refers to cell addresses such as A1, B1, C1, and so on.
As in the picture above, to multiply A1, B1, and C1, you can write =(A1*B1)*C1 or if you want you can also combine it with a certain value, for example =(A1*B1)+3. That’s more or less a brief description of the formula or formula in the Microsoft Excel software.
Complete Excel Multiplication Formula
There are many Excel multiplication formulas with different functions that you can use. Starting from being typed directly (manually), automatically, with the PRODUCT function, calculating percentages, multiplying money, and so on. Here is the full explanation.
Manual Excel Multiplication Formulas
1. Typed Directly
To do multiplication in Ms. Excel manually, you can type the numbers (data) directly into the cell by using the multiplication arithmetic operator (*).
For example, if you want to multiply the number 3 x 8 x 12, then write the formula directly in one of the cells. Because each formula in excel is written equal to (=), the writing becomes:
=3*8*12
After that press enter and you will get the result which is 288.
2. Numbers (Data) in Cell
You can also multiply numbers or data in cells. Usually this method is used when you make personal expense records, office financial reports, or the like.
As in the example below, to multiply the numbers you can write the formula =D1*D2*D3 in cell D4 then press enter, then you will get the result, which is 60.
3. Numbers (Data) from Other Sheets
In fact, you can multiply numbers on other sheets in Microsoft Excel. The trick is to write “=” in the cell you want, click all the cells that you will multiply until they run out, then press enter. To make it clearer, check the example in the image below.
Here we will use two sheets, namely sheet 1 and sheet 2. On sheet 1 there are numbers 3 and 4 while on sheet 2 there are numbers 2 and 12.
To multiply all these numbers, the formula is:
=D1*Sheet2!C1*Sheet1!D2*Sheet2!C2
after pressing enter, the result obtained is 288.
Automated Excel Multiplication Formulas
Apart from writing the cell names (eg D1, D2, and D3) manually, you can also do another method, namely type “=” then click the cell you want to multiply (D1), then type “*”, then click again on cell D2 , type “*” and click cell D3. Finally press enter.
You can use this formula to calculate multiple numbers in multiple cells at once. The trick is to first write a formula with the name of the cell in the cell where the results will be stored. After that point the cursor to the lower right corner of the cell until the cursor is in the + shape, then drag it down to the last cell. Like the example below:
After you get the multiplication result in cell C1 (=A1*B1), you can fill in cells C2, C3, C4, and C5 automatically in the above way. So the results you will get are as follows:
Automated Excel Multiplication Formulas with PRODUCT
Furthermore, you can use the special function “PRODUCT” to calculate multiplication in Microsoft Excel. It’s easy, really. Just click on the cell where the result will be stored later, then type “=PRODUCT” and click on the cell that you want to calculate, then type a comma “,” or a semicolon “;” (depending on the language you use). After that, click again what you want to calculate, then press enter. As in the example below:
So the formula obtained from this method is: =PRODUCT(A1;B1). After that, all you have to do is drag it down as explained earlier to fill cells C2 to C5.
You can learn other functions and their use in the book Mastering Microsoft Office Excel 2019 by Johan Arifin. This book is here to give you a solution to learn new things at ms. The new Excel 2019 is practical, effective, efficient and packaged in an attractive way so that you can operate Excel optimally.
Excel Multiplication Percent Formulas
1. How to Display Percent (%) in Ms. Excel
In Microsoft Excel, Sinaumed’s can also multiply by percent. But before that, you should know that Microsoft Excel cannot display the percent (%) format automatically.
Therefore, you must convert data or numbers into percent before multiplying them. You can do this by:
- Type the percent symbol (%) in the cell directly
- Use the “percentage” format in the Format Cells dialog box, or
- Change the writing format in cells using the Tab Number -> Percent (%) menu. You can also use the CTRL+SHIFT+% key combination on the keyboard.
Of all these methods, the most frequently used is the second method.
2. Replacing the Amount by Percentage
You can use this method if Sinaumed’s wants to increase or decrease the amount with a percentage. For example, you want to reduce your weekly expenses by 15%, use the formula to calculate it:
=amount to reduce*(1-percentage deduct)
To make it clearer, look at the image below:
=B1*(1-C1) then after pressing enter, the result obtained is IDR 170,000.
In this formula, the number 1 used has a value equivalent to 100%. So automatically Ms. Excel will first calculate the data or numbers in brackets, namely 1-C1 (100% -15%) and get a value of 85%. After that, the result is just multiplied by B2 (Rp. 200,000. This means that if you write it manually the calculation will be like this:
100%-15%=85%
IDR 200,000 x 85%= IDR 170,000
If you want to increase your expenses by 15%, simply replace the minus sign (-) with the plus (+) to get the formula:
=B2*(1+C1)
After that press enter. From here, all you have to do is fill in cells C2, C3, and C4 automatically by moving the cursor in cell D1.
3. Multiplying Numbers in Multiple Cells by Percent
Sinaumed’s can also multiply the numbers in multiple cells by percents. For example like the image below:
1. First enter the numbers you want to multiply by 15% in cells A1-A5
2. Type “15%” or the percentage you want in an empty cell then copy this number with the CTRL C combination
3. After copying, select the cell A1 to A5 by dragging the mouse cursor down.
4. Right click on the cell that you have selected, then click paste special or paste special and select Value>multiply then click ok
5. You will get the multiplication of the numbers in cells A1-A5 by 15% as below:
Excel Multiplication Formula with Rupiah or Other Currency
Sinaumed’s can also calculate multiplication with rupiah or other currencies in Excel. For example, to calculate the total expenses for one week. You can multiply the price of each item you buy by the amount.
The calculation formula is =(price of the item you buy*number of item). So, to make it easier, try to check the formula image below:
So the formula is =B2*C2 or IDR 85,000 x 2 = IDR 170,000. After filling in cell D2, all you have to do is drag the cursor down to fill in cells D3, D4, and D5. In this way, you will get the following results:
Where did the IDR 670,000 figure come from? The answer is from multiplying the numbers in cells D2 – D5 using the formula:
=SUM(D2:D5)
Excel Multiplication Formula Sideways (Constant Number)
Sometimes when using the Excel multiplication formula we want to calculate the data in a cell with the same number in another cell. Cases like this are usually referred to as multiplication of constant (fixed) numbers. Now, to calculate the multiplication, you can add the $ symbol into the formula in the cell before copying it. To make it clearer, check the sample image below:
In the example above, you want to multiply all the numbers in cells A2 through A6 by the number 5 in cell C2. You will write the amount in cells B2 to B6. So, to get the result, you can write the formula =A2*C2 in cell B2.
To fill in cells B3 to B6, you cannot copy the formula down from cell B2 because Ms. Excel assumes that cell C2 references (which are multiplied) change to C3, C4, and so on, for which there is no data, so the results in cells B3 to B6 will be zero.
So for the formula to work, you need to add a “$” symbol in front of the cell name you are multiplying (C2-C6) so the formula becomes: =
A2*$C2 and so on
Why do you have to add the “$” symbol in the formula? Because the symbol is in charge of telling Ms. Excel that the reference to C2 is absolute, so when you copy the formula from B2 to B3 to B6, the cell reference will always go to cell C2.
Sinaumed’s can also use applied formulas and functions in Microsoft Excel. You can see the formulas and functions in the book Formulas and Functions Applied to Microsoft Excel for Processing Data and Reports compiled by Madcoms.
Why is the Excel Multiplication Formula the Result #Value?
Sinaumed’s should know that when using the Excel multiplication formula, the result can show a #VALUE error. #VALUE itself is Ms. Excel to tell you that there is something wrong with writing the formula or cell that you are referencing. This error occurs very often and can be caused by several reasons, namely:
- There are text characters or symbols that cannot be read by Excel formulas
- Error in the use of a period or comma in the decimal number format
- When you combine numbers and text in one cell
- There are spaces in the Excel multiplication, addition, or other formulas
- There is invalid data
- Use of Plus and Minus signs
- The cells to be counted are empty, meaning they don’t have numbers
- Error in Array Formula
The question is, how do you solve the #VALUE error in this Excel multiplication? Here are some ways that you can apply based on information written by Nur Awaluddin in his book Excel Troubleshooting, 100+ Microsoft Excel Problems and Solutions.
How to Solve the #VALUE Error in the Excel Multiplication Formula
1. Replacing periods with commas
You can use this method when multiplying decimal numbers. Usually if you write a decimal number with a period (.), Excel will treat it as text, not a number. For example you are 234.5.
To fix this, please replace the full stop with a comma, so instead of writing 234.5 you can write 234.5. After that, just enter it into the formula you will use.
2. Change the Text Format to Number (Number)
You can also solve the #VALUE error by changing the text format to number. You can do this through the Format Cell or the Custom sub menu on the Format Cell menu.
3. Separating Numbers with Units
The third way is the easiest way to do it, namely by separating numbers with units in different cells. The aim is that Ms. Excel can read the numbers you enter into the cells as numbers, not text.
Tips for Avoiding #VALUE Errors in Excel Multiplication Formulas
- Write or input all numbers (data) into cells carefully. Make sure there are no other symbols, text, or non-numeric in the cell.
- Use the =ISERROR(value) function. If the results that come out are correct, then Ms. Excel will show a FALSE result, otherwise if the result is wrong then what is shown is TRUE.
- Check the data source. You should check the data source that you are going to calculate, who knows, the source is set with Merge & Center–a function to combine several cells at once. Errors like these can result in a #VALUE error in Excel multiplication formulas.
The #VALUE error often occurs, especially when you have to calculate a large amount of data. In addition, beginners or those who are just learning Ms. Excel also often encounters this #VALUE error due to ignorance. Therefore, you can apply the methods above to overcome the #VALUE error.
Closing
Thus the discussion about the excel multiplication formula , all multiplication formulas will make it easier for you to process data, so that the data is not wrong. Hopefully all the discussion in this article is useful for Sinaumed’s.
If you want to find various kinds of books about excel, then you can get them at sinaumedia.com . To support Sinaumed’s in adding insight, sinaumedia always provides quality and original books so that Sinaumed’s has #MoreWithReading information .