Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Formula Help!

  1. #1
    Guest

    Default

    I'm trying to figure out a formula that will calculate the following problem: I want to split a certain bill between tenants based on their square footage, but some tenants need to pay double. For example:
    I have $200.00 to charge out to tenants A & B, based on the square footage of their leases. Tenant A has 75 square feet, Tenant B has 25 square feet. I can't just charge $2 a square foot; Tenant B needs to pay double what Tenant A pays. Can anyone help?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-27 06:01, Anonymous wrote:
    I'm trying to figure out a formula that will calculate the following problem: I want to split a certain bill between tenants based on their square footage, but some tenants need to pay double. For example:
    I have $200.00 to charge out to tenants A & B, based on the square footage of their leases. Tenant A has 75 square feet, Tenant B has 25 square feet. I can't just charge $2 a square foot; Tenant B needs to pay double what Tenant A pays. Can anyone help?
    how about
    =A2/3*2 For b
    and
    =+A2/3*1 For a?

    good luck

    [ This Message was edited by: IML on 2002-02-27 06:27 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-27 06:26, IML wrote:
    how about
    =A2/3*2 For b
    and
    =+A2/3*1 For a?

    good luck

    [ This Message was edited by: IML on 2002-02-27 06:27 ]
    Lets be somewhat nicer for the smaller tenant:

    In B1: =ROUND(A1/3/0.5,0)*0.5

    In B2: =A1-B1

    where A holds the bill.

  4. #4
    Guest

    Default

    Thanks, both of those work fine, but what about if I change the square footage-how can I build that into the formula?

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-27 06:57, Anonymous wrote:
    Thanks, both of those work fine, but what about if I change the square footage-how can I build that into the formula?
    What are the criteria for paying by square footage? And in what instances does one person payment become a multiplier of the other?

    A clear definition and some examples would help. I think it would also help if I could remember anything from algebra.

    If I read this again, I think maybe the larger tenant shouldn't pay double as you suggest, but 3 times the amount, since (s)he has three times the space. Maybe your after???

    =A1*(75/100)
    =A1*(25/100)



    [ This Message was edited by: IML on 2002-02-27 07:09 ]

  6. #6
    Guest

    Default

    I actually have a list of several tenants-some are restaurants, some are not. The restaurants are going to pay double the price per square foot than the non-restaurants. But if someone vacates, then I want to be able to take them out without it affecting the formula. Does that make sense?

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-27 06:57, Anonymous wrote:
    Thanks, both of those work fine, but what about if I change the square footage-how can I build that into the formula?
    How do you mean? Care to elaborate because your initial specs distinguish only between smaller and larger footages in relation to payment?


  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about you list a table such as
    {"tenant","true SF","multplier","adjusted";"A",75,1,75;"B",25,2,50;0,0,0,125}
    where tenant B is restaurant. And use the adjusted square foot to calculate.

    For example,
    tenant a would pay $120 (200*75/125)
    tenant b would be $80 (200*50/125).

    Are these results you would expect?


    On 2002-02-27 07:07, Anonymous wrote:
    I actually have a list of several tenants-some are restaurants, some are not. The restaurants are going to pay double the price per square foot than the non-restaurants. But if someone vacates, then I want to be able to take them out without it affecting the formula. Does that make sense?

  9. #9
    Guest

    Default

    The results are good-thanks. I've never worked with tables before, but I'm going to try to figure it out. I appreciate your help.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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