+ Reply to Thread
Results 1 to 8 of 8

Counting consecutive values

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

    Counting consecutive values

    Hello everyone,

    I have been sent an excel sheet from a teacher of mine, but he is not available at the moment so I can not ask him. I have spent hours trying to figure it out myself, but I just can`t grasp it on my own.

    Basically, the sheet is a montecarlo simulation. 5000 simulations of 10 trades in the stock market, where 0 equals a loss and 1 is a winner.

    The purpose of the sheet is to find the probability of getting 1,2,3,4,5,6,7,8,9 or 10 consecutive winners or losers.

    For that purpose, he has used the FREQUENCY function, but I just do not understand the formula and the way it is done.

    I understand the sheet and all, but I am simply stuck on the formula.

    I copied and pasted the relevant part of the sheet into a new sheet that I attached.

    I would be very thankful if anyone could walk me gently through each step of the formula for consecutive wins and consecutive losses. I seem to have some problems understanding the use of the COLUMN function in the formula as well.

    Thanks in advance!

    Best regards,

    Elijah
    Attached Files Attached Files

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Counting consecutive values.

    Let's use this analogy for the FREQUENCY function.

    We have penquins to ship.
    We have boxes that hold various numbers of penguins.

    For each penguin order, we need to reserve a box of the appropriate size.

    Using your posted workbook and using Row_13 for our example...
    each column number represents a number of penguins to ship

    These are the values in columns D:M
    Please Login or Register  to view this content.
    We have orders for:
    Please Login or Register  to view this content.
    The first section of the FREQUENCY function: IF(D13:M13=1,COLUMN(D:M))
    shows those numbers when evaluated: {FALSE,FALSE,6,7,8,FALSE,10,FALSE,FALSE,13}

    The second section effectively tells us what box sizes we have to work with:
    IF(D13:M13<>1,COLUMN(D:M))
    resolves to: {4,5,FALSE,FALSE,FALSE,9,FALSE,11,12,FALSE}

    So for box capacities, we have:
    4
    5
    9
    11
    12
    Over_12

    If we don't have an exact match for a penguin order, we go to the next size up.

    The FREQUENCY function tells us which box capacities are appropriate for each penguin order.
    Then it calculates the count for each box capacity
    Please Login or Register  to view this content.
    These are the boxes required:
    Please Login or Register  to view this content.
    We want to know the box capacity we need the most of...
    So...our B13 formula resolves this way:
    Please Login or Register  to view this content.
    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Counting consecutive values.

    that's it i'm off to north pole ! no penguins there lol
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

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

    Re: Counting consecutive values.

    Ron Coderre,

    Thank you very much for giving me a hand.

    I read it quickly yesterday before going to bed, and I was thinking what the hell is this guy talking about with penguins and all

    I just sat down with it now and I think it is sinking in. I will keep studying your example for a while and if I do not completely grasp it, I might have to ask you one more time if that`s okay

    All the best,

    Elijah

  5. #5
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Counting consecutive values.

    OK...but, today all I can think of is aardvarks!

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

    Re: Counting consecutive values

    Hehe. You can save the aardvarks for another time

    After re-reading your example, getting it, then forgetting it, re-reading it again and practicing, I finally got it!

    Thank you very much for taking the time, much appreciated!

    I am curious, what level is this kind of formula on? Basic or advanced excel?

    This sheet has made me feel like an idiot at times, considering how long time I used to figure it out.

    Would there be an easier way to perform the same task or is the frequency function the best choice?

  7. #7
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Counting consecutive values

    I'll call the FREQUENCY function advanced, due to the learning curve.

    The FREQUENCY function uses the bins in the same way a an Amazon.com book
    packer would select a box. If you can't find one the exact size, you go one size bigger
    (the book certainly won't fit in a smaller box, right?) Then it calculates how many of each
    bin (box) are required to accommodate the data (books).

    Here's how I became comfortable with it. One day, I spent an hour playing with various
    input cells and bin cells. At some point, it all made sense. Now, it's fairly easy to use.
    The biggest gotcha in learning that formula is realizing that it will sometimes create a
    bin for items smaller than your minimum bin and/or a create a bin for items larger than your
    maximum bin...depending on the data. Built carefully, the function can be very powerful.

    I hope that helps.

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

    Re: Counting consecutive values

    Thanks again, Ron.

    It definitely helped

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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