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

[chrislrob]

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!

 

[giacomo]

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

 

[gplhl]

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?

 

[chrislrob]

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!

 

[chrislrob]

Giacomo,

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

Thanks for your help.