Counting or finding formula

[hopkins199]

I am working on an assignment and i am stuck writing a formula that i need to get done asap.

I have created a random number generator for a coin toss

[mycode]=RAND()<.5, "Heads","Tails")[mycode]

that works fine. so i have that ten times on the same row.

I need to make a formula that looks at those ten cells and finds if there are four "Heads" in a row and if so than it is a win condition if not then lose condition.


someone please help me. i do not need the answer i just need to know if theres a formula out there that will help my problem i will do my own work.

[FDibbins]

Thanks for the title change

Take a look at using IF() and countif() for this

[macropod]

• We would love to help you with your query

Except, AFAIK, we generally don't do homework...

[hopkins199]

i am not asking for the answer just need to know what formula i can use to get my own answer. i am glad people think i am looking for a handout. i would have posted the problem if that were the case instead of what i actually posted.

[FDibbins]

Did you see my edit to my 1st post to you?

[hopkins199]

yea sorry i am just lost and flustered spending most of my day on this and can't wrap my tired brain around it yet.

[FDibbins]

Understandable

What have you tried so far?

countif() syntax is =countif(range-containg-criteria,criteria)

You would then wrap this in an IF() statement, testing for your requirements

[martindwilson]

countif wont work
there is a frequency array formula but try explaining that away!

[hopkins199]

the problem for me is i have ten items of data that i am trying to have it count if their are four of a certain type in that string of ten. then it would comeback as a win. From my limited knowledge of excel trying to have it count four items in a row out of ten seems like a long formula.

[FDibbins]

aahh ok I missed the "4 in a row"

There is a work-around using a helper column, starting on the 4th value, using countif() based on the above 4 values, copied down

[martindwilson]

use a helper column lets assume you have =IF(RAND()<0.5, "Heads","Tails") in a1:a10
and then use offset in b1
you can create a range 4 rows deep from each cell in column a

=COUNTIF(OFFSET(A1,0,0,4),"heads")=4 you only need to fill that down to b7
that will check each 4 cells in turn
a:a4 then a2:a5 ....a7:a10 and return true/false
you can the do something on that data to see if true is returned i'll leave that for you to figure out but countif or match should what you are looking for

[martindwilson]

yeah ford that seems a bit easier and avoids having to learn offset for now
might as well spill the beans! try =COUNTIF(A1:A4,"heads") in b4 then see what happens if you fill that down to b10

[FDibbins]

Yup, thats pretty much where I was headed, Martin

[hopkins199]

ok i will try to get this working thanks

[hopkins199]

ok so if i have to do this many times over how will i use the helper cell. i am supposed to run this 10000 times.

[martindwilson]

so you are going to sit and recalculate the workbook 10000 times?

[hopkins199]

thats what i thought thanks for the help everyone this is a good source of information.