Formula Errors

Calculate Professional Tax by Gender and Salary in Excel

Rana GarciaRana Garcia Nov 22, 2026 868 views

Question details

Calculate professional tax using different salary thresholds and tax amounts for male and female employees.

Product
Microsoft Excel
Device & OS
not provided
Scenario
An employee worksheet contains gender and salary values that determine the professional tax amount.
Observed behavior
The worksheet should return the appropriate tax for each employee's gender and salary.
Before you start

Confirm that column A contains M or F, column B contains nonnegative numeric salaries, and column C is available for the result. Verify that the example thresholds and tax amounts match the rules applicable to your payroll.

Solution 1Recommended

Use IF and LOOKUP to calculate professional tax

Use this formula when gender is in A2 and salary is in B2, with the example tax brackets below.

For M, the formula returns 0 below 7,500, 175 from 7,500 to below 10,000, and 200 at 10,000 or above. For F, it returns 0 below 10,000 and 200 at 10,000 or above.

1
Enter the formula

Select cell C2 and enter =IF(A2="M",LOOKUP(B2,{0,7500,10000},{0,175,200}),LOOKUP(B2,{0,10000},{0,200})).

2
Apply it to employees

Press Enter, then drag the fill handle from C2 down column C to calculate tax for the remaining rows.

3
Check the results

Compare a few values in column C with the stated brackets, especially salaries of 7,500 and 10,000.

Check gender entries: The formula treats any value other than M as the female branch. Check column A for blanks or invalid entries before using the results.
Calculate tax in WPS Spreadsheet

Calculate professional tax in WPS Spreadsheet

Use the same IF and LOOKUP formula in WPS Spreadsheet to calculate tax from employee gender and salary.

  1. 1. Open the worksheet: Open WPS Spreadsheet and select your employee workbook, or create a new spreadsheet.
  2. 2. Prepare the columns: Enter M or F in column A and nonnegative numeric salaries in column B, starting on row 2.
  3. 3. Calculate the tax: Select C2, enter =IF(A2="M",LOOKUP(B2,{0,7500,10000},{0,175,200}),LOOKUP(B2,{0,10000},{0,200})), and press Enter.
  4. 4. Fill and save: Drag the fill handle down column C, check the results against your tax rules, and use File > Save or Save As to save the workbook.
Apply separate salary brackets by genderOpen and save Microsoft Excel XLSX filesCopy the formula down a payroll worksheetUse familiar spreadsheet cells and formulas
microsoft office alternative - wps office

Frequently Asked Questions

What happens when a salary is exactly 7,500 or 10,000?

For M, a salary of 7,500 returns 175 and 10,000 returns 200. For F, a salary of 10,000 returns 200.

Can I change the salary thresholds and tax amounts?

Yes. Edit the threshold and result arrays in the formula in C2, keeping each threshold paired with its corresponding tax amount and the thresholds in ascending order.

Why does a blank or unexpected gender return the female tax amount?

The IF test checks only whether A2 equals M; every other value uses the female branch. Check column A for F, blanks, and invalid entries.

Can I use this formula if my salary or gender is in another column?

Yes. Change A2 to the gender cell and B2 to the salary cell in the formula, then copy the formula down from the result cell.