+ Reply to Thread
Results 1 to 18 of 18

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

  1. #1
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    605

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert JosÚ Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    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: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See the file
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    605

    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?

  4. #4
    Forum Expert JosÚ Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    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: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    605

    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.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,500

    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
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Forum Expert JosÚ Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

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

    Quote Originally Posted by Elijah View Post
    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.

  8. #8
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    605

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

    Quote Originally Posted by AliGW View Post
    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.

    Quote Originally Posted by JosÚ Augusto View Post
    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?
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,500

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

    Quote Originally Posted by Elijah View Post
    Thank you, Ali.
    No problem.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,500

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

    This might help you: https://en.excel-translator.de/translator/

    =HVISFEIL(MAKSA(INDEKS([H];RAD([@H])-4;1):INDEKS([H];RAD([@H])+$B$2-5;1));"")

  11. #11
    Forum Expert JosÚ Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

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

    Hi
    See the file
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    605

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

    Quote Originally Posted by AliGW View Post
    This might help you: https://en.excel-translator.de/translator/

    =HVISFEIL(MAKSA(INDEKS([H];RAD([@H])-4;1):INDEKS([H];RAD([@H])+$B$2-5;1));"")
    Ali,

    It sure did! Thank you!

    I wasn't aware that this tool existed. Very useful.

    Quote Originally Posted by JosÚ Augusto View Post
    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:

    =HVISFEIL(MAKSA(INDEKS([@H];RAD([@H])-4;1):INDEKS(@[H];RAD([@H])+$B$2-5;1));"")

    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!

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,500

    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.

  14. #14
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    605

    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: copy to clipboard
    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: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    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.
    Attached Files Attached Files
    Last edited by Elijah; 08-13-2018 at 10:57 AM.

  15. #15
    Forum Expert JosÚ Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    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.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    605

    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.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,500

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

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

  18. #18
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    605

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

    I forgot that, Ali. Done.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculate percentage change across two separate tables
    By shepherdc2814 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-19-2013, 05:30 PM
  2. Calculate percentage change of cell
    By flat_pro in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-12-2013, 01:51 PM
  3. [SOLVED] Change color of cell based on percentage
    By gwymanx1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-08-2012, 04:45 PM
  4. How to change the cell shade based on a percentage criteria?
    By oleb84 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-31-2011, 06:03 PM
  5. Replies: 4
    Last Post: 01-18-2011, 10:47 AM
  6. [SOLVED] percentage:how do I calculate the percentage change
    By Knowledge001 in forum Excel General
    Replies: 3
    Last Post: 11-25-2005, 02:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1