WPS Spreadsheets: Automatically Update Stock from IN and OUT Tables
Question details
The user needs to update stock automatically across multiple rows using incoming and outgoing quantities.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A stock table contains Date, IN, OUT, and Stock columns, with an opening stock value stored separately.
- Observed behavior
- The desired result is a running stock balance where each row adds the cumulative IN quantities and subtracts the cumulative OUT quantities from the opening stock.
Arrange the worksheet with Date, IN, OUT, and Stock columns, and place the opening stock quantity in a dedicated cell such as F1. Make sure the IN and OUT values are numeric rather than text.
Use a cumulative formula to calculate the running stock balance
Use this method when each row should show the previous stock plus incoming quantities minus outgoing quantities.
The cumulative ranges expand as the formula is copied downward. The absolute references keep the opening-stock cell and starting rows fixed, while the ending row changes for each transaction.
In WPS Spreadsheets, create columns for Date, IN, OUT, and Stock. Place the opening stock value in cell F1, with IN in column B, OUT in column C, and Stock in column D.
Select cell D2 and enter =$F$1+SUM($B$2:B2)-SUM($C$2:C2), then press Enter.
Select D2, drag the fill handle down the Stock column, or use Home > Fill > Down to copy the formula through all transaction rows.
Enter or review quantities in the IN and OUT columns. IN values increase the balance, while OUT values reduce it immediately.
Request a tailored formula for inventory allocation
Use this approach when incoming quantities must be matched to specific outgoing rows instead of producing a simple running balance.
Calculate Automatic Stock Balances in WPS Spreadsheets
WPS Spreadsheets lets you build a running inventory balance with familiar formulas, fill the calculation across rows, and keep stock totals synchronized as IN and OUT quantities change. It supports Microsoft Excel workbooks for easy migration.
- 1. Open a spreadsheet: Launch WPS Office and open an existing Excel workbook or choose Spreadsheet from the New menu.
- 2. Set up the inventory columns: Create Date, IN, OUT, and Stock columns, then enter the opening stock in a separate cell such as F1.
- 3. Add the running-balance formula: Select D2 and enter =$F$1+SUM($B$2:B2)-SUM($C$2:C2), assuming IN is in column B and OUT is in column C.
- 4. Fill and verify the calculation: Copy D2 down the Stock column and change an IN or OUT value to confirm that the balance updates automatically.
Frequently Asked Questions
What formula automatically updates stock from IN and OUT quantities?
If IN is in column B, OUT is in column C, and the opening stock is in F1, enter =$F$1+SUM($B$2:B2)-SUM($C$2:C2) in D2, then copy it down.
Why are dollar signs used in the stock formula?
The dollar signs make the opening-stock cell and the starting points of the cumulative ranges absolute. This keeps the formula structure correct when it is filled down.
How does an OUT quantity affect the running stock balance?
An OUT quantity is included in the cumulative SUM for column C and is subtracted from the opening stock and cumulative IN total, reducing the balance on that row.
Can this formula allocate specific incoming batches to outgoing rows?
No. It calculates a running balance only. Batch allocation requires additional rules, such as first-in-first-out matching, and a tailored formula based on the workbook layout.
