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

Thread: Tell Excel to Add IF a $ amount or multiply and insert value

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all,

    I am a real estate broker that sometimes leases space that escalates each year at a percentage of rent, for example, 3% per year but sometimes the rent escalates each year at a certain dollar amount, for example $0.50 per year.

    Is there a formula that will allow me to simply enter either a $ amount or a % amount in a cell and have another cell automatically add the sum if it is a $ amount or multiply the sum if it is a percentage amount?

    We make a lot of errors in the office because we manually change the cells as needed. I would love to have a single sheet that let me automatically calculate both.

    Thanks for your help! I know I'll never be able to help anyone here, but I'm pretty handy around the office!

  2. #2
    Board Regular giacomo's Avatar
    Join Date
    Feb 2002
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If it's safe to assume your % increase is always less than 100% and your $ increase is always greater than $1 you could use the following:

    IF(B1<1,B1*A1,B1)+A1

    where your current rent is in A1 and your increase amount is in B1


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-27 10:34, chrislrob wrote:
    Hi all,

    I am a real estate broker that sometimes leases space that escalates each year at a percentage of rent, for example, 3% per year but sometimes the rent escalates each year at a certain dollar amount, for example $0.50 per year.

    Is there a formula that will allow me to simply enter either a $ amount or a % amount in a cell and have another cell automatically add the sum if it is a $ amount or multiply the sum if it is a percentage amount?

    We make a lot of errors in the office because we manually change the cells as needed. I would love to have a single sheet that let me automatically calculate both.

    Thanks for your help! I know I'll never be able to help anyone here, but I'm pretty handy around the office!
    If you were to have the current lease price in A2 and you were to use column B for $ increase and column C for percentage increase, the formula below does the math using the value in either B or C as long as one is empty.

    =IF(ISBLANK(B2),A2+(A2*C2),A2+B2)

    Make sense?


    Regards,

    Gary Hewitt-Long

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks again, Gary!

    I put both formulas on the chart and then I'll tell everyone to "hide" whichever one they don't use. We give the sheets out to clients.

    I look SO smart. I hope Mr. Excel will forgive me when I don't tell anyone else in the office about this site! At last, I'm irreplaceable! Bwahahahahahahaha!

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Giacomo,

    I forgot to say that both the percentage and the dollar amount are usually less than 1.

    Thanks for 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
  •