If Function with Drop Down List

Queue

New member
Joined
Mar 1, 2020
Messages
3
Reaction score
0
Points
0
Excel Version(s)
Mac 2016
I am trying to do a calculation with the IF function that will allow for a commission calculation to take place depending on the transaction type. Here in BC, I am a real estate agent and want to have a calculation based on who is representing whom. For example, the agent representing the buyer will receive the below commission
Commission for the first $100,000 to be paid 3.25% and the balance to paid at 1.15% (Buying Agent)
Commission for the first $100,00 to be paid 3.78% and the balance to be paid at 1.35% (Selling Agent).
If I am representing John Doe and use the drop down list and choose "Buying" then calculate the commission based on the selling price (eg $450,000) of the property and the above percentage levels. Then the same for choosing "Seller" from the drop down list and do the above criteria.
 
1.Assume A1 is original selling price (variable)
2. B1 is the cell to choose agent type (Selling or Buying) by uisng dropdown list
select B1,data,data validaton,setting,list, then enter "Buying,Selling" in "source", ok to save and exit.

3. D1=Min(100000,A1)*IF(B1="Buying",0.0325,0.0378)+MAX(0,A1-100000)*IF(B1="Buying",0.0115,0.0135)
NOTE: the reason to use "MAX" and "MIN" in formual is to avoid selling price is less than 10K. I know it is impossible in BC but this is something must be considered in formula structure
 
Last edited:
Back
Top