Can someone please explain this formula to me

[Jack-P-Winner]

I am trying to understand what this formula is saying compared to another one similar. If someone can break it down for me and explain what it is doing I would appreciate it. I have 51 rows with these formulas in them starting with row 2 the first group is in one column and the second group is in another. The top one just does not look right to me. I put a post on the Microsoft forum and had a dozen views but no replies. I know someone here will know

Thanks

I have one book that counts to 51 rows and 101 rows and I am counting how many times a number is drawn on an even or odd game number. That being said I am comparing what is drawn say in row 22 there is a 1 showing that 17 has been drawn 1 time. Now in row 16 the number 17 is drawn again so it goes to 2 since it has been drawn twice. When I do a reset on my games it may drop a 17 off and then go back to 1 Does any of this make sense to you in comparison to my formulas. See the attached pic. I am trying to make sure my logic is correct

Also the two groups are suppose to be doing the same thing. In these formulas

=COUNTIF($BI$2:$BI$51,BI2)-COUNTIF($BI$2:$BI$2,BI2)

=COUNTIF($BI$2:$BI$51,BI3)-COUNTIF($BI$2:$BI$2,BI3)

=COUNTIF($BI$2:$BI$51,BI4)-COUNTIF($BI$2:$BI3,BI4)

=COUNTIF($BI$2:$BI$51,BI5)-COUNTIF($BI$2:$BI4,BI5)

=COUNTIF($BI$2:$BI$51,BI6)-COUNTIF($BI$2:$BI5,BI6)

The BI2 stays constant but as you go down the middle and end part moves in sequence

BI$2,BI3

BI3,BI4

BI4,BI5

BI5,BI6

BUT, the 2nd group is constant all the way down

=(COUNTIF(F2:F$51,AX2)+COUNTIF(Q2:Q$51,AX2)+COUNTIF(AB2:AB$51,AX2)+COUNTIF(AM2:AM$51,AX2)+COUNTIF(AX2:AX$51,AX2)+COUNTIF(BO2:BO$51,AX2))

=(COUNTIF(F3:F$51,AX3)+COUNTIF(Q3:Q$51,AX3)+COUNTIF(AB3:AB$51,AX3)+COUNTIF(AM3:AM$51,AX3)+COUNTIF(AX3:AX$51,AX3)+COUNTIF(BO3:BO$51,AX3))

=(COUNTIF(F4:F$51,AX4)+COUNTIF(Q4:Q$51,AX4)+COUNTIF(AB4:AB$51,AX4)+COUNTIF(AM4:AM$51,AX4)+COUNTIF(AX4:AX$51,AX4)+COUNTIF(BO4:BO$51,AX4))

=(COUNTIF(F5:F$51,AX5)+COUNTIF(Q5:Q$51,AX5)+COUNTIF(AB5:AB$51,AX5)+COUNTIF(AM5:AM$51,AX5)+COUNTIF(AX5:AX$51,AX5)+COUNTIF(BO5:BO$51,AX5))

=(COUNTIF(F6:F$51,AX6)+COUNTIF(Q6:Q$51,AX6)+COUNTIF(AB6:AB$51,AX6)+COUNTIF(AM6:AM$51,AX6)+COUNTIF(AX6:AX$51,AX6)+COUNTIF(BO6:BO$51,AX6))

so, what is the difference between the two of these? As I said, they should be doing the same function.

Thanks

 

[David Evans]

Upload the file, JPW, and you're more likely to get an answer as people can follow along so much easier

you might want to explain a little more about what you're trying to model, unless of course, it is designed to give you an unfair advantage in a gaming situation

 

[NARAYANK991]

Hi Larry ,

If we take the following formula :

=COUNTIF($BI$2:$BI$51,BI4)-COUNTIF($BI$2:$BI3,BI4)

the first part , highlighted in BLUE , counts the number of occurrences of each cell number in the entire data range. In the posted formula , we will know how many times the value in BI4 occurred in the range BI2 : BI51.

The second part , highlighted in RED , counts the number of occurrences of each cell number in the range above that cell ; we will know how many times the value in BI4 has already occurred , in the range BI2 : BI3.

However , why this approach has been used is difficult to understand ; after all , what the posted formula will actually tell us is :

how many times does the value in each cell in the data range occur at that cell and below that cell

For example , let us assume the value in BI4 is 33 , and this same value occurs 5 times in the range BI2 : BI51 , in the cells BI2 , BI11 , BI27 , BI33 and BI51.

In the first occurrence of this value , we will have :

=COUNTIF($BI$2:$BI$51,BI2)-COUNTIF($BI$2:$BI$2,BI2)

which will be 5 - 1 = 4.

In the second occurrence of this value , which will be :

=COUNTIF($BI$2:$BI$51,BI11)-COUNTIF($BI$2:$BI$10,BI11)

we will have 5 - 1 = 4.

In the third occurrence of this value , which will be :

=COUNTIF($BI$2:$BI$51,BI27)-COUNTIF($BI$2:$BI$26,BI27)

we will have 5 - 2 = 3.

In the fourth occurrence of this value , which will be :

=COUNTIF($BI$2:$BI$51,BI33)-COUNTIF($BI$2:$BI$32,BI33)

we will have 5 - 3 = 2.

In the last occurrence of this value , which will be :

