CountIF help

[john102016]

Hello everyone and thank you for taking the time to help me, My question is I am trying to figure out the probabilities of a coin flip that is flipped 4 times. I know there are 16 possible outcomes. here is my excel question.

I am trying to count how many time the letter H appears 1 time, 2 times, 3 times in a cell. I am trying to use the CountIF b1:b16"*h*" and that works for counting the cells that have an H. My problem is I need to know how many cells contain 1 h, then how many contain 2 h's etc... I have done it manually but I need it in excel. Thank you in advance.

John

[MrShorty]

I think it would help to be clear exactly what you have. What does your input look like? It sounds like it is a single text string like "hhth" or "tthh" or similar, is that correct?

If that is correct, my first thought is -- is there some external requirement that requires the input to be in a single cell like that? If I were doing this without restriction, I would enter the result of a single toss in a single cell. A group of four would then span across 4 columns. With each toss in its own cell, counting the h's (and t's if desired) is a very simply countif() function. Then, with that column of counts, another "frequency table" where I compute the frequency of the number of h's in sets of 4 (using either the frequency() function or a series of countif() functions)

If I was required to enter the data as a single text string, my first step would probably be to separate the 4 character text string into 4 one character cells (probably using the MID() function), then proceed as above.

Please Login or Register  to view this content.

Your question focused on counting the number of H's in a set of 4. Note that the result of both examples I gave is 2. Is order important, or would these two sets of 4 be treated as equivalent in your analysis?

[john102016]

My Data is as follows,
Results of coin flips
HHHH THHH

HHHT THHT

HHTH THTH

HHTT THTT

HTHH TTHH

HTHT TTHT

HTTH TTTH

HTTT TTTT

Out of this I need to see what the probability of getting 1 H is 2 H's and so on. So I am trying the CountIF b1:b16"*H*" That counts all the cells with an H but I do not know how to restrict the value to 1 h or 2h's. Sorry for the confusion I hope that make more sense. I am new to excel so this is a bit challenging but fun to learn.

Thank you for your help,

John

[MrShorty]

As I indicated, the approach I would likely take is to split the 4 character text string into 4 single character text strings. Whether you prefer to hand enter them in like that, or use the MID() function to split the text up. Did you understand how I used the MID() function to split the text up?

[sanram]

You can use this formula :

Please Login or Register  to view this content.