• 100% Satisfaction Guarantee

NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
53509759
Type Your Microsoft Office Question Here...
NewITZone is online now

# I need an excel formula to calculate the following: If

### Customer Question

I need an excel formula to calculate the following:
If revenue is less than market rent, then pay 66% of revenue as adjusted rent.
If revenue is greater than market rent, then pay 5% of the revenue less the market rent paid.
Not sure if this can be done on a single line since the first part calculates adjusted rent, but the second part would need to reference the adjusted rent to see if it exceeds the market rent.
Submitted: 5 months ago.
Category: Microsoft Office
Expert:  Jason Jones replied 5 months ago.

Hello,
I am the person that will be helping you today. May I take a look at the document in question so that I can put together the formula for you?

Please, be forewarned that the site is not secure:
- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"

Thank you,
Jason

Customer: replied 5 months ago.
I am unable to upload file to Wikisend as you request. Get error message, "We are sorry, but the requested file doesn't exist or has expired and is no longer available." Tried several times. Please advise.
Expert:  Jason Jones replied 5 months ago.

Customer: replied 5 months ago.
Line 12 in red is the problematic line.
Expert:  Jason Jones replied 5 months ago.

Thank you.

Expert:  Jason Jones replied 5 months ago.

Please, walk me through this calculation using cell references. IE: If C8 is bigger than C10, then you want this to happen. Etc, etc.

Customer: replied 5 months ago.
I C8<C11, then C12 should equal the %in \$B\$49 of c11 (this part is easy).
If C12>C11, then pay the % in \$B\$50 of C8, less the amount in C11.
Expert:  Jason Jones replied 5 months ago.

Part of the description is missing. So, if C8 < C11, then C12 equals \$B\$49 * C11.

Where does the next formula come in? Will that be for the same cell?

Is the next formula only done if C8 > C11 or is it done in addition to it?

Customer: replied 5 months ago.
The second part only applies if the result of C12 > C11.
Expert:  Jason Jones replied 5 months ago.

Okay. In which cell do you want this formula?

Customer: replied 5 months ago.
I we need another cell to calculate the desired result, we can use c13 which is blank or you can insert row beneath C12.
Expert:  Jason Jones replied 5 months ago.

Okay, I think I have it. Please, take a look at the document I attached. Does that work?

Customer: replied 5 months ago.
Not sure we are there. Ideally I want a single line to show the result I call Adjusted Rent to be Paid (Line 12). This would be carried across the sheet. If C8*\$B\$49 is less than C11, then show C8*\$B\$49. If C8*\$B\$49 is greater than or equal to C11, then show C8*\$B\$50 less C11.
Expert:  Jason Jones replied 5 months ago.

In your formulas, you use line 12. How exactly do you want the layout to be displayed?

Customer: replied 5 months ago.
As on my original document, Line 12 is a dollar amount to be paid based on the calculated variables we discussed.
Expert:  Jason Jones replied 5 months ago.

Okay, I placed the formulas in Line 13.

Customer: replied 5 months ago.
Ok, is there an updated document for me to review?
Expert:  Jason Jones replied 5 months ago.

The one I sent you has a LINE 13 in it. It is highlighted the same color as Line 12.

Customer: replied 5 months ago.
Based on the current values, calculations should produce the following results:
H12 should be \$11,596, which it is. (H8*\$B\$49).
I12 should be \$14, 207, which it is NOT (I8*\$B\$49).
Not sure we're on the same page.
Expert:  Jason Jones replied 5 months ago.

I am pretty sure we are not on the same page. How does H12 and I12 effect the values? You mentioned a few cells, but not those.

Expert:  Jason Jones replied 5 months ago.

This is the first time I have seen this document. Please, explain to me what the values 'should' be and how those values were obtained.

Customer: replied 5 months ago.
Everything we have discussed for column C is designed to carry across the sheet. I am looking to columns H and I because that is where I see the current calculations go wrong. If you review my initial descriptions, it is not hard to do these calculations manually. I'm just trying to create a formula to do them across the sheet.
Expert:  Jason Jones replied 5 months ago.

We are having trouble communicating and I apologize for that.

For this reason, I am opting out of the question and allowing another expert the chance to help.

The next expert will see all that is on this page, so there will be no need to repeat anything.

When another expert picks this up, you will be notified by email.