Function Problems

Calculate VAT Only When Y Is Entered in Excel

Maira MehtabMaira Mehtab Nov 18, 2026 869 views

Question details

Calculate VAT only when an employee enters Y in a control column, and otherwise display a blank.

Calculate VAT Only When Y Is Entered in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
An employee enters a flag for each row, and the worksheet calculates VAT for rows marked Y.
Observed behavior
Rows marked Y should show a VAT result; rows marked N or another value should appear blank.
Before you start

Identify separate cells for the control flag, the amount before VAT, the VAT rate, and the result. In the example below, these are G20, F20, J7, and H20 respectively; enter the rate in J7 as a percentage, such as 20%.

Solution 1Recommended

Use IF to calculate VAT for rows marked Y

Use this formula when the source amount excludes VAT and you want to calculate the VAT amount in a separate column.

1
Enter the source values

Enter the amount before VAT in F20, the control value in G20, and the VAT rate as a percentage in J7.

2
Enter the formula

Select H20 and enter =IF(G20="Y",F20*$J$7,""). The formula calculates VAT when G20 contains Y and displays a blank otherwise.

3
Fill the remaining rows

Select H20 and drag its fill handle down through H29. The $J$7 reference keeps the VAT rate fixed while the row references change.

4
Check the result

Enter Y in G20 to check the calculation, then enter N to confirm that H20 appears blank. Replace the example cell references with those used in your worksheet.

Use IF to calculate VAT for rows marked Y
Avoid a circular reference: Do not use H20 as the input amount in a formula entered in H20. If J7 contains a fixed VAT amount rather than a rate, adjust the calculation to match your intended operation.
Calculate VAT in WPS Spreadsheets

Use an IF formula for conditional VAT in WPS Spreadsheets

Open your workbook in WPS Spreadsheets and use an IF formula to calculate VAT for rows marked Y while leaving other results blank.

  1. 1. Open the workbook: Open WPS Spreadsheets and select Open to choose your Excel workbook.
  2. 2. Enter the inputs: Enter an amount before VAT in F20, a control value in G20, and the VAT rate as a percentage in J7.
  3. 3. Add the IF formula: Select H20 and enter =IF(G20="Y",F20*$J$7,""). Adjust the cell references if your worksheet uses different columns.
  4. 4. Fill and save: Drag the fill handle from H20 through H29, then choose File > Save or Save As to save the workbook.
Calculate VAT conditionally with the IF functionOpen and edit Microsoft Excel XLSX and XLS filesFill formulas down a column while keeping the VAT rate fixedWork with a familiar spreadsheet interface
QA img-9

Frequently Asked Questions

Why does the formula show a circular reference warning?

The formula refers to its own cell. If the formula is in H20, put the source amount in another cell, such as F20, and reference F20 instead.

Will a lowercase y trigger the calculation?

Yes. Excel's standard text comparison is not case-sensitive, so G20="Y" also matches lowercase y. Extra spaces can prevent a match.

Why does the formula use $J$7 instead of J7?

The dollar signs make the VAT-rate reference absolute. When you drag the formula down from H20, it continues to use J7 instead of changing to J8, J9, and so on.

How can I display the amount including VAT instead of the VAT alone?

If F20 is the amount before VAT and J7 is the rate, enter =IF(G20="Y",F20*(1+$J$7),"") in H20. This adds the calculated VAT to the original amount.