„Excel“ formulė: Mokesčio tarifo apskaičiavimas su fiksuota baze

Turinys

Bendroji formulė

=IF(A1

Summary

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

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

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%

Explanation

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

=IF(B5

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)

Įdomios straipsniai...