# Thread: Formula for Complex Rent Calculations (XL2003)

1. ## 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?

2. ## 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.

3. ## 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>

4. ## Re: Formula for Complex Rent Calculations (XL2003)

Be sure to check it in several situations!

5. ## 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.

6. ## 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)+( D8-VLOOKUP(D8,INDIRECT("Tier"&D17),1))*VLOOKUP(D8,IND IRECT("Tier"&D17),3))

[See attached modification of Hans' workbook.]

Steve

7. ## Thank you

Thanks a bunch Steve. Works great. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•