YTD return for a moving range.

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 2, 2014

• #2

Welcome to MrExcel.

Try:

=AVERAGE(B2:INDEX(B:B,MATCH(9.99999999999999E+307,B:B)))

 

Upvote 0

M

MarcJohn

New Member

Joined

Jun 2, 2014

Messages

28

• Jun 2, 2014

• #3

I don't think that's quite what I'm looking for. The most recent date is at the top and the beginning of the year is some rows down (and continues to be pushed down another row every day). =SUM of the range of CHG (which is F3:F111 on my spreadsheet) works but obviously tmrw that will not work because the start of the year will be F112....

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 2, 2014

• #4

Why do you think it's not what you are looking for? Did you try it?

 

Upvote 0

M

MarcJohn

New Member

Joined

Jun 2, 2014

Messages

28

• Jun 2, 2014

• #5

Yes, I tried it. It doesn't give me the correct value. I need to compare the price of a security today with the price of that security on the first day of the year- So that I can simply compute a YTD return. I just don't know how to lock the reference point for the first day of the year because the dates move down one row every day. Hopefully that makes more sense.


I need a formula that will continuously compare the yield from 1/1/14 (which will move down one row everyday) with the present yield.

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 2, 2014

• #6

You have data below first day of year? You don't in your sample data.

 

Upvote 0

M

MarcJohn

New Member

Joined

Jun 2, 2014

Messages

28

• Jun 2, 2014

• #7

I apologize, I'm new to the forum.. didn't mean to make a faulty example. Yes I have multiple years of data on the spreadsheet but for this particular problem i only need to use the data back to the first of the year. Thats why I was wondering if there is a way to lock in on the cell that corresponds with the first of the year.

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 2, 2014

• #8

Maybe try:

=AVERAGE(B2:INDEX(B:B,MATCH(DATE(YEAR(A2),1,1),A:A,-1)))

 

Upvote 0

M

MarcJohn

New Member

Joined

Jun 2, 2014

Messages

28

• Jun 2, 2014

• #9

I apologize but that's still not what I'm looking for.... Here is a better laid out table to go by..

Date/Time	Last Yield	CHNG	YTD return	Your return
=Now	0.394	0.019	0.012	0.3813
5/30	0.375	-0.002
5/29	0.377	0.01
5/28	0.367	0.021
5/27	0.346	0.002
5/26	0.344	0
5/23	0.344	-0.001
1/1	0.382	...
... and so on...	...	...

<tbody>
</tbody>

The YTD return should be the sum of the changes from individual days, or simply the first of the year minus today. I have that in bold blue. Your function gave me the value I have in bold red. (The colors have nothing to do with relative value by the way- just a way to make them standout)

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jun 2, 2014

• #10

The SUM would be:

=SUM(C2:INDEX(C:C,MATCH(DATE(YEAR(A2),1,1),A:A,-1)))

 

Upvote 0