# Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell?

1. ## Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell?

Hi all,

I'm looking at some stock price data and calculate some basic statistics:

1) Price change:

(Price Today - Price N Days ago)/Price N Days ago

I just enter the formula directly.

2) N day Maximum High & N day Minimum Low

I simply use the maximum and minimum function with the selected range (last 3 days for example).

*

Typically, I look at 2 or 3 day, but I have interest in exploring higher values also, i.e., 10 day change. I could solve this by making multiple tables, but it would be very nice if a formula could be made where the lookback period can simply be changed by a cell value or similar.

I'm attaching a sheet where I have created the 3 day change/maximum/minimum where the N = 3 is merely illustrative. But the N = 3 is how I envision that calculation/lookback period could be changed easily.

I'm of course open to other suggestions.

Uten navn.png

Thanks in advance for any help!   Register To Reply

2. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell

Hi

Try these formulas in G6, H6 and I6

Formula:  `Please Login or Register  to view this content.`

Formula:  `Please Login or Register  to view this content.`

Formula:  `Please Login or Register  to view this content.`

See the file  Register To Reply

3. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell

Hi, Jose,

Thank you so much! Seems to work great. I will do some testing tomorrow to make 100% sure it's correct, but it sure seems like it.

I suspected the OFFSET function could be used for max/min and to be honest, if I had put my brain more to it, I could perhaps have figured out that one alone.

But the INDEX formulas are a bit above my capabilities, so I highly appreciate getting your help on that one. Curiously though. On the spreadsheet, I can't seem the IFERROR you write in the post?  Register To Reply

4. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell

Hi @Elijah

To avoid the use of volatile functions such as the offset function, you can use the two following fórmulas to get N-Day High/Low based

Formula:  `Please Login or Register  to view this content.`

Formula:  `Please Login or Register  to view this content.`  Register To Reply

5. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell

Hi, José,

Thank you! What do you mean by 'volatile function such as the offset function'...?

Also, in post #2 you were referring to =IFERROR(([@C]-INDEX([C],\$B\$2+ROW([@C])-5,1))/INDEX([C],\$B\$2+ROW([@C])-5,1),"") in cell G6, but on the sheet you uploaded, the IFERROR part was omitted.

I was curious if there was an error either in the sheet or what you posted above?

It seems to work correctly though.   Register To Reply

6. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell

Read up here on volatile functions: https://www.sumproduct.com/thought/v...lk-dirty-to-me  Register To Reply

7. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell Originally Posted by Elijah Also, in post #2 you were referring to =IFERROR(([@C]-INDEX([C],\$B\$2+ROW([@C])-5,1))/INDEX([C],\$B\$2+ROW([@C])-5,1),"") in cell G6, but on the sheet you uploaded, the IFERROR part was omitted.
To see the error go to the last line of your table.  Register To Reply

8. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell Originally Posted by AliGW Read up here on volatile functions: https://www.sumproduct.com/thought/v...lk-dirty-to-me
Thank you, Ali. Very illuminating. Know I know why I always get ask if I want to save (even though I just did) when exiting my sheets with OFFSET.  Originally Posted by José Augusto To see the error go to the last line of your table.
Ah, yes. I understand. I was just curious if there was a reason you didn't include the IFERROR in the sheet you uploaded, but I guess you just didn't bother to add it.

I was able to add it now and it seems to work correctly.

I can't seem to make the N-DAY HIGH/LOW to work though. I've tried a few times. Since I have a Norwegian Excel, I can't simply copy and paste either.

Care to take a look?   Register To Reply

9. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell Originally Posted by Elijah Thank you, Ali.
No problem.   Register To Reply

10. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell  Register To Reply

11. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell

Hi
See the file  Register To Reply

12. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell Originally Posted by AliGW Ali,

It sure did! Thank you! I wasn't aware that this tool existed. Very useful. Originally Posted by José Augusto Hi
See the file
Thank you so much, José!

I was just about to post my OWN sheet as I was able to figure it out on my own using Ali's translator.

The mistake I made was very small:

I had a @ in front of the H as boldened above.

At least it looked like that was the error. I couldn't see any other differences.

Regardless, it's working great now and I'm very happy for all help! Thanks again!   Register To Reply

13. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell

Yes, it's a very handy tool - I have it permanently bookmarked.   Register To Reply

14. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell

Hi, all,

I added a new blank row between the N at row 2 and the header at row 5 (now row 6). This naturally changed the formula. So, I've tried to wrap my head around the INDEX formula and finally find myself starting to understand it a bit better.

For N-Day Change, the formula should now read:

Formula:  `Please Login or Register  to view this content.`

If I understand correctly, in this part: \$B\$2+ROW([@C])-6

the number 6 (prior 5) is simply chosen to make the correct offset and find the correct row/value to reference in the formula. Right?

But what about the number 1 at the end?

\$B\$2+ROW([@C])-6,1)

*

The formulas for N-Day High/Low were a bit above my capabilities, but I think I've corrected them properly:

Formula:  `Please Login or Register  to view this content.`

Formula:  `Please Login or Register  to view this content.`

Also, on the MINA formula, the last one, I don't quite understand why both [H] and [L] are referenced as opposed to the MAXA where only [H] is referenced.

I consider what I asked solved, but would be great to get confirmation that I got it right and also some explanation on what I'm asking if possible. EDIT: Although it's strictly solved, I'm unchecking it in case people won't view it if it's "Solved". Will put it back on later. Thanks.  Register To Reply

15. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell

Hi @Elijah

To correct the formulas and obtain a more robust formulas you can substitute the values of the constant 5 and the 4 by ROW(J\$5) and ROW(J\$5)-1.

The column J Or G or H as no interference with the function ROW. You can choice another column, but 5 is the number of the row head of your table.

Also ROW([@L]) is the same as ROW([@H]).

See the file and the updated formulas and try to insert one or more rows between Row 1 and Row 4.  Register To Reply

16. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell

Excellent! Thank you so much, José Augusto! That works great. Also, thanks for the clarification. Sadly, I wasn't able to give you more reputation yet, but please receive my gratitude.  Register To Reply

17. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell  Register To Reply

18. ## Re: Calculate N-Day Percentage Change and N-Day High/Low based on N value in separate cell

I forgot that, Ali. Done.   Register To Reply