Calculate a Conditional VAT Amount in Excel Based on Y or N
Question details
Create a formula that calculates a VAT-related amount when the control cell contains Y and leaves the result blank for N.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses column G as a Y or N control, column H for values, and cell J7 as a VAT reference.
- Observed behavior
- The result should appear only for rows marked Y; rows marked N should display a blank.
Check that column G contains the Y or N controls, column H contains numeric values, and J7 contains the intended VAT reference. Confirm whether you need to subtract the value in J7 or use it as a percentage rate.
Use an IF Formula to Calculate the Amount Only for Y
Use this formula when the calculation is the value in column H minus the value in J7.
Select the result cell for row 20 and enter =IF(G20="Y",H20-$J$7,"").
Drag the result cell's fill handle down through row 29. The $J$7 reference stays fixed while the row references change.
Enter Y or N in column G and verify that Y shows the subtraction result and N displays a blank.

Create the Same Conditional VAT Formula in WPS Spreadsheet
Use WPS Spreadsheet's IF function to calculate an amount for rows marked Y and leave other rows blank. Adjust the calculation depending on whether J7 is an amount or a VAT rate.
- 1. Open the workbook: Open WPS Spreadsheet and select Open to load your workbook.
- 2. Enter the formula: Select the result cell for row 20 and enter =IF(G20="Y",H20-$J$7,"").
- 3. Copy the formula: Drag the cell's fill handle down through row 29 to apply the formula to the remaining rows.
- 4. Verify and save: Check the results for Y and N in column G, then select File > Save.

Frequently Asked Questions
How do I calculate VAT as a percentage instead of subtracting J7?
If H20 is the taxable amount and J7 contains the VAT rate, enter =IF(G20="Y",H20*$J$7,"") in the result cell.
Why does the formula use $J$7?
The dollar signs make J7 an absolute reference, so it stays fixed when you drag the formula down to other rows.
What happens if the control cell is blank or contains something other than Y?
The formula returns an empty string, so the result cell appears blank. This also applies when the control cell contains N.
Can I use this formula in WPS Spreadsheet?
Yes. Open the workbook in WPS Spreadsheet, enter the same IF formula in the result cell, and drag the fill handle down.




