Help with IF function

[Nina]

I am attempting to calculate a percentage rent fee based on the total Net
Operating Income (NOI).

The Net Operating Income (NOI) has a minimum threshold of $5 Million.

The percentage rent fee calculation has the following parameters:

If the NOI is between $5,000,000-$5,500,000, then the percentage rent fee is
17.5% of $500,000.

If the NOI is between $5,500,000 -$6,000,000, then the percentage rent fee
is 22.5% of $500,000.

If the NOI is between $6,000,001-$6,500,000, then the percentage rent fee is
25.0% of $500,000.

If the NOI is between $6,500,001-$7,000,000, then the percentage rent fee is
28.0% of $500,000.

If the NOI is between $7,000,001-$7,500,000, then the percentage rent fee is
30.0% of $500,000.

If the NOI is greater than $7,500,001, then the percentage rent fee is 17.5%
of the NOI minus $7,500,000.

I attempted to set up the calculation using the IF function, but ran into
problems calculating the percentage rent fee if the NOI equaled $5,500,000,
$6,000,000, $6,500,000, $7,000,000 or $7,500,000. This is because these
values are the maximum amounts in each of the parameters listed above and
because the IF function uses < or > and not = to.

Is there a different function I could use to solve this problem?

 

[Gaurav]

you can use <= or >=

 

[Mike H]

Hi,

If I've understood correctly build a table like this which in the case of
this example is in A1 - B5 but can be anywhere.

5000000 17.50%
6000001 22.50%
6500001 25.00%
7000001 30.00%
7500001 17.50%

The with your NOI in c1 try the formula
=C1*VLOOKUP(C1,A1:B5,2,TRUE)

Mike

 

[Dave]

Hi Nina,
The IF function can use >, <, =, >=, <=, <>.
You could probably use the <= (less than or equal to) operator combination
in your formula to make it work.

Regards - Dave.