# Counting consecutive values

1. ## 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.

Best regards,

Elijah

2. ## 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?

3. ## Re: Counting consecutive values.

that's it i'm off to north pole ! no penguins there lol

4. ## 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. ## Re: Counting consecutive values.

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

6. ## 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. ## 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. ## Re: Counting consecutive values

Thanks again, Ron.

It definitely helped

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

#### 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