• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Table that takes data when a condition occurs

pao13

Member
Hi. First post here. Hope to have a pleasant stay. Now to my problem.

I have attached my spreadsheet. I have some closing prices of a stock and have already created a formula that results 1 if the stock falls by 10% or more and 2 if the third Friday of every odd month happens first. This is column H. When the "event" occurs this price becomes the "initial price".

I would like to have a table (grey area) so that when event in column H occurs (1 o 2) the cells in the table to have the date, the price and the volatility of that event.

ie 17/01/2014 27.39 18.79
03/02/2014 24.41 29.01
21/03/2014 22.31 37.52

Any help would be greatly appreciated.
 

Attachments

  • RSX DATA15.xlsm
    64.1 KB · Views: 0
Last edited:
Thanks for that! This works.
The problem is that if I change column H for column G in the formula (condition: every month instead of every other month) the formula stops working. It also stops working when I add extra data in row 337 even though I change the formula for 337 instead of 336.

I attached the excel again.

Any clue about that?
 

Attachments

  • RSX DATA17.xlsm
    107.7 KB · Views: 0
Hi ,

There is no reason why the formula should stop working , unless you did not use CTRL SHIFT ENTER to enter the formula after changing it.

See the file.

Narayan
 

Attachments

  • RSX DATA17.xlsm
    115.2 KB · Views: 0
Hi. I'm not starting a new thread because the question is very similar and I'm the thread starter. I'm attaching my workbook again.

What I would like in column P of the "P_L events up to 2 months" sheet is a formula that populates the expiration dates of the "events exp up to 2 months" sheet. That is, when column U has 'expiration', column P in sheet "P_L events up to 2 months" to have the date of column R.

P5 21/02/2014
P6 18/04/2014
P7 20/06/2014
P8 15/08/2014
etc.

I modified your formula
IFERROR(INDEX('events exp up to 2 months'!S28:'events exp up to 2 months'!S369,SMALL(IF('events exp up to 2 months'!V28:'events exp up to 2 months'!V369="expiration",ROW('events exp up to 2 months'!V28:'events exp up to 2 months'!V369)-MIN(ROW('events exp up to 2 months'!V28:'events exp up to 2 months'!V369))+1),ROW('events exp up to 2 months'!S20))),"")


but it misses some dates.

Thanks for any help!
 

Attachments

  • RSX NEW test.xlsm
    457.7 KB · Views: 0
Back
Top