excel formula for real estate commissions

[jfilipczak]

Okay, I am trying to set up a sheet that calculates commissions. We get paid this way 3% of the 1st $100,000 and 2% of the remainder. That is an easy calculation, but I want the same cells to offer an option if the sale price is under $100,000 so everything is being read from the same cells.

For instance we sell something for $45,000 and we get 2.5% commission.

Right now I have it set up that I put the purchase price in and the next two cells break up the price, first cell is $100,000 and the next is whatever is the purchase price minus 100,000. Then the first cell multiplies it by 3% and the second cell muliplies the remainder by 2%, but if the commission is under $100,000 then what do I do?

Does this make sense?

 

[shg]

We get paid this way 3% of the 1st $100,000 and 2% of the remainder.

For instance we sell something for $45,000 and we get 2.5% commission.

I can't reconcile those two statements. Can you clarify?

 

[Aladin Akyurek]

Are you saying that:

When A2 = 60,000, B2 should read: 1,500 by the 2.5% rule;

When A2 = 120,000, B2 should read: 3,400 by the 3% rule for 100,000 and by the 2% rule for 120,000-100,000?

 

[jfilipczak]

Are you saying that:

When A2 = 60,000, B2 should read: 1,500 by the 2.5% rule;

When A2 = 120,000, B2 should read: 3,400 by the 3% rule for 100,000 and by the 2% rule for 120,000-100,000?

Yes that is exactly it.

 

[Aladin Akyurek]

Yes that is exactly it.

In B2 enter:

=(A2 < 100000)*A2*2.5%+IF(A2>=100000,100000*3%+(A2-100000)*2%)

 

[jfilipczak]

In B2 enter:

=(A2 < 100000)*A2*2.5%+IF(A2>=100000,100000*3%+(A2-100000)*2%)

Thank you. That worked perfectly. That's for understanding my garble as well.

 

[Aladin Akyurek]

Thank you. That worked perfectly. That's for understanding my garble as well.

Great. Thanks for providing feedback.