Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function
you can use <= or =
"Nina" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function
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 "Nina" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function
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. "Nina" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |