+ Reply to Thread
Results 1 to 7 of 7

Help identifying trends and patterns

  1. #1
    Registered User
    Join Date
    11-23-2012
    Location
    Massachusetts
    MS-Off Ver
    Home and Office 2016 32-bit
    Posts
    72

    Help identifying trends and patterns

    Hello,

    I have not been able to figure out a formula that detects patterns. I have tried some conditional formulas, but haven't been able to figure it out. I attached an example.

    I need several formulas that identify several patterns. I need the formulas for the following situations:

    1. % of time after a profitable day, we have a profitable day
    2. % of time after 2 profitable days, we have a profitable day
    3. % of time after 3 profitable days, we have a profitable day
    and so on

    I also need:
    1. % of time after a negative day, we have a profitable day
    2. % of time after 2 negative days, we have a profitable day
    3. % of time after 3 negative days, we have a profitable day
    and so on

    I enter new information every day, ideally I would like all the formulas to automatically update every day. They could give me the % of a profitable day today based on the recent history. I also need the formulas to constantly adjust to the new information, meaning if a month ago there was a 60% of a profitable day after a negative day, but recent results have changed that, I need the formulas to roll with the new data that is entered every day.
    Pattern example.xlsx
    Thank you. I really appreciate the help.
    Steve

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Help identifying trends and patterns

    Your data layout is ... not self-explanatory. Why all the blanks in col G? Is 11/16/15 the "day after" 11/14/15?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    11-23-2012
    Location
    Massachusetts
    MS-Off Ver
    Home and Office 2016 32-bit
    Posts
    72

    Re: Help identifying trends and patterns

    Pattern example.xlsx

    Hello,
    For some reason, I missed your reply, I apologize.

    I cleaned up the sheet. Please see attached.

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

    Re: Help identifying trends and patterns

    Hi

    I do not know if that's what you want

    See the file Pattern example.xlsx

  5. #5
    Registered User
    Join Date
    11-23-2012
    Location
    Massachusetts
    MS-Off Ver
    Home and Office 2016 32-bit
    Posts
    72

    Re: Help identifying trends and patterns

    For some reason, I don't get emails anymore when someone replies to my posts. I apologize for the delay.

    Thank you Jose. I'm not exactly sure what you did. Does L7 state there is only an 18% chance of having a profitable day after 2 profitable days in a row? Does L8 state there is only a 26% chance of having a negative day after having 2 negative days in a row.

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

    Re: Help identifying trends and patterns

    Hi Dbogey

    No.
    K7 value represent the number of isolated days (K1 days=1), i.e.
    [Non rentable] [Rentable] [Non rentable] = 54% chance of having a profitable day between two non profitable days

    L7 value represent the number of pair days (L1 days=2), i.e.
    [Non rentable] [Rentable] [Rentable] [Non rentable] = 18%

    The same for line 8 with non rentable days.

    I hope you understand

  7. #7
    Registered User
    Join Date
    11-23-2012
    Location
    Massachusetts
    MS-Off Ver
    Home and Office 2016 32-bit
    Posts
    72

    Re: Help identifying trends and patterns

    I'm not sure I understand. If you are stating, L7 states there is an 18% chance of the specific pattern, negative, positive, positive, negative, that is interesting.

    But what I need is simpler, I would think. I need the % of time there are 2 positive days in a row, 3 positive days in a row, 4 positive days in a row, etc. I also need the % of time there are 2 negative days in a row, 3 negative days in a row, 4 negative days in a row, etc.

    Does your sheet state that anywhere? If not, is there a way to do that?

    Thanks for the help

+ 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. Looking or Trends/Patterns in open text field
    By obxlee in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-28-2013, 07:14 PM
  2. [SOLVED] Excel 2010 Identifying data changed in cells and Identifying the changed rows
    By SandyLake in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-13-2013, 01:12 AM
  3. Identifying patterns across columns!
    By yellow281 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-29-2012, 12:04 PM
  4. Identifying Trends, Predicting Dates
    By jennyaccord in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-20-2012, 09:36 AM
  5. Trends
    By gordy4barnet in forum Excel General
    Replies: 4
    Last Post: 08-08-2011, 06:44 AM
  6. Converting 2007 VBA color patterns to 2003 color patterns
    By ChemistB in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-13-2011, 12:00 PM
  7. trends
    By usenet in forum Excel General
    Replies: 3
    Last Post: 01-09-2006, 03:20 PM
  8. TRENDS
    By Robin in forum Excel General
    Replies: 0
    Last Post: 02-11-2005, 10:06 AM

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