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

Lookup and Reference Examples

  • How to get last row in text data in Excel
  • How to get first row number in range in Excel
  • Perform case-sensitive Lookup in Excel
  • How to reference named range different sheet in Excel
  • Vlookup Examples in Excel

Data Analysis Examples

  • Managing Conditional Formatting Rules in Excel
  • Number and Text Filters Examples in Excel
  • Understanding Pivot Tables in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS

Data Validation Examples

  • Excel Data validation with conditional list
  • Excel Data validation allow uppercase only
  • Excel Data validation date in next 30 days
  • Excel Data validation specific characters only
  • Excel Data validation must contain specific text

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:   Multiple chained VLOOKUPs 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 with numbers and text 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 create email address from name in Excel
  • LEFT, LEFTB functions: Description, Usage, Syntax, Examples and Explanation
  • NUMBERVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Remove line breaks in a cell in Excel
  • Get position of 2nd 3rd and more instance of character in Excel
  • Get last weekday in month in Excel
  • How to get same date next year or previous year in Excel
  • Convert Unix time stamp to Excel date
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • Get first day of month in Excel
  • TBILLEQ function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate Net Present Value (NPV) in Excel
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
  • SLN function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles