Results 1 to 7 of 7
  1. #1
    Silver Lounger
    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

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

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

  4. #4
    Plutonium Lounger
    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!

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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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)+( D8-VLOOKUP(D8,INDIRECT("Tier"&D17),1))*VLOOKUP(D8,IND IRECT("Tier"&D17),3))

    [See attached modification of Hans' workbook.]

    Steve

  7. #7
    Silver Lounger
    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

Posting Permissions

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