How to Use IF and ISBLANK Correctly in a WPS Spreadsheets Cost Formula
Question details
The user needs a courier-cost formula in WPS Spreadsheets that remains blank when the cost cell is empty, adds £20 when the cost is below £100, and adds 20% when the cost is £100 or more.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a cost calculation formula that references a cost cell and returns different outcomes depending on whether the cell is blank, below £100, or £100 and above.
- Observed behavior
- The desired result is a blank cell for empty cost input, £20 for a cost below £100, and a 20% charge for a cost of £100 or more.
Make sure the cost input cell contains a true blank or a consistent empty-string result, because ISBLANK and the empty-string check behave differently. Also confirm the result cell is not formatted as Text so the formula can calculate properly.
Use a nested IF formula with K3="" to test an empty cost cell
Use this when you want a single, reliable formula that leaves the result blank for empty input and calculates the correct courier charge.
The formula checks the empty condition first. Once the cost cell is not blank, it compares the numeric value and returns £20 for amounts below 100 or 20% of the cost for amounts 100 and above.
Click the cell where the courier cost result should appear in WPS Spreadsheets.
Type =IF(K3="","",IF(K3<100,20,K3*20%)) and replace K3 with your actual cost cell reference.
Press Enter to apply the formula, then drag the fill handle down to copy it to the remaining cost rows.
Use ISBLANK(K3) as the blank check in the courier-cost formula
Choose this if you specifically want an ISBLANK-based formula and need to keep the blank test separate from the numeric comparison.
Calculate Blank-Safe Courier Costs in WPS Spreadsheets
Use WPS Spreadsheets to apply a blank-safe nested IF formula so courier costs stay empty for missing amounts, show £20 below £100, and calculate 20% for £100 and above. It works with standard Excel formulas and .xlsx files.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the spreadsheet that contains the courier cost column, and click the result cell.
- 2. Enter the blank-safe nested IF formula: Type =IF(K3="","",IF(K3<100,20,K3*20%)) and adjust K3 to match your cost cell reference.
- 3. Apply and copy the formula: Press Enter, then drag the fill handle down to apply the formula to the remaining rows.
- 4. Verify with sample values: Test empty, below-£100, and £100-and-above values to confirm blank, 20, and the 20% result appear correctly.
Frequently Asked Questions
Why should I avoid =IF(ISBLANK(K3)<100,...)?
ISBLANK(K3) returns TRUE or FALSE, not the numeric cost value. Comparing TRUE or FALSE to 100 does not perform the intended amount check; use =IF(K3="","",IF(K3<100,20,K3*20%)) or =IF(ISBLANK(K3),"",IF(K3<100,20,K3*20%)).
Can I use K3="" instead of ISBLANK(K3) in WPS Spreadsheets?
Yes. For a truly empty cell, both work. K3="" also treats the result of a formula that returns an empty string as blank, while ISBLANK does not.
What should I change if my cost is not in cell K3?
Replace every K3 in the formula with the actual cost cell reference, such as D2, and press Enter to recalculate.
Does this formula work in Microsoft Excel too?
Yes. The nested IF formula is standard and works in WPS Spreadsheets and Microsoft Excel, so .xlsx worksheets can be shared between the two applications.
