Calculate Professional Tax by Gender and Salary in Excel
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.
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.
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.
Select cell C2 and enter =IF(A2="M",LOOKUP(B2,{0,7500,10000},{0,175,200}),LOOKUP(B2,{0,10000},{0,200})).
Press Enter, then drag the fill handle from C2 down column C to calculate tax for the remaining rows.
Compare a few values in column C with the stated brackets, especially salaries of 7,500 and 10,000.
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. Open the worksheet: Open WPS Spreadsheet and select your employee workbook, or create a new spreadsheet.
- 2. Prepare the columns: Enter M or F in column A and nonnegative numeric salaries in column B, starting on row 2.
- 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. 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.

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.




