Function Problems

Calculate a Conditional VAT Amount in Excel Based on Y or N

Amos GikundaAmos Gikunda Nov 18, 2026 868 views

Question details

Create a formula that calculates a VAT-related amount when the control cell contains Y and leaves the result blank for N.

Calculate a Conditional VAT Amount in Excel Based on Y or 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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the formula

Select the result cell for row 20 and enter =IF(G20="Y",H20-$J$7,"").

2
Fill down

Drag the result cell's fill handle down through row 29. The $J$7 reference stays fixed while the row references change.

3
Check the results

Enter Y or N in column G and verify that Y shows the subtraction result and N displays a blank.

Use an IF Formula to Calculate the Amount Only for Y
Adjust the VAT calculation if needed: The example subtracts J7 directly; it does not calculate VAT from a percentage. If J7 contains a VAT rate and H20 is the taxable amount, replace H20-$J$7 with H20*$J$7 to calculate the VAT amount.
Calculate VAT in WPS Spreadsheet

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. 1. Open the workbook: Open WPS Spreadsheet and select Open to load your workbook.
  2. 2. Enter the formula: Select the result cell for row 20 and enter =IF(G20="Y",H20-$J$7,"").
  3. 3. Copy the formula: Drag the cell's fill handle down through row 29 to apply the formula to the remaining rows.
  4. 4. Verify and save: Check the results for Y and N in column G, then select File > Save.
Calculate VAT-related amounts only for rows marked YOpen and edit Microsoft Excel XLSX filesFill formulas down while keeping the VAT reference fixedUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.