New to the forum (Hello!), please forgive me if I am posting my question(s) incorrectly. I have been working on this all day and have hit a wall.
The table that I am building is below:
<tbody>
</tbody>
What I am trying to do is write a formula for Cells K2 and P2 that provide a sales bonus for both Speed and Price respectively.
K2 - Speed Bonus formula that calculates the appropriate bonus based on time it takes to complete sale (Date 2- Date 1)
Example. Date 2 < 180d from Date 1 then (.50% * C2 = Bonus),
Date 2 > 180d, but < 270d, from Date 1 then (.25% * C2 = Bonus), and
Date 2 > 270d from Date 1 then (0.0% * C2 = Bonus)
P2 - Price Bonus formula that calculates a bonus or a penalty depending on % of "Actual Price" above/below the "Target Price"
Example:
If "Actual Price" is <10% Above/Below the "Target Price" than apply a 0% Bonus or Penalty (0.00% * "Actual Price").
If "Actual Price" is >10% Above or Below the "Target Price", but < 15% Above of Below the "Target Price", apply a +.25% Bonus/Penalty (+.25% * "Actual Price")
IF "Actual Price" is > 15% of the "Target Price", apply a +.50 Bonus or Penalty (+.50% * "Actual Price")
I am playing with nested if statements for both of these but have failed miserably all day.
Can anyone help me?
The table that I am building is below:
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |
| 1 | Property | Target Price | Actual Price | Sales Fee % | Sales Fee | Date 1 (Start) | Date2 (Finish) | Date 2 is > than 270d from Date1 | Date2 is < 270d from Date1 | Date2 is < 180d from Date1 | Speed Bonus 1 | Actual Price is >15% + Above/Below Target Price | Actual Price is 10-15% + Above/Below Target Price | Actual Price is 0-10% + Above/Below Target Price | Price Bonus(Penalty) 2 | Total Sales Fee | ||
| 2 | Site 1 | $70,000 | $90,000 | 3.00% | $2,700 | 1/1/2018 | 5/1/2018 | 0.00% | 0.25% | 0.50% | $450 | 0.50% | 0.25% | 0.00% | $(450) | $2,700 | ||
| 3 | Site 2 | 28.57% | ||||||||||||||||
| 4 | Site 3 |
<tbody>
</tbody>
What I am trying to do is write a formula for Cells K2 and P2 that provide a sales bonus for both Speed and Price respectively.
K2 - Speed Bonus formula that calculates the appropriate bonus based on time it takes to complete sale (Date 2- Date 1)
Example. Date 2 < 180d from Date 1 then (.50% * C2 = Bonus),
Date 2 > 180d, but < 270d, from Date 1 then (.25% * C2 = Bonus), and
Date 2 > 270d from Date 1 then (0.0% * C2 = Bonus)
P2 - Price Bonus formula that calculates a bonus or a penalty depending on % of "Actual Price" above/below the "Target Price"
Example:
If "Actual Price" is <10% Above/Below the "Target Price" than apply a 0% Bonus or Penalty (0.00% * "Actual Price").
If "Actual Price" is >10% Above or Below the "Target Price", but < 15% Above of Below the "Target Price", apply a +.25% Bonus/Penalty (+.25% * "Actual Price")
IF "Actual Price" is > 15% of the "Target Price", apply a +.50 Bonus or Penalty (+.50% * "Actual Price")
I am playing with nested if statements for both of these but have failed miserably all day.
Can anyone help me?
