Results 1 to 7 of 7

20070719, 00:26 #1
 Join Date
 Mar 2001
 Location
 Dallas, Texas, USA
 Posts
 1,680
 Thanks
 0
 Thanked 1 Time in 1 Post
Formula for Complex Rent Calculations (XL2003)
Very difficult to explain, so I've attached a sample workbook.
I need a formula that will calculate a monthly rent based on the net sales of a business. To complicate matters, there are three methods for determining the rent. I've already placed a combo box with radio buttons to determine which rent method should be used. Fixed rent is simple, but the other two rental methods are "tier" rents. Standard Tier and Revised Tier.
As an example: "Standard Tier Monthly Rent" = 5% of the 1st $41,666.67 of net sales, plus 7.5% of the net sales in the range ($41,666.68  $62,500), plus 10% of the net sales over $62,500. So, if a store did $78,000 in net sales for the given month, that months rent would be: (5% * $41,666.67) + (7.5% * $20,833.33) + (10% * $15,500).
I'm having a terrible time composing a formula that will determine the rent amount. If the formula cannot be built in one cell (row 24), it's quite alright to use other rows for calculations (I can take care of the cosmetics of the worksheet). Any ideas? Ricky

20070719, 00:47 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula for Complex Rent Calculations (XL2003)
Based on Steve's formula in <post:=661,081>post 661,081</post:>, I devised the horrible formula
=IF(D17=1,D22,IF(D17=2,IF(D8<='Tier Rents'!$B$7,'Tier Rents'!$D$6*D8,IF(D8<='Tier Rents'!$B$8,'Tier Rents'!$D$6*'Tier Rents'!$B$7+(D8'Tier Rents'!$B$7)*'Tier Rents'!$D$7,'Tier Rents'!$D$6*'Tier Rents'!$B$7+'Tier Rents'!$D$7*('Tier Rents'!$B$8'Tier Rents'!$B$7)+'Tier Rents'!$D$8*(D8'Tier Rents'!$B$8))),IF(D17=3,IF(D8<='Tier Rents'!$B$15,'Tier Rents'!$D$14*D8,IF(D8<='Tier Rents'!$B$16,'Tier Rents'!$D$14*'Tier Rents'!$B$15+(D8'Tier Rents'!$B$15)*'Tier Rents'!$D$15,'Tier Rents'!$D$14*'Tier Rents'!$B$15+'Tier Rents'!$D$15*('Tier Rents'!$B$16'Tier Rents'!$B$15)+'Tier Rents'!$D$16*(D8'Tier Rents'!$B$16))),"")))
I hope it's correct! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
It could be streamlined, but it's late!
Note: I changed the rates to real percentages instead of text values.
See attached version.

20070719, 00:57 #3
 Join Date
 Mar 2001
 Location
 Dallas, Texas, USA
 Posts
 1,680
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula for Complex Rent Calculations (XL2003)
That's one ugly formula! <img src=/S/smile.gif border=0 alt=smile width=15 height=15> As long as it works...
Late? It's only 8PM
<img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Ricky

20070719, 00:59 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula for Complex Rent Calculations (XL2003)
Be sure to check it in several situations!

20070719, 02:12 #5
 Join Date
 Mar 2001
 Location
 Dallas, Texas, USA
 Posts
 1,680
 Thanks
 0
 Thanked 1 Time in 1 Post
Thanks
I tested it on a dozen different sales figures and it gave the correct answer every time. I added an additional IF statement to the front of the formula to take care of negative or text entries. Thanks again.
 Ricky

20070719, 12:04 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Thanks
If you add a calculated column to your "Tier Rent tables" to give the "Base amount", and name your 2 tables "Tier2" and "Tier3" you can use Lookups to get the values:
=IF(D17=1,D22,VLOOKUP(D8,INDIRECT("Tier"&D17),4)+( D8VLOOKUP(D8,INDIRECT("Tier"&D17),1))*VLOOKUP(D8,IND IRECT("Tier"&D17),3))
[See attached modification of Hans' workbook.]
Steve

20070720, 01:36 #7
 Join Date
 Mar 2001
 Location
 Dallas, Texas, USA
 Posts
 1,680
 Thanks
 0
 Thanked 1 Time in 1 Post
Thank you
Thanks a bunch Steve. Works great. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
 Ricky