Calculate % change based on a chosen amount of days

[Alexmedft]

I have a string of data lets say 365 days. I want a formula that looks at last day and calculates % change based on the certain number days I choose. I want to see how a stock has changed over say last 30 days or 100 days from last settlement. How do I do this?

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Alexmedft]

I attached a spreadsheet with the data.

[samba_ravi]

=(OFFSET(B217,(G214-1)*-1,)-B217)/OFFSET(B217,(G214-1)*-1,)
Try

[samba_ravi]

or
=(INDEX(B:B,ROW(B217)-G214+1)-B217)/INDEX(B:B,ROW(B217)-G214+1)

[Alexmedft]

This works but I forgot to add that everyday when stock settles we add another day how can i get this formula to look at last day and then calculate 60 days from that?

[AliGW]

Try this:

=(INDIRECT("B"&MATCH(MAX(A:A),A:A,0)-G214+1)-INDIRECT("B"&MATCH(MAX(A:A),A:A,0)))/INDIRECT("B"&MATCH(MAX(A:A),A:A,0)-G214+1)

[Alexmedft]

That works great but one question the data is decreasing so the percentage should be negative why is it returning a positive. Do I need to flip something in the formula?

[AliGW]

It's giving the result you asked for in the attachment, which is positive.

[Alexmedft]

60 days ago price was 64.07 today its 44.50 so priced decrease which should return a negative %.

[AliGW]

But that is not what it says in your attachment. This was the mock-up you gave us to work with:

Excel 2016 (Windows) 32 bit

	F	G
214	# Days	60
215	% Change	30.55%

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	G
215	=(B158-B217)/B158

Sheet: Sheet1

So please tell us what the answer for that scenario SHOULD be and why.

[samba_ravi]

=(INDEX(B:B,MATCH(10^10,A:A))-INDEX(B:B,MATCH(10^10,A:A)-60+1))/INDEX(B:B,MATCH(10^10,A:A)-60+1)
Try this

[Alexmedft]

I am sorry for the confusion that scenario was a mock up and had signs backwards. i was using (original-last)/original to get my positive value but formula should have been (last-Original)/Last.

[Alexmedft]

Awesome that worked thank you guys.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.