• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Counting only the first few occurances but data changes weekly.

T

Timothy1721

New Member
I've looked thought this forum and I'm couldn't find what I was looking to do any where, though I may have missed it.


On the below chart I want to count the "Yes" data in the first four "pulls" rows. I know can do this with a countif, however, the data changes routinely and is automatically sorted based on class first than %. Also, I may be required to only count the top 3 or possibly the top 5 results.


Is there a way to do this so the excel can grab the "pull" range automatically I want counted automatically without having to go in and edit the formula.


I'm not an expert in Excel, so if more information or clarity is needed, please let me know!

Thanks!

[pre]
Code: 
Number	Class	%
12	AAA	17.05%	NO
3	AAA	16.67%	NO
40	AAA	16.67%	NO
11	AAA	15.00%	NO
1	AAA	14.29%	NO
51	AAA	14.29%	NO
17	AAA	13.70%	NO
25	AAA	13.70%	NO
7	AAA	12.70%	NO
27	AAA	12.70%	NO
31	AAA	11.82%	NO
46	AAA	10.91%	YES
38	AAA	9.28%	NO
52	AAA	9.28%	NO
33	AAA	9.09%	YES
8	AAA	5.71%	NO
32	AAA	0.00%	NO
18	AAB	50.00%	NO
45	AAB	17.05%	NO
42	AAB	16.67%	NO
[/pre]
 
Hi Timothy1721,


Welcome to the Forums!!


Can you explain what does "Pull Rows" means, can you elaborate the process using an example from above table.


Regards,
 
Hello Fasheeh! Thanks for the Welcome!


It looks like the "pulls" didn't copy over properly when I was posted this. I've included it below.


The "No" or the "Yes" in the pulls column is determining if it matched a pulled number. For example, if the number 46 was pulled from a hat a "Yes" will be listed in that column. If the number is not pulled from a hat, there is a "No" in that column.


The next time numbers are pulled from a hat, the rankings will changed because the percentage will be different (the percentage is based on numerous calculations).


The other thing that complicates this is that the class of the number can change after each drawing. So while there may be 17 class AAA numbers in this drawing, there could be only 15 AAA in the next drawing, and therefore I would only want to count the "Yes" on the top 3 class AAA numbers, not the top 4.


I'm probably not explaining this well and over complicating things a bit, but essentially that is how it works.

[pre]
Code: 
Number	Class	%	pulls
12	AAA	17.05%	NO
3	AAA	16.67%	NO
40	AAA	16.67%	NO
11	AAA	15.00%	NO
1	AAA	14.29%	NO
51	AAA	14.29%	NO
17	AAA	13.70%	NO
25	AAA	13.70%	NO
7	AAA	12.70%	NO
27	AAA	12.70%	NO
31	AAA	11.82%	NO
46	AAA	10.91%	YES
38	AAA	9.28%	NO
52	AAA	9.28%	NO
33	AAA	9.09%	YES
8	AAA	5.71%	NO
32	AAA	0.00%	NO
18	AAB	50.00%	NO
45	AAB	17.05%	NO
42	AAB	16.67%	NO
43	AAB	16.67%	YES
36	AAB	13.70%	NO
37	AAB	12.70%	NO
44	AAB	12.70%	NO
22	AAB	11.82%	NO
13	AAB	9.28%	NO
21	AAB	5.71%	NO
49	AAB	0.00%	YES
26	ABA	9.28%	NO
50	ABB	28.57%	NO
39	ABB	13.70%	NO
5	BAA	17.05%	NO
2	BAA	11.82%	NO
14	BAA	9.28%	YES
[/pre]
 
Hi, Timothy1721!

Like Faseeh I'm a bit confused, I still don't get how it should work. My doubts or assertions:

a) You'll provide the 4 columns A:D as input, no need to calculate percentages

b) You need a group of N extra cells to count the top-N for the "Yes", how do you calculate N? It depends on or derives from no. of items per class?

c) It's only for Class = "AAA" or for each class?

Regards!
 
No, I want to count the "Yes" but only in the top 4 AAA results.


I know that I can select a range to do this (=COUNTIF(E3:E6,"yes")will work, but If I want to count only the top 3 AAA that have "yes" for the next drawing, this method won't work because it will count the top 4.


I have cell that tells me how many of the top AAA to count on each drawing so I want to be able to put that number into the formula(=COUNTIF(E3:E7,"yes") somehow, but I don't know if that is possible.


Does this make sense?
 
Hi, Timothy1721!


I assume this as your answers to my previous post:

a) Yes to A:D, nothing to be done with C.

b) Just a unique cell. You have in cell F1 (e.g.) the number of top-N to consider.

c) Only for "AAA".


If I'm right try with this formula at G1:

=CONTAR.SI.CONJUNTO(INDIRECTO("C3:C"&3+F1-1);"AAA";INDIRECTO("E3:E"&3+F1-1);"Yes") -----> in english: =COUNTIFS(INDIRECT("C3:C"&3+F1-1),"AAA",INDIRECT("E3:E"&3+F1-1),"Yes")


