Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get relative row numbers in a range in Excel
  • To count total rows in a range in Excel
  • Offset in Excel
  • Lookup entire row in Excel
  • Two-way lookup with VLOOKUP in Excel

Data Analysis Examples

  • Get column index in Excel Table
  • How to add Trendline to a chart in Excel
  • Subtotal function in Excel
  • How to Create Gantt Chart in Excel
  • How to Create One and Two Variable Data Tables in Excel

Data Validation Examples

  • Excel Data validation only dates between
  • Excel Data validation must contain specific text
  • Excel Data validation no punctuation
  • Excel Data validation number multiple 100
  • Excel Data validation unique values only

How to Calculate Tax Rates in Excel

by

This example teaches you how to calculate the tax on an income using the VLOOKUP function in Excel. The following tax rates apply to individuals who are residents of Australia.

Taxable income Tax on this income
0 – $18,200 Nil
$18,201 – $37,000 19c for each $1 over $18,200
$37,001 – $87,000 $3,572 plus 32.5c for each $1 over $37,000
$87,001 – $180,000 $19,822 plus 37c for each $1 over $87,000
$180,001 and over $54,232 plus 45c for each $1 over $180,000
Worked Example:   Basic Tax Rate calculation with VLOOKUP in Excel

Example: if income is 39000, tax equals 3572 + 0.325 * (39000 – 37000) = 3572 + 650 = $4222

To automatically calculate the tax on an income, execute the following steps.

1. On the second sheet, create the following range and name it Rates.

2. When you set the fourth argument of the VLOOKUP function to TRUE, the VLOOKUP function returns an exact match or if not found, it returns the largest value smaller than lookup_value (A2). That’s exactly what we want!

Worked Example:   Group arbitrary text values in Excel

Explanation: Excel cannot find 39000 in the first column of Rates. However, it can find 37000 (the largest value smaller than 39000). As a result, it returns 3572 (col_index_num, the third argument, is set to 2).

Worked Example:   VLOOKUP from another sheet in Excel

3. Now, what’s left is the remainder of the equation, + 0.325 * (39000 – 37000). This is easy. We can return 0.325 by setting col_index_num to 3 and return 37000 by setting col_index_num to 1. The complete formula below does the trick.

Note: when you set the fourth argument of the VLOOKUP function to TRUE, the first column of the table must be sorted in ascending order.

Post navigation

Previous Post:

How to calculate Day of the Year in Excel

Next Post:

How to Break Worksheets into Pages in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to extract name from email address in Excel
  • How to Capitalize first letter in a sentence in Excel
  • How to extract text between parentheses in Excel
  • How to extract multiple lines from a cell in Excel
  • T function: Description, Usage, Syntax, Examples and Explanation
  • How to get Weekdays, Working days between Two Dates in Excel
  • Convert date to text in Excel
  • How to get workdays between dates in Excel
  • Count dates in current month in Excel
  • Create date range from two dates in Excel
  • YIELDMAT function: Description, Usage, Syntax, Examples and Explanation
  • How to Calculate Tax Rates in Excel
  • Calculate interest for given period in Excel
  • Example of payment for annuity in Excel
  • PRICEMAT function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles