How to use date to adjust results

H

honkin

Active Member
Joined
Mar 20, 2012
Messages
385
Office Version
  1. 2016
Platform
  1. MacOS
I have a sheet which records results and the P&L from those results. I am wanting to trial something; using time-weighting to skew the results, but not sure how to do this.

Column A has the date and what I am wanting to do is have the P&L calculation altered depending on the date. This is how the standard calculation looks at the moment

Code: 
=IF(AND(I2>1,G2<=20),-100,IF(AND(I2=1,G2<=20),(G2-1)*98,0))


It checks the finish position (I) and the SP - Starting Price (G) and if the finish position is 1 and the SP is <=20, the the calculation is performed. That all works fine, but I am wanting to now bring the date into play, but unsure how to do it. I assume it will require an additional column which will look at the Profit column (J) and apply the various % based on the distance from today's date.

So I want to work in 6 month blocks. If the date is within 6 months, then the P&L remains 100% of what it was, so no code needed there at all.
If the date is over 6 months and up to 12 months, I want the P&L to only be 50%.
If the date is over 12 months and up to 18 months, I want the P&L to only be 25%
If the date is over 18 months and up to 24 months, I want the P&L to only be 12.5%
If the date is over 24 months and up to 30 months, I want the P&L to only be 6.25%
If the date is over 30 months and up to 36 months, I want the P&L to only be 3.125%

Does that make sense at all? The P&L gets reduced depending on how far from today's date it is.

Is this at all possible?

I have played around with using this formula just to find out if the current date is within 6 months. It's not anywhere near the final code, but a start

Code: 
=AND(A2>EOMONTH(TODAY(),-(6+1)),A2<=EOMONTH(TODAY(),-1))


It gives a simple TRUE or FALSE, response. It seems to work in that respect, which leads me to believe I am sort of on the right track, but I don't know how to structure this with the above percentages to adjust the profit in column J.

cheers
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
What do you want to happen if it goes over 36 months ?
I suspect that using a lookup table would be better but since you are using even 6 month intervals this might work for you.
Excel Formula: 
=IF(AND(I2>1,G2<=20),-100,IF(AND(I2=1,G2<=20),(G2-1)*98,0))
  * IF(DATEDIF(A2,EOMONTH(TODAY(),-1),"m")<=6,100%,3.125%*2^(6-ROUNDUP(DATEDIF(A2,EOMONTH(TODAY(),-1),"m")/6-1,0)))
 
Upvote 0
Alex Blakenburg said:
What do you want to happen if it goes over 36 months ?
I suspect that using a lookup table would be better but since you are using even 6 month intervals this might work for you.
Excel Formula: 
=IF(AND(I2>1,G2<=20),-100,IF(AND(I2=1,G2<=20),(G2-1)*98,0))
  * IF(DATEDIF(A2,EOMONTH(TODAY(),-1),"m")<=6,100%,3.125%*2^(6-ROUNDUP(DATEDIF(A2,EOMONTH(TODAY(),-1),"m")/6-1,0)))
Click to expand...
Apologies for the delay Alex and thanks for the reply.

I ended shooting myself in the foot on this one and confusing the issue, as the calculation of the P&L is already done, so I don't really want to double up on actually calculating it, as some of the models in other sheets don't use things like a maximum as in G (<Gx). I really am looking at an additional column which will simply adjust the P&L based on date. The P&L lives in various columns depending if it backing for a win, backing for a place, or even laying either of those options, but as an example, AD2 is Win P&L. I removed all the P&L calculation from your example and replaced it with the column reference which has the P&L (AD)

So I tried AD2
Code: 
=AD2*IF(DATEDIF(A2,EOMONTH(TODAY(),-1),"m")<=6,100%,3.125%*2^(6-ROUNDUP(DATEDIF(A2,EOMONTH(TODAY(),-1),"m")/6-1,0)))

This unfortunately just shows the value in AD2. The date is in A and is in this format dd/mm/yyy

I'm sure I will have broken your code by simply removing the initial calculation part

cheers
 
Upvote 0
I don't understand what you mean. In the below I have just entered a figure in AD and put the formula you have in K2, its definitely not just giving me AD.
If you want to give me an XL2BB sample I can have another look.

PS: Ignore my formula in A2 it was just to give me a test date

20230311 Percentage past months honkin.xlsx
ABJKLABACAD
1P&L DateFormulaCol AD
231-Dec-191.562550
20230415 New
Cell Formulas
RangeFormula
A2A2=EOMONTH(TODAY(),-40)
K2K2=AD2*IF(DATEDIF(A2,EOMONTH(TODAY(),-1),"m")<=6,100%,3.125%*2^(6-ROUNDUP(DATEDIF(A2,EOMONTH(TODAY(),-1),"m")/6-1,0)))
 
Upvote 0
Alex Blakenburg said:
I don't understand what you mean. In the below I have just entered a figure in AD and put the formula you have in K2, its definitely not just giving me AD.
If you want to give me an XL2BB sample I can have another look.

PS: Ignore my formula in A2 it was just to give me a test date

20230311 Percentage past months honkin.xlsx
ABJKLABACAD
1P&L DateFormulaCol AD
231-Dec-191.562550
20230415 New
Cell Formulas
RangeFormula
A2A2=EOMONTH(TODAY(),-40)
K2K2=AD2*IF(DATEDIF(A2,EOMONTH(TODAY(),-1),"m")<=6,100%,3.125%*2^(6-ROUNDUP(DATEDIF(A2,EOMONTH(TODAY(),-1),"m")/6-1,0)))
Click to expand...
cheers Alex

You're correct. My mistake, it was doing that as the date was January this year, so of course it stays at 100% as it is under 6 months.

It certainly does seem to calculate, but it is not accurate just yet. When I changed the date to 01/01/2021 (1 January 2021), the actual profit was 21.56, but the revised profit was 2.695, which is 12.5% of the original. 1 Jan 2021 is just over 27 months ago, so the correct reply should be 1.348, which is 6.25% of 21.56.

So as per the original post

Over 6 months and up to 12 months = 50%
Over 12 months and up to 18 months = 25%
Over 18 months and up to 24 months = 12.5%
Over 24 months and up to 30 months = 6.25%
Over 30 months and up to 36 months = 3.125%

For whatever reason, it lumped a date which was just over 27 months ago, into the 18 to 24 category

When I changed the date to 01/07/2020, the result was also incorrect. It was 1.348, which is 6.25% of 21.56, but that date is 33 months ago, so should have been calculated at 3.125%.

I hope that helps. It is working, but not accurately.
 
Upvote 0
I'm afraid that got pretty ugly.
There are 3 options here, give them a try and see which you prefer.

20230311 Percentage past months honkin.xlsx
ABJKLABACADAE
1P&L DateFormulaCol AD
21/07/20203.1251Option 1
31/07/20203.1251Option 2
20230415 New
Cell Formulas
RangeFormula
A3A3=A2
K2K2=AD2*IF(DATEDIF(A2,TODAY(),"m")>36, 0, 100 / (2^ (ROUNDUP( ( DATEDIF(A2,TODAY(),"m") + IF(EDATE($A$17,-DATEDIF(A2,TODAY(),"m"))=A2,0,1)) /6, 0) -1)) )
K3K3=AD3*IFERROR(CHOOSE( ROUNDUP( ( DATEDIF(A3,TODAY(),"m") + IF(EDATE($A$17,-DATEDIF(A3,TODAY(),"m"))=A3,0,1) ) / 6, 0), 100,50,25,12.5,6.25,3.125),0 )
 
Upvote 0
Solution
Alex Blakenburg said:
I'm afraid that got pretty ugly.
There are 3 options here, give them a try and see which you prefer.

20230311 Percentage past months honkin.xlsx
ABJKLABACADAE
1P&L DateFormulaCol AD
21/07/20203.1251Option 1
31/07/20203.1251Option 2
20230415 New
Cell Formulas
RangeFormula
A3A3=A2
K2K2=AD2*IF(DATEDIF(A2,TODAY(),"m")>36, 0, 100 / (2^ (ROUNDUP( ( DATEDIF(A2,TODAY(),"m") + IF(EDATE($A$17,-DATEDIF(A2,TODAY(),"m"))=A2,0,1)) /6, 0) -1)) )
K3K3=AD3*IFERROR(CHOOSE( ROUNDUP( ( DATEDIF(A3,TODAY(),"m") + IF(EDATE($A$17,-DATEDIF(A3,TODAY(),"m"))=A3,0,1) ) / 6, 0), 100,50,25,12.5,6.25,3.125),0 )
Click to expand...
Cheers Alex

Thanks so much. Yes....it was getting that way.

Option 1 merely puts the date from from column A, so not an option

Option 3, seems to not actually do any calculations at all. It merely puts an amount of 100, 50, 25, 12.5, 6.25 or 3.125, so it appears to simply give the % value rather than any P&L. It's of no use in this instance.

Option 2 seems to calculate everything correctly based on the varying percentages, but what it does is to shift everything 2 decimal places to the right. 21.56 becomes 2156 and so on. No real issue; I just added /100 to the end of the formula which brings it all back into line.

That seems to work well, Alex, so thank you very much for your effort. It is much appreciated

cheers
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
date formula in conditional formatting not behaving as expected
Replies
2
Views
224
bh24524
B
S
  • Solved
When Filter is applied, Excel jumps down multiple rows to (Blanks). How can Filter ignore blanks?
Replies
3
Views
284
Peter_SSs
Peter_SSs
A
  • Solved
lookupifs?? lookup value so long as date value is greater than specified date
Replies
5
Views
103
Peter_SSs
Peter_SSs
Nlhicks
  • Question Question
Conditional Format to turn Red if 5 yrs out, Yellow if 4 yrs out and green otherwise
2
Replies
13
Views
326
etaf
etaf
J
  • Solved
Using Month query inside sumif formula
Replies
6
Views
665
Jpull
J

Forum statistics

Threads
1,223,886
Messages
6,175,195
Members
452,616
Latest member
intern444

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