With F1:

up to 11, retrieves 0

from 12 to 14, retrieves 1

from 15, retrieve 2


Is that what you wanted? If not, please elaborate.


Regards!
 
Thanks Sir JB7! I think I can make this work. I should have mentioned that I do want to do this for the other classes as well "AAB","ABB", etc.


Would I use the same formula and just sub in the AAB or ABB?


I did have another question in regards to the formula. I know that C3:C is the range, I am not sure what the "&3" is doing. Could you let me know that?


Thanks so much for your time!
 
Hey Sir JB7....As as follow up I plugged in the formula you suggested, and it does work for AAA! I tried it on the AAB and it did not work.


Sorry for being such a rookie at this, but your help is really appreciated.
 
Hi, Timothy1721!


"C3:C"&3+F1-1 explanation:

As your "AAA" class data starts in row 3, cell C3, and you have to search into the F1 values starting at C3, the expression returns the address required for the search.


For other classes you can replace "AAA" but keeping that unique formula, not copying it because of the starting row of class (not always 3 for other classes). That's why I asked -and got no answer I should say- that in my first comment.


For this new scenario please give a look at this file, with 2 count versions at columns I and J, it's up to you, the first uses fixed cell references, the second dynamic named range references:

https://dl.dropboxusercontent.com/u/60558749/Counting%20only%20the%20first%20few%20occurances%20but%20data%20changes%20weekly.%20%28for%20Timothy1721%20at%20chandoo.org%29.xlsx


G3: class


H3: top-N


I3: count 1

=SI.ERROR(CONTAR.SI.CONJUNTO(INDIRECTO(DIRECCION(COINCIDIR(G3;C$3:C$36;0)+3-1;3)&":"&DIRECCION(COINCIDIR(G3;C$3:C$36;0)+3-1+H3-1;3));G3;INDIRECTO(DIRECCION(COINCIDIR(G3;C$3:C$36;0)+3-1;5)&":"&DIRECCION(COINCIDIR(G3;C$3:C$36;0)+3-1+H3-1;5));"Yes");"") -----> in english: =IFERROR(COUNTIFS(INDIRECT(ADDRESS(MATCH(G3,C$3:C$36,0)+3-1,3)&":"&ADDRESS(MATCH(G3,C$3:C$36,0)+3-1+H3-1,3)),G3,INDIRECT(ADDRESS(MATCH(G3,C$3:C$36,0)+3-1,5)&":"&ADDRESS(MATCH(G3,C$3:C$36,0)+3-1+H3-1,5)),"Yes"),"")


J3: count 2

=SI.ERROR(CONTAR.SI.CONJUNTO(INDIRECTO(DIRECCION(COINCIDIR(G3;BetsClassList;0)+FILA(BetsClassList)-1;COLUMNA(BetsClassList))&":"&DIRECCION(COINCIDIR(G3;BetsClassList;0)+FILA(BetsClassList)-1+H3-1;COLUMNA(BetsClassList)));G3;INDIRECTO(DIRECCION(COINCIDIR(G3;BetsClassList;0)+FILA(BetsClassList)-1;COLUMNA(BetsPullList))&":"&DIRECCION(COINCIDIR(G3;BetsClassList;0)+COLUMNA(BetsClassList)-1+H3-1;COLUMNA(BetsPullList)));"Yes");"") -----> in english: =IFERROR(COUNTIFS(INDIRECT(ADDRESS(MATCH(G3,BetsClassList,0)+ROW(BetsClassList)-1,COLUMN(BetsClassList))&":"&ADDRESS(MATCH(G3,BetsClassList,0)+ROW(BetsClassList)-1+H3-1,COLUMN(BetsClassList))),G3,INDIRECT(ADDRESS(MATCH(G3,BetsClassList,0)+ROW(BetsClassList)-1,COLUMN(BetsPullList))&":"&ADDRESS(MATCH(G3,BetsClassList,0)+COLUMN(BetsClassList)-1+H3-1,COLUMN(BetsPullList))),"Yes"),"")


Named ranges:

BetsTable: =DESREF(Hoja1!$B$3;;;CONTARA(Hoja1!$B:$B)-1;4) -----> in english: =OFFSET(Hoja1!$B$3,,,COUNTA(Hoja1!$B:$B)-1,4)

BetsClassList: =DESREF(BetsTable;;1;;1) -----> in english: =OFFSET(BetsTable,,1,,1)

BetsPullList: =DESREF(BetsTable;;3;;1) -----> in english: =OFFSET(BetsTable,,3,,1)


Just advise if any issue.


Regards!
 
Wow! I plugged it in and it did exactly what I was looking for.


I never in a million years would have figured that out on my own. Thanks so much!!
 
Hi, Timothy1721!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top