Dynamic Formula Help

[floriandotti]

Hello,

I am trying to make a dynamic chart that will update when I put 2 columns of information in: Portfolio and Benchmark returns. I have everything worked out except one area, and it has to do with a date.

I have maybe 100 different portfolio paired returns, so I made one master worksheet that I can just enter the returns and everything is calculated, but the problem is that the inception dates differ per portfolio.

I can adjust the start day easily, but I am having trouble with some scripting.

=IF($A185>=DATEVALUE("30/04/2011"),STDEV($F174:$F185)*SQRT(12),"")

This is what I've come up with so far, but the issue is that in this column, the $F174 must stay static within each portfolio, but it differs between all portfolios.

My questions is whether I can make the location of cell "$f174" dependent on the FIRST cell that is >=DATEVALUE, and from anything after to remain static?

Thank you for your time.

 

[Gerald Higgins]

Sorry for the slow reply f, I haven't been able to give as much time to this as I would like.

In response to post #10, I haven't tested this in the context of your problem, but in principle, yes, you can do that.

OFFSET() will work with negative values, for the row and column arguments (though obviously not for the height and width arguments).

 

[floriandotti]

it doesn't work in my example because it's not rolling. It just keeps the distance between each new cell the same, but it's not a dynamic location if that makes any sense.

 

[Gerald Higgins]

Sorry, I don't know what you mean, can you explain in more detail please ?

 

[floriandotti]

I mean that instead of making a dynamic range, for instance, one that would go from $F$(Whatever the start date is):$F(Whatever row is being computed)

it does a set distance of whatever that row is from each row.

so for instance, if the row with the date is 100, then 100 is my lookup value. That is going to be the row with the date of inception of the program, and it would work like this : (which is wrong)

107 - Stdev($F107:F207)
108 - Stdev($F108:F208)
...
200 - Stdev($F200:F300)

I need it to work like this:

107 - Stdev($F$100:$F107)
108 - Stdev($F$100:$F108)
...
200 - Stdev($F$100:$F200)

where $F$100 will change based on different start dates, so if the data goes back to 1992, the Inception date will be around row 240, because of monthly data, where if the returns data goes only back to 1996, the inception date will be around row 170...

So in summary, the row which holds the inception date will vary between portfolios, and I need an offset() or index() code that will pull that number (which is dynamically computed each time in cell $BJ$9) and use the value in that cell to determine the start date of column $F in the Stdev() formula.

Does that make sense? Thank you so much for your persistence.

 

[floriandotti]

Also,

I don't know if you know anything about Excel Graphs, but I'm trying to make line graphs of Run-up and Drawdown, and I have a series of data which goes like:

4%
5%
6%
5%
4.2%
NA
NA
NA
9%
... so on, so there will be NA()'s when the series is not in a drawdown or run-up (respectively), but when I try to graph the series, I have 3 of them: Excess, Manager, and Market returns.

I would LIKE Excel to not connect the dots. I want a broken graph, but no matter what I do, Excell connects the series. If it is NA(), then Excel interpolates, and I have a straight line between previous value and future value, and if it is 0% or TEXT in place of NA(), Excel graphs the drop lines to 0, so it still is not working visually. Is there a way to make a cell clear it's contents instead of "" or NA() or 0?

for instance, here is my drawdown formula that I made:

=IF(MAX($D$7:$D9)>MAX($D$7:$D8),NA(),-(MAX($D$7:$D9)-D9))

I would like to substitue "DELETE CELL VALUE/FORMULA" for NA().

The issue is that this is a template for many, many managers, so I cannot manually go in and delete values for each manager, and every time new returns are recorded. I need it to update automatically. All the data is linked to charts and tables too...

Thanks

 

[Gerald Higgins]

Let's deal with one problem at a time

The question you have asked in post #15 is asked fairly often on the board.
I know what you mean, and I can't actually remember what the answer is, but you should be able to find an answer on the board, maybe using the search functions.

Going back to post #14, I think I know what you mean, and I think we can do this, maybe with a combination of OFFSET, INDEX, and MATCH.
BUT, I'm still not completely clear about the layout of your file.

What is in BJ9 ?
Is it a year ?
Or another cell address ?
Or a row number ?

 

[floriandotti]

What we know:

$BJ$9 is =MATCH(Summary!"Inception Date",A:A,0), so it takes whatever date the program started and matches a row number. I have monthly data starting on B7, going through Bx. Column A is full of dates, like 28/02/2008.

What is important is that for each portfolio, I have data that goes back to different dates. For some it's 1996, for others it's 2000, and for a few it's 2004, etc. Each manager portfolio starts somewhere, and from that, there are different program start dates.

What I need is to create a dynamic, updating range. I have 5 columns, amongst maybe 100 other columns of data, but these 5 are the only ones that I haven't figured out, and that's because the program inception date differs between portfolios, AND the column A differs between portfolios too.

Since my return data starts at row 7, I need a range that goes from $F7:$F$INCEPTIONDATE's_ROW. The inception date row will remain static, but the 7 will roll when I drop it, so it will go $F$INC:$F7, $F8, ... , $F200, etc.

The number that INC needs to be is found in BJ9, but since it needs to be a row number and not a range length, the offset() formula didn't work.

BJ9 will change, like from 123 to 113, or 165. For all of these numbers, it is matched based on the inception date, so it's ok, but I need a way to make a formula like this:

=IF($A31>=DATEVALUE($BK$8),SKEW($F31:$F$666),"")

Broken down

this code is found in cell AZ31.
in AZ 32, we find:
=IF($A32>=DATEVALUE($BK$8),SKEW($F32:$F$666),"")

So you see that the $F$666 remains static, while all of the other numbers roll.

The DateValue BK8 is the same date that BJ9 is based off of, but it is in TEXT format so the code can read it.

I used 666 just for an example, but what really needs to go there is whatever number is found in BJ9.

So is there a way to get this:

=IF($A32>=DATEVALUE($BK$8),SKEW($F32:$F$INDEX($BJ$9)),"")

where INDEX($BJ$9)=the number in BJ9, such that the range is $F32:$F$# in BJ9?

Thanks!!

 

[Gerald Higgins]

I'm not an expert on the SKEW() function, in fact I've never used it.
But I think you can use OFFSET within SKEW().
For example

SKEW(OFFSET($F32,0,0,$BJ$9,1))

In this case, the value in BJ9 is giving you the height of the OFFSET range.

You MIGHT NOT want to use the raw value of BJ9 - for example, you might want to subtract some constant value, if there's some difference between the row numbers in your ranges.
So you could, for example, do something like this

SKEW(OFFSET($F32,0,0,$BJ$9-5,1))

Personally I'm convinced that this approach can be made to work, although I appreciate I may not be explaining it too clearly.

If you're still struggling, I would suggest trying it on a simplified version of your dataset, with fewer records, just to see how it all works.