• 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.

Need help on a formula?

SONJOE JOSEPH

SONJOE JOSEPH

Member
Dear all Excel Experts,

Good Morning. I am attaching a file were u will find the Date, CP ( Closing Price ), DR (Daily Return) and Yearly Daily Average Return (YDAR). The question is on a constant cell ie "N2" i want the values starting from D253. You will find here that on 1st Jan,13 the YDAR was 0.10% so this should appear in the constant "N2" cell. On the next day ie 2nd Jan,13 the YDAR was also 0.10% so the constant "N2" cell has to change to 0.10%. So this process has to continue. (This is one way of doing)

Second Method
-----------------
Let suppose that in Range "D" the values of YDAR from D253 to D268 are not there. Whether we can make a formula were i need to find the YDAR in cell "N2" on a yearly basis ie 365 days. For eg from 1-Jan,13 to 3rd Jan,12 the YDAR is 0.10% . So the the cell "N2" has to update the value 0.10%. On the next day ie 2nd Jan,13 to 4-Jan,12 the YDAR is same 0.10% so the cell "N2" has to again change to 0.10%. So this process has to continue.

Some times the YDAR values changes so each result has to updated in the "N2" cell ie the key thing here.

Please help me to solve this problem and i'm attaching a working file for your reference.

Thanking You,

With Regards,

Sonjoe Joseph.
 

Attachments

  • SP.xlsx
    20 KB · Views: 9
I'm not really sure what you want
But in D2 put =IFERROR(AVERAGEIFS($C$2:$C$268,$A$2:$A$268,"<="&A2,$A$2:$A$268,">"&EDATE(A2,-12)+1),0)
Copy down

This will get the 12 month average backwards from the current date
 
Hui said:
I'm not really sure what you want
But in D2 put =IFERROR(AVERAGEIFS($C$2:$C$268,$A$2:$A$268,"<="&A2,$A$2:$A$268,">"&EDATE(A2,-12)+1),0)
Copy down

This will get the 12 month average backwards from the current date
Click to expand...

Well this is not i'm looking for my friend. See the YDAR from 3 Jan,12 to 1 Jan,13 is 0.10% (J253) this has to be updated in the cell "N2". Likewise from 4 Jan,12 to 2nd Jan,13 is also 0.10% (J254) so this value has to get updated in the same cell "N2". So this process has to continue then only its correct. Now i hope u got the idea.

Regards,

Sonjoe Joseph.
 
In N2 you can have =J253
But then I don't know what you mean by
"Likewise from 4 Jan,12 to 2nd Jan,13 is also 0.10% (J254) so this value has to get updated in the same cell "N2" "

Can you complete some cells to show what the answer should be as I have no idea what you mean
 
You must log in or register to reply here.
Back
Top