+ Reply to Thread
Results 1 to 9 of 9

range and if formula to detect a % increase in a timeframe

  1. 05-31-2016, 05:41 PM #1
    Ebru
    Ebru is offline
    Registered User
    Join Date
    04-28-2016
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    20

    range and if formula to detect a % increase in a timeframe

    Hi

    I want to backtest a stock analysis based on historical data.
    Therefore I need a formula which analyses day per day if in the timeframe of 30 consecutive days in the future there was a 15% price increase.

    Below the link to the Tesla example:
    https://docs.google.com/spreadsheets...uchypM/pubhtml
    "lookup if value in cell G25 is at least once at 115% in cells G24-G2, if so "Yes" if not "No"."
    It would be great if the formula would refer to another sheet where I put the number of days and %.

    I think an if formula and range formula should be used.

    Thanks for your help

    Ebru
    Reply With QuoteRegister To Reply
  2. 06-01-2016, 02:29 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,679

    Re: range and if formula to detect a % increase in a timeframe

    Attach a sample workbook here (many members will not follow links to file-sharing sites). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 06-01-2016, 02:51 PM #3
    Ebru
    Ebru is offline
    Registered User
    Join Date
    04-28-2016
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    20

    Re: range and if formula to detect a % increase in a timeframe

    Hi
    I have/has issues to attach a file on this forum.
    Here's the link to the file on a filesharing website:
    http://www.filedropper.com/teslastock1

    The goal is thus to have a
    lookup if value in cell G25 is at least once at 115% in cells G24-G2, if so "Yes" if not "No"."

    Ebru
    Reply With QuoteRegister To Reply
  4. 06-01-2016, 03:05 PM #4
    MrShorty
    MrShorty is offline
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,819

    Re: range and if formula to detect a % increase in a timeframe

    I'm not sure I understand, but it sounds like you want to know if G25 is greater than 115% of the max value in G2:G24. Will something like =max(G2:G24)*1.15<=G25 work (it will return TRUE or FALSE instead of Yes or No)
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.
    Reply With QuoteRegister To Reply
  5. 06-01-2016, 04:22 PM #5
    Ebru
    Ebru is offline
    Registered User
    Join Date
    04-28-2016
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    20

    Re: range and if formula to detect a % increase in a timeframe

    Hi MrShorty
    Thanks for your answer.
    My goals is to predict the stockmarket, or at least try to .
    I take historical data and presume we are today 26/04/2016. then I want to know which stocks significantly rose after that day, then I will analyse that population to look at patterns before 26/04/2016 which gave in hindsight a significant indication that these stocks were about to rise.
    So I 1st want to know if there is a value in the 30 days after 26/04/2016 which has a value which is at +15% (then i will drag down that formula on all earlier dates).
    So I think it is like the opposite but I do not know how to tweak the formula, can you post it? Feel free to
    ask additional info if needed.

    Ebru
    Reply With QuoteRegister To Reply
  6. 06-01-2016, 05:15 PM #6
    MrShorty
    MrShorty is offline
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,819

    Re: range and if formula to detect a % increase in a timeframe

    I don't think I can post it, because I do not understand the logic behind what you are trying to do. Can you work out G25 by hand? Should G25 be Yes/True or No/False, and how do you arrive at that result? Considering that G25 is larger than any of the values in G2:G24, is the answer false/no no matter what the specific criteria is?
    Reply With QuoteRegister To Reply
  7. 06-02-2016, 04:02 PM #7
    Ebru
    Ebru is offline
    Registered User
    Join Date
    04-28-2016
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    20

    Re: range and if formula to detect a % increase in a timeframe

    No problem:
    Lets take: 26/04/2016 here the close price (=column Adj.close) of the stock was 253.740005 USD
    The question I have is "Did this stock go +15% in the next 20 tradingdays?
    Or in Excel language: is there at least 1 cell in the range G25-G6 which is at least at 253.740005USD*1,15=291,8 USD? if so put TRUE on the line with date 26/04/2016, if not put False.
    Then I will drag down that formula to have it answered for all records earlier than 26/04/2016.

    It would be great to have such formula!

    Ebru
    Reply With QuoteRegister To Reply
  8. 06-02-2016, 04:13 PM #8
    MrShorty
    MrShorty is offline
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,819

    Re: range and if formula to detect a % increase in a timeframe

    It appears that your ranges have changed from yesterday's sample file. Wouldn't it be something like =max(next 20 days)>=1.15*today? or using yesterday's ranges =max(G2:G24)>=1.15*G25
    Reply With QuoteRegister To Reply
  9. 06-03-2016, 05:56 PM #9
    Ebru
    Ebru is offline
    Registered User
    Join Date
    04-28-2016
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    20

    Re: range and if formula to detect a % increase in a timeframe

    Thanks MrShorty!
    This formula is so helpful and easier than I tought.
    Thank so much - you made my weekend!

    Ebru
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Detect when all cells in a range change value
    By ozizushi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-22-2014, 12:09 PM
  2. Lookup formula based on a condition and date timeframe
    By rmitri in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-14-2013, 01:56 PM
  3. [SOLVED] Detect missing number within range
    By azhanurul in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-26-2012, 02:22 AM
  4. [SOLVED] Macro if detect any value put assigned value in all range
    By feroguz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-13-2012, 12:21 PM
  5. How to auto increase a monthly range, & addition formula based on dates if criteria
    By Spiritseeker in forum Excel General
    Replies: 1
    Last Post: 03-15-2009, 05:38 AM
  6. [SOLVED] Auto-detect range size
    By shelfish in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-19-2006, 05:25 PM
  7. [SOLVED] Detect NULL (??) range?
    By Ed in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-25-2005, 01:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1