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

Lookup and Reference Examples

  • Multi-criteria lookup and transpose in Excel
  • Count unique text values with criteria
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • MATCH function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • How to combine 2 or more chart types in a single chart in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • Working With Tables in Excel
  • Example of COUNTIFS with variable table column in Excel
  • Add Outline to Data in Excel

Data Validation Examples

  • Excel Data validation no punctuation
  • Excel Data validation exists in list
  • Data validation must not exist in list
  • Excel Data validation allow weekday only
  • Excel Data validation require unique number

Tax rate calculation with fixed base in Excel

by

This tutorial shows how to work Tax rate calculation with fixed base in Excel using the example below;

Formula

=IF(A1<limit,A1*rate,(A1-limit)*rate+fixed)

Explanation

This example shows how to set up simple formula using the IF function to calculate a tax amount with both fixed and variable components. In the example shown, the formula in C5 is:

=IF(B5<limit,B5*rate,(B5-limit)*rate+fixed)

in a worksheet with the following named ranges: rate = F4, fixed = F5, limit = F6.

Worked Example:   Not Equal To '<>' operator in Excel

The rules of this problem are as follows:

  1. If amount is less than $1000, tax is amount * 20%.
  2. If amount is greater than or equal to $1000, tax is $200 + (amount over 1000) *20%
Worked Example:   Win loss points calculation in Excel

How this formula works

The core of this formula is a single IF statement that checks the amount in column B against the base limit:

=IF(B5<limit

If TRUE, the formula simply multiplies the amount in B5 by tax rate:

B5*rate

If FALSE, the formula applies the tax rate to the amount over 1000, then adds the fixed amount:

(B5-limit)*rate+fixed)
Worked Example:   How to create Checklist in Excel

Post navigation

Previous Post:

Return blank if in Excel

Next Post:

Categorize text with keywords 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
  • Remove last characters from right in a cell in Excel
  • How to count number of characters of text in a cell in Excel
  • CLEAN function: Description, Usage, Syntax, Examples and Explanation
  • How to count specific words in a cell in Excel
  • How to add a line break with a formula while joining text strings in Excel
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • Dynamic date list in Excel
  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation
  • Count birthdays by month in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • ODDLPRICE function: Description, Usage, Syntax, Examples and Explanation
  • ISPMT function: Description, Usage, Syntax, Examples and Explanation
  • Future value vs. Present value examples in Excel
  • SLN function: Description, Usage, Syntax, Examples and Explanation
  • DDB function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles