Nested If Statements to calculate Bonus for Speed (# of Days) and % Above/Below Target Price

B

bsteelman

New Member
Joined
Mar 8, 2018
Messages
2
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:
[TABLE="width: 882"]
<tbody>[TR]
[TD="class: xl70, width: 14, bgcolor: transparent"][/TD]
[TD="class: xl82, width: 58, bgcolor: transparent, align: center"]A
[/TD]
[TD="class: xl82, width: 62, bgcolor: transparent, align: center"]B
[/TD]
[TD="class: xl82, width: 62, bgcolor: transparent, align: center"]C
[/TD]
[TD="class: xl82, width: 62, bgcolor: transparent, align: center"]D
[/TD]
[TD="class: xl82, width: 61, bgcolor: transparent, align: center"]E
[/TD]
[TD="class: xl82, width: 66, bgcolor: transparent, align: center"]F
[/TD]
[TD="class: xl82, width: 64, bgcolor: transparent, align: center"]G
[/TD]
[TD="class: xl82, width: 64, bgcolor: transparent, align: center"]H
[/TD]
[TD="class: xl82, width: 64, bgcolor: transparent, align: center"]I
[/TD]
[TD="class: xl82, width: 64, bgcolor: transparent, align: center"]J
[/TD]
[TD="class: xl82, width: 80, bgcolor: transparent, align: center"]K
[/TD]
[TD="class: xl82, width: 16, bgcolor: transparent, align: center"]L
[/TD]
[TD="class: xl82, width: 84, bgcolor: transparent, align: center"]M
[/TD]
[TD="class: xl82, width: 84, bgcolor: transparent, align: center"]N
[/TD]
[TD="class: xl82, width: 84, bgcolor: transparent, align: center"]O
[/TD]
[TD="class: xl83, width: 102, bgcolor: transparent, align: center"]P
[/TD]
[TD="class: xl82, width: 15, bgcolor: transparent"]Q
[/TD]
[TD="class: xl82, width: 70, bgcolor: transparent, align: center"]R
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: transparent, align: center"]1
[/TD]
[TD="class: xl71, bgcolor: transparent"]Property
[/TD]
[TD="class: xl72, width: 62, bgcolor: transparent, align: center"]Target
Price
[/TD]
[TD="class: xl72, width: 62, bgcolor: transparent, align: center"]Actual
Price
[/TD]
[TD="class: xl72, width: 62, bgcolor: transparent, align: center"]Sales Fee %
[/TD]
[TD="class: xl72, width: 61, bgcolor: transparent, align: center"]Sales Fee
[/TD]
[TD="class: xl66, width: 66, bgcolor: transparent, align: center"]Date 1
(Start)
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent, align: center"]Date2
(Finish)
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent, align: center"]Date 2 is > than 270d from Date1
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent, align: center"]Date2 is < 270d from Date1
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent, align: center"]Date2 is < 180d from Date1
[/TD]
[TD="class: xl72, width: 80, bgcolor: transparent, align: center"]Speed
Bonus
1
[/TD]
[TD="class: xl72, width: 16, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 84, bgcolor: transparent, align: center"]Actual Price is >15% +
Above/Below
Target Price
[/TD]
[TD="class: xl67, width: 84, bgcolor: transparent, align: center"]Actual Price is 10-15% +
Above/Below Target Price
[/TD]
[TD="class: xl67, width: 84, bgcolor: transparent, align: center"]Actual Price is 0-10% +
Above/Below
Target Price
[/TD]
[TD="class: xl72, width: 102, bgcolor: transparent, align: center"]Price Bonus(Penalty)
 2
[/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl72, width: 70, bgcolor: transparent, align: center"]Total Sales Fee
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: transparent, align: center"]2
[/TD]
[TD="class: xl71, bgcolor: transparent"]Site 1
[/TD]
[TD="class: xl73, bgcolor: transparent"]$70,000
[/TD]
[TD="class: xl74, bgcolor: #DDEBF7"]$90,000
[/TD]
[TD="class: xl75, bgcolor: transparent, align: right"]3.00%
[/TD]
[TD="class: xl76, bgcolor: transparent"]$2,700
[/TD]
[TD="class: xl68, bgcolor: transparent"]1/1/2018
[/TD]
[TD="class: xl65, bgcolor: #DDEBF7"]5/1/2018
[/TD]
[TD="class: xl69, bgcolor: transparent"]0.00%
[/TD]
[TD="class: xl69, bgcolor: transparent"]0.25%
[/TD]
[TD="class: xl69, bgcolor: transparent"]0.50%
[/TD]
[TD="class: xl77, width: 80, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "]$450
[/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]0.50%
[/TD]
[TD="class: xl69, bgcolor: transparent"]0.25%
[/TD]
[TD="class: xl69, bgcolor: transparent"]0.00%
[/TD]
[TD="class: xl79, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] "]$(450)
[/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"]$2,700
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: transparent, align: center"]3
[/TD]
[TD="class: xl71, bgcolor: transparent"]Site 2
[/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl81, width: 102, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: right"]28.57%
[/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: transparent, align: center"]4
[/TD]
[TD="class: xl71, bgcolor: transparent"]Site 3
[/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

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?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Cell K2:
=IF(DATEDIF(F2,G2,"D")<=180,C2*J2,IF(DATEDIF(F2,G2,"D")<=270,C2*I2,C2*H2))

Cell P2:
=IF(ABS((B2-C2)/C2)<=0.1,SIGN(B2-C2)*C2*O2,IF(ABS((B2-C2)/C2)<=0.15,SIGN(B2-C2)*C2*N2,SIGN(B2-C2)*C2*M2))
 
Upvote 0
Hi,

try this:


Book1
ABCDEFGHIJKLMNOP
1PropertyTarget PriceActual PriceSales Fee %Sales FeeDate 1 (start)Date2 (finish)Date 2 is > than 270d from Date1Date2 is < 270d from Date1Date2 is < 180d from Date1Speed Bonus 1Actual Price is >15% + Above/Below Target PriceActual Price is 10-15% + Above/Below Target PriceActual Price is 0-10% + Above/Below Target PricePrice Bonus(Penalty)Total Sales Fee
2Site 1$70.000,00$90.000,003,00%$ 2.700,001-1-20185-1-20180.00%0.25%0.50%$ 450,000.50%0.25%0.00%$ 450,00$2,700
3Site 2
4Site 3
Sheet1
Cell Formulas
RangeFormula
K2=LOOKUP(G2-F2,{0;180;270},{0.5;0.25;0})%*C2
O2=LOOKUP((C2/B2-1),{-1;-0.15;-0.1;0;0.1;0.15;1},{-0.5;-0.5;-0.25;0;0.25;0.5;0.5})%*C2
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
M
  • Question Question
Nested If Or Statement within If Or Statement?
Replies
3
Views
342
eduzs
E
M
  • Question Question
How to get the First date the Price was over or below the Average Price?
2
Replies
17
Views
944
Tetra201
Tetra201
J
  • Question Question
Nested Loops Needed to Import Data between 2 different workbooks - Brain Buster!
Replies
3
Views
1K
joshisms
J
C
  • Question Question
Is the following Process even Posible
Replies
0
Views
609
coxycoxycoxy
C

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top