=COUNTIF($BI$2:$BI$51,BI51)-COUNTIF($BI$2:$BI$50,BI51)

we will have 5 - 4 = 1.

Is this what you want the formula to do ?

Narayan

 

[Jack-P-Winner]

Hi Larry ,

If we take the following formula :

=COUNTIF($BI$2:$BI$51,BI4)-COUNTIF($BI$2:$BI3,BI4)

the first part , highlighted in BLUE , counts the number of occurrences of each cell number in the entire data range. In the posted formula , we will know how many times the value in BI4 occurred in the range BI2 : BI51.

The second part , highlighted in RED , counts the number of occurrences of each cell number in the range above that cell ; we will know how many times the value in BI4 has already occurred , in the range BI2 : BI3.

However , why this approach has been used is difficult to understand ; after all , what the posted formula will actually tell us is :

how many times does the value in each cell in the data range occur at that cell and below that cell

For example , let us assume the value in BI4 is 33 , and this same value occurs 5 times in the range BI2 : BI51 , in the cells BI2 , BI11 , BI27 , BI33 and BI51.

In the first occurrence of this value , we will have :

=COUNTIF($BI$2:$BI$51,BI2)-COUNTIF($BI$2:$BI$2,BI2)

which will be 5 - 1 = 4.

In the second occurrence of this value , which will be :

=COUNTIF($BI$2:$BI$51,BI11)-COUNTIF($BI$2:$BI$10,BI11)

we will have 5 - 1 = 4.

In the third occurrence of this value , which will be :

=COUNTIF($BI$2:$BI$51,BI27)-COUNTIF($BI$2:$BI$26,BI27)

we will have 5 - 2 = 3.

In the fourth occurrence of this value , which will be :

=COUNTIF($BI$2:$BI$51,BI33)-COUNTIF($BI$2:$BI$32,BI33)

we will have 5 - 3 = 2.

In the last occurrence of this value , which will be :

=COUNTIF($BI$2:$BI$51,BI51)-COUNTIF($BI$2:$BI$50,BI51)

we will have 5 - 4 = 1.

Is this what you want the formula to do ?

Narayan

Sometimes I just wanna hug you Narayan. Yes you got it That's exactly what I'm doing. Now the question I have is. Look at the other group. To me it looks like it's doing something different or am I wrong?

=(COUNTIF(F2:F$51,AX2)+COUNTIF(Q2:Q$51,AX2)+COUNTIF(AB2:AB$51,AX2)+COUNTIF(AM2:AM$51,AX2)+COUNTIF(AX2:AX$51,AX2)+COUNTIF(BO2:BO$51,AX2))

=(COUNTIF(F3:F$51,AX3)+COUNTIF(Q3:Q$51,AX3)+COUNTIF(AB3:AB$51,AX3)+COUNTIF(AM3:AM$51,AX3)+COUNTIF(AX3:AX$51,AX3)+COUNTIF(BO3:BO$51,AX3))

 

[Jack-P-Winner]

Upload the file, JPW, and you're more likely to get an answer as people can follow along so much easier

you might want to explain a little more about what you're trying to model, unless of course, it is designed to give you an unfair advantage in a gaming situation

Thanks for replying Narayan helped me out

 

[NARAYANK991]

Hi Larry ,

Let us look at the following formula :

=(COUNTIF(F2:F$51,AX2)+COUNTIF(Q2:Q$51,AX2)+COUNTIF(AB2:AB$51,AX2)+COUNTIF(AM2:AM$51,AX2)+COUNTIF(AX2:AX$51,AX2)+COUNTIF(BO2:BO$51,AX2))

First off , the formula will not produce the same result as the other formula since it is looking at different cells altogether , and it is summing up the results from 6 columns.

Unless we know the values in all 6 columns , it will not be possible to understand exactly what the result of the formula will be.

What the formula is doing is :

It is counting the number of occurrences of the value in each of the cells AX2 through AX51 , in the ranges F2:F$51 , Q2:Q$51 , AB2:AB$51 , AM2:AM$51 , AX2:AX$51 , BO2:BO$51.

Two things need to be noticed here :

1. The start cell of every range in each formula is not absolute , as in F2 , Q2 , AB2 , AM2 , AX2 , BO2.

2. The end cell of every range in each formula is absolute , as in F$51 , Q$51 , AB$51 , AM$51 , AX$51 , BO$51.

Thus this formula always counts the number of occurrences from the current row till the last row , where the last row is fixed at row 51.

Of course , it is obvious that as we go down the data range , from row 2 through to row 51 , the counts will decrease , since we are looking at a smaller range each time , but whether the result of counting from 6 columns will be the same as the result from looking at just one column , only you can say , since you know what the data is like.

Narayan

 

[Jack-P-Winner]

See if this makes sense. The formula is counting all 6 columns BUT, lets take each column as an individual number like in your example with number 33. $51 is fixed since there are only 51 rows I want to count. I want to count the number of occurrences from the rows that have numbers in them. So, if I have the number 33 is in 3 rows it would place a 3 the column that the 33 is in. If number 33 is in 3 separate rows it would place the number of occurrences it pertains too F2:F$51 , Q2:Q$51 , AB2:AB$51 , AM2:AM$51 , AX2:AX$51 , BO2:BO$51