How to Multiply an Entered Number by a Fixed Amount in Excel
Question details
The user needs to calculate a product by multiplying an entered quantity by a fixed amount, such as 5 by $50 to return $250, without overwriting the entered value.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is building a worksheet that applies a fixed unit rate or amount to an entered quantity and expects the calculated product to appear in a separate result cell.
- Observed behavior
- The worksheet should return 250 when the quantity 5 is entered and the fixed amount is 50, while keeping the original input value unchanged.
Open your workbook in WPS Office Spreadsheets and decide which cells will hold the fixed amount, the entered quantity, and the calculated result. Keep the fixed amount in a separate cell so the formula can always reference it without overwriting the input.
Reference Separate Cells for the Fixed Amount and Quantity in a Formula
Use this method when you want to enter a quantity once and have a third cell calculate the product without overwriting the original input or creating a circular reference.
A formula cannot safely replace the original input in the same cell; that behavior would require VBA and could repeatedly multiply an already calculated result. Placing the fixed amount, quantity, and formula in separate cells keeps the calculation reliable.
Click cell A1, type 50, and press Enter to store the fixed amount.
Click cell B1, type 5, and press Enter to store the entered quantity.
Click cell C1, type =A1*B1, and press Enter. C1 displays 250.
Multiply Quantities by Fixed Amounts in WPS Office Spreadsheets
Use separate cells for the fixed amount and the entered quantity, then place a multiplication formula in a third cell. WPS Office Spreadsheets calculates the product automatically and preserves your input values.
- 1. Open WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the workbook where you want to multiply a quantity by a fixed amount.
- 2. Enter the fixed amount and quantity: Click A1, type 50, and press Enter. Click B1, type 5, and press Enter.
- 3. Enter the multiplication formula: Click C1, type =A1*B1, and press Enter.
- 4. Verify the calculation: Check that C1 shows 250, then change B1 to test that the result updates automatically.
Frequently Asked Questions
Why does putting =A1*B1 in A1 not work correctly?
It either creates a circular reference or overwrites the original input, so the formula cannot reliably recalculate when the quantity changes. Place the fixed amount and quantity in separate cells and put the formula in a third cell, such as =A1*B1 in C1.
Can I keep the fixed amount on another sheet?
Yes. Enter the fixed amount in a cell on another sheet, then reference it in the formula with the sheet name, for example =Sheet2!A1*B1.
How do I make the result display as a dollar amount?
Select the result cell, right-click, choose Format Cells, select Currency or Accounting, and choose the dollar symbol. The cell will then display the calculated product with a dollar sign.
Will the result update automatically when I change the quantity?
Yes. If C1 contains =A1*B1, changing B1 from 5 to 10 immediately recalculates and displays the new product in C1.
