Fix an Excel Formula for Isolated Buy or Sell Entries in WPS Office Spreadsheets
Question details
The user needs to correct an Excel formula in WPS Office Spreadsheets so isolated Buy or Sell transactions return blank instead of an unwanted result, without using IFERROR in a way that leaves referenced cells blank and breaks later calculations.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook tracks Buy and Sell transactions in columns N and O. The formula calculates profit/loss for normal sequences but needs to handle isolated entries cleanly.
- Observed behavior
- For an isolated transaction, the formula returns an unwanted result. Using IFERROR leaves a referenced cell blank, causing a downstream calculation to fail.
Open the worksheet in WPS Office Spreadsheets and locate the transaction-type column (for example, column N for Buy/Sell) and the price column (column O). Check whether blank cells occur at the end of an isolated entry before editing the formula.
Use a conditional formula that checks the next transaction and handles blank values directly
Use this solution when normal Buy/Sell sequences calculate correctly but isolated transactions produce an unwanted result or downstream formulas fail because a referenced cell is blank.
This formula removes the need for IFERROR around the entire calculation. It first checks whether the next row continues the same transaction type or is blank, returns an empty string for isolated entries, and only then computes the signed price difference using LOOKUP.
Click the cell in the results column that currently returns the unwanted result for an isolated Buy or Sell entry, for example P96.
Replace the existing formula with =IF(OR(N96=N97,N97=""),"",IF(N96="Buy",-1,1)*(LOOKUP(2,1/(N$37:N96=IF(N96="Sell","Buy","Sell")),O$38:O96)-O96)*20000).
Change N$37:N96 and O$38:O96 so the N range starts at your first transaction row and the O range starts one row below the N range start. Also confirm O96 is not blank when using this calculation.
For any later formula that references this result, wrap the reference in a blank check such as =IF(P96="","",P96*other_cell) instead of relying on IFERROR.
Fix isolated Buy or Sell formula errors with WPS Office Spreadsheets
Open the workbook in WPS Office Spreadsheets and apply a conditional IF, OR, and LOOKUP formula that returns blank for isolated Buy or Sell entries. WPS Office preserves Excel workbook behavior while remaining free and lightweight.
- 1. Open the file in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the Excel workbook that contains the Buy/Sell transaction formula.
- 2. Locate the results column: Go to the sheet and click the cell where the isolated transaction formula should be fixed.
- 3. Enter the conditional formula: Type or paste the IF/OR/LOOKUP formula, then adjust the N and O ranges to match the first data row in your workbook.
- 4. Test with an isolated entry: Select an isolated Buy or Sell row and confirm the formula returns blank instead of a calculated value.
- 5. Save as Excel workbook: Press Ctrl+S or click Menu > Save As, choose Excel Workbook (*.xlsx), and keep the file compatible with Microsoft Excel.
Frequently Asked Questions
Why does my formula still calculate a value for an isolated Buy or Sell entry?
Because the original formula assumes every entry has a related next row. Add the condition OR(N96=N97,N97="") so the formula returns blank when the next transaction is the same type or blank.
Why do I get an error in a later calculation after using IFERROR?
IFERROR can return a blank cell, and if a downstream formula expects a number, it fails. Use a blank-handling check in the downstream formula, or return blanks from the source formula and handle them explicitly.
How do I adapt the LOOKUP range when my data starts on a different row?
Change N$37:N96 to start at your first data row and O$38:O96 to start one row below that N range start. For example, if data starts at N5, use N$5:N64 and O$6:O64.
Can I use this Excel formula in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports Excel functions such as IF, OR, LOOKUP, and ISNUMBER, and opens .xlsx files without converting them.
