Automatic Calculation

[francis-bacon]

Hi,
I have a question concerning the calculation of a simple percent change between two values. That is of course not a real problem.
But what is for me is a problem is that the time period for calculating the percent change between two values is changing.
In the example below I have calculated a 10 day percent change between two values; e.g. I have manually calculated the percent change between value 1,456.38 in row 36 and value 1,448.31 in row 27.
What I want is changing the value in cell C1 and then the percent change between two values is calculated automatically according to the value in cell C1.
In my example the value 10 is entered in cell C1 so I calculate the percent change between the current value and the value 10 days ago.
If I now enter the value 14 in cell C1 the percent change between the current value and the value 14 days ago should be calculated automatically. How can this be done?

Sapmle Calulation.png

A B C
1 1,418.30 10
2 1,416.60
3 1,418.34
4 1,409.71
5 1,412.84
6 1,412.11
7 1,414.85
8 1,423.82
9 1,430.73
10 1,431.90 0.96
11 1,430.62 0.99
12 1,426.37 0.57
13 1,430.50 1.47
14 1,422.95 0.72
15 1,427.99 1.12
16 1,440.13 1.79
17 1,423.90 0.01
18 1,422.18 -0.60
19 1,420.62 -0.79
20 1,428.82 -0.13
21 1,438.24 0.83
22 1,445.94 1.08
23 1,448.39 1.79
24 1,446.99 1.33
25 1,448.00 0.55
26 1,450.02 1.83
27 1,448.31 1.84
28 1,438.06 1.23
29 1,433.37 0.32
30 1,444.26 0.42
31 1,455.30 0.65
32 1,456.81 0.58
33 1,455.54 0.59
34 1,459.68 0.81
35 1,457.63 0.52
36 1,456.38 0.56 =+((100*A36)/A27)-100


Thanks a lot for your efforts.
Thomas

[XLent]

Thomas,

You have a few options here, one would be:

B1: =IF(ROWS(A$1:A1)<=$C$1;"";100*($A1/INDEX($A:$A;ROW(A1)-$C$1)-1))
copied down

You could also use OFFSET which would be simpler (syntax) but Volatile.

[avk]

Can you share sample file.

[francis-bacon]

Hi XLent,

first of all, many thanks for your help.
Unfortunately I have to reply that I get an error message when I enter your formula in cell B1.
=IF(ROWS(A$1:A1)<=$C$1;"";100*($A1/INDEX($A:$A;ROW(A1)-$C$1)-1))

The error message is:
"There is problem wit this formula.
Not trying to type this formula? ....

In the formula the part $C$1 is highlighted.
=IF(ROWS(A$1:A1)<=$C$1;"";100*($A1/INDEX($A:$A;ROW(A1)-$C$1)-1))

Thanks again,
Thomas

[AliGW]

Attach the workbook.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), 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.

[francis-bacon]

Hi avk,

see comment from AliGW.

Thanks

[XLent]

Hi XLent,

first of all, many thanks for your help.
Unfortunately I have to reply that I get an error message when I enter your formula in cell B1.

I had assumed given your locale you would be using the ; delimiter but, perhaps, you're using , in which case:

B1: =IF(ROWS(A$1:A1)<=$C$1,"",100*($A1/INDEX($A:$A,ROW(A1)-$C$1)-1))
copied down

edit:

in reference to attachments -- you can add an Attachment but follow the directions at the end of AliGW's prior post - i.e. Go Advanced when replying and use Manage Attachments link (as opposed to the icon).

[AliGW]

Hi avk,

see comment from AliGW.

Thanks

No, that was meant for you. YOU need to provide the workbook - it's your problem.

EDIT: to clarify, by "your problem" I mean it is your query, so it's your responsibility to provide the workbook.

[francis-bacon]

Hi XLent,

again many thanks for your help.

This time the formula works fine.

Although I live in Germany I use the American English version of Windows 10 with the English settings for date, time, and number settings.

I will attach the Excel file. I thought it is not possible to attach a file but only the icon that does not work.

[AliGW]

Works fine here in your workbook (see column D):

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
1	**1,418.30	**	10		=IF(ROWS(A$1:A1)<=$C$1,"",100*($A1/INDEX($A:$A,ROW(A1)-$C$1)-1))
2	**1,416.60	**
3	**1,418.34	**
4	**1,409.71	**
5	**1,412.84	**
6	**1,412.11	**
7	**1,414.85	**
8	**1,423.82	**
9	**1,430.73	**
10	**1,431.90	**
11	**1,430.62	**0.87		0.87
12	**1,426.37	**0.69		0.69
13	**1,430.50	**0.86		0.86
14	**1,422.95	**0.94		0.94
15	**1,427.99	**1.07		1.07
16	**1,440.13	**1.98		1.98
17	**1,423.90	**0.64		0.64
18	**1,422.18	**-0.12		-0.12
19	**1,420.62	**-0.71		-0.71
20	**1,428.82	**-0.22		-0.22
21	**1,438.24	**0.53		0.53
22	**1,445.94	**1.37		1.37
23	**1,448.39	**1.25		1.25

Sheet: Sheet1

[AliGW]

I see you've marked this as solved. What was the issue in the end?

[francis-bacon]

Hi AliGW,

the formula is working fine, so there in no more any issue. I have therefore marked the topic as solved. Isn't that the right procedure?

[AliGW]

Yes, but it's courteous to those who have helped you to sign the thread off here and say how it was resolved - this is also useful for anyone viewing the thread later.