Hey all,
I'm working on a commission structure formula that's a flat-fee dollar amount rather than a percentage. Basically, I'm having trouble with the if/and statements.
You'd put in the sale price, the percentage the agent is taking (i.e. 2.5%), and then a fee field would calculate how much in fees the agent owes based on this structure:
0 - 249,999 = $395
250,000 - 499,999 = $595
500,000 -749,999 = $795
750,000 - 999,999 = $995
1,000,000 - 1,249,999 = $1195
1,250,000 - 1,499,999 = $1395
1,500,000 - 1,749,999 = $1595
1,750,000 - 1,999,999 = $1795
2,000,000 - 2,249,999 = $1995
2,250,000 - 2,499,999 = $2195
2,500,000 - 2,749,999 = $2395
I've got the IF statements down, but I'm having trouble adding in the AND as well as including the math for the dollar amount to be deducted from the total gross commission.
So it'd go as follows:
A1: Purchase Price ($350,000)
A2: Agent Commission Percentage (2.5%)
A3: Total Gross Commission ($8,750.00)
A4: Net Commission Minus Fee ($8750.00 - $595 fee = $8155)
Essentially, our admin should be able to put in purchase price and percentage and get the net commission amount to cut the check for. I just can't seem to get the AND statements down with the single dollar amount.
Any help or tips would be appreciated!
Bookmarks