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

chrislrob

New Member
Joined
Feb 26, 2002
Messages
13
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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
Giacomo,

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

Thanks for your help.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top