I am trying to work on rent increases that are set in 3 tiers. the 2nd and 3rd tier are working great as they are very simple calculations. however on the 1st tier the increase is variable depending the variance between current rent and proposed rent. if current is higher than proposed the rent stays at current. however if current is less than proposed i want to set up cells that compare the variance and calculate the increase based on the amount of the variance. for example if current rent is $25 less than proposed rent i want to increase by X%, if it is $45 less then I want to increase by x% and so on. I want to set up a table that the x% uses to determine the amount of the increase so that each property can decide on their own percentages. My nested IF formula works if the current rent is more than proposed however if current rent is less than proposed it looks at the table but decreases the new rather than increases. I am sure this is just a simple oversight on my part but I can't seem to get it right. any suggestions would be appreciated. Thank you Wanda
formula problem on calculating increases
- Thread starter wnorrick
- Start date
First, let's create your percent lookup table. We need to start with the lowest possible value. Since there's a possibility of current being more than proposed, which will create negative values, we'll start with a very small number as a sort of "catch all".
[pre]
[/pre]
Everything beyond first row is up to you, change to whatever you need it to be.
Now, formula to calculate new rent should be:
=Current*(1+LOOKUP(Proposed-Current,LookupTable))
With this formula, if proposed is less than current, the lookup table will output a 0%, and formula just does Current*1 = Current. If proposed has increased, lookup table will give correct increase (say it's $$30, then output is 5%). This causes formula to be
Current*(1+5%) = Current*(1.05)
[pre]
Code:
Diff Percent Increase
-9E99 0%
$25 5%
$45 10%
$100 15%
...Everything beyond first row is up to you, change to whatever you need it to be.
Now, formula to calculate new rent should be:
=Current*(1+LOOKUP(Proposed-Current,LookupTable))
With this formula, if proposed is less than current, the lookup table will output a 0%, and formula just does Current*1 = Current. If proposed has increased, lookup table will give correct increase (say it's $$30, then output is 5%). This causes formula to be
Current*(1+5%) = Current*(1.05)
Okay, I guess I am worse off than I thought as I am still having trouble. i have a mini spreadsheet set up below with my columns that are affected. I am still making some kind of stupid mistake. can you do the formula for me so I can see where I am going wrong. thank you so much. the rows for data are 10-24.
[pre]
[/pre]
[pre]
Code:
col H col I col J col K col o Col P
LR New Var Tier 1 Diff %
535 535 0 535 -9.00E+99 0%
535 535 0 535 $25 5%
535 535 0 535 $45 10%
560 555 5 560 $100 15%
505 505 0 505
510 505 5 510
615 610 5 615
510 505 5 510
585 585 0 585
555 555 0 555
535 505 30 535
505 505 0 505
555 555 0 555
505 505 0 505
510 535 -25 510Assumptions:
LR is old price, New is new price.
Formula (in K10) to calculate Tier 1 value:
=H10*(1+LOOKUP(I10-H10,$O$10:$P$13))
Note that with this formula, the Var column is not needed, and is a little misleading in current format. E.g., your last line shows a negative variance, but if we want to use our new lookup table, this should be a positive variance. Perhaps this is what's causing problems?
LR is old price, New is new price.
Formula (in K10) to calculate Tier 1 value:
=H10*(1+LOOKUP(I10-H10,$O$10:$P$13))
Note that with this formula, the Var column is not needed, and is a little misleading in current format. E.g., your last line shows a negative variance, but if we want to use our new lookup table, this should be a positive variance. Perhaps this is what's causing problems?
Luke, as i work on the other properties the variances are different on each one. what would be the best way to make the lookup table a range so that I could set up one template for all of the properties. something like $20-$25, $26-$30, etc. you have been so much help and I really appreciate it. it looks like i need to study lookups this weekend.
From a visual standpoint, you could add a 2nd column to show the ranges the you indicate, but from a XL/math standpoint, it's not needed. Each number in the first column represents the cut-off point for a range. So, with the example we have, anything greater than or equal to -9x10^99 and less than $25 will evaluate to 0%. anything greater than or equal to $25 and less than $45 will evaluate to 5%. The convenience of this is that we don't have to write both sides of the "ranges", just the bottom cut-off points.