Optimum Set
- Thread starter Karnac
- Start date
bloubul
Member
GillesD / PAB
GillesD this is in response of your reply, so please help me before I go on holiday.
I'm starting a fresh but encounter the following problem. I remember PAB stated that some Excel formula's must be constructed different in outher countries. I'm using two office packs. Office 2000 (at work) and Office XP Pro (at home) and it gives different counts. E.G
Office 2000
No Cnt
1 59
2 58
3 55
4 52
5 44
6 54
7 66
8 63
Office XP Pro
No Cnt
1 70
2 69
3 62
4 60
5 53
6 63
7 79
8 73
What I'm trying to get is the number of times each number has appeared since the beginning of our lotto. the formula that I use is : =COUNTIF(RANGE:RANGE,"NUMBER"). The 2 spreadsheets are
identical. So why the difference in counting?
Please help
BlouBul
GillesD this is in response of your reply, so please help me before I go on holiday.
I'm starting a fresh but encounter the following problem. I remember PAB stated that some Excel formula's must be constructed different in outher countries. I'm using two office packs. Office 2000 (at work) and Office XP Pro (at home) and it gives different counts. E.G
Office 2000
No Cnt
1 59
2 58
3 55
4 52
5 44
6 54
7 66
8 63
Office XP Pro
No Cnt
1 70
2 69
3 62
4 60
5 53
6 63
7 79
8 73
What I'm trying to get is the number of times each number has appeared since the beginning of our lotto. the formula that I use is : =COUNTIF(RANGE:RANGE,"NUMBER"). The 2 spreadsheets are
identical. So why the difference in counting?
Please help
BlouBul
Hi bloubul,
Hi bloubul,
One Reason that you could be Getting Two Different Sets of Results is that One Spreadsheet ( at Work for Example ) is Using Absolute Formulas and the Other Spreadsheet ( at Home for Example ) is Using Relative Formulas.
If you were to Copy these Formulas Across OR Down this is how the Absolute & Relative Formulas Work :-
Reference, ( Description ) & Changes to
$A$1 ( Absolute Column AND Absolute Row ) Changes to $A$1
A$1 ( Relative Column AND Absolute Row ) Changes to C$1
$A1 ( Absolute Column AND Relative Row ) Changes to $A3
A1 ( Relative Column AND Relative Row ) Changes to C3
So lets Assume that your Lotto Data is in Cells B2:G1001 ( 6 Numbers Drawn for 1,000 Draws ).
In Cells I2:I50 Enter Numbers 1 to 49.
In Cell J2 Enter the Formula :-
=COUNTIF(B$2:G$1001,$I2)
Copy this Formula ( Notice the Cell References ) Down to Cell J50.
This will give you the Numbers 1 to 49 ( in Cells I2:I50 ) and the Associated Total Number of Times Each Number has Appeared ( in Cells J2:J50 ) Since the Beginning of your Lotto. Change the Cell References Accordingly.
You can Use the Formula like this, as Per your Example :-
=COUNTIF(B$2:G$1001,1)
=COUNTIF(B$2:G$1001,2)
=COUNTIF(B$2:G$1001,3)
...
=COUNTIF(B$2:G$1001,49)
But this would Mean that you would have to Physically go through and Change the Number you are Checking, and Really, there is NO Need to do this.
Let me Know how you get on.
All the Best.
PAB
Hi bloubul,
One Reason that you could be Getting Two Different Sets of Results is that One Spreadsheet ( at Work for Example ) is Using Absolute Formulas and the Other Spreadsheet ( at Home for Example ) is Using Relative Formulas.
If you were to Copy these Formulas Across OR Down this is how the Absolute & Relative Formulas Work :-
Reference, ( Description ) & Changes to
$A$1 ( Absolute Column AND Absolute Row ) Changes to $A$1
A$1 ( Relative Column AND Absolute Row ) Changes to C$1
$A1 ( Absolute Column AND Relative Row ) Changes to $A3
A1 ( Relative Column AND Relative Row ) Changes to C3
So lets Assume that your Lotto Data is in Cells B2:G1001 ( 6 Numbers Drawn for 1,000 Draws ).
In Cells I2:I50 Enter Numbers 1 to 49.
In Cell J2 Enter the Formula :-
=COUNTIF(B$2:G$1001,$I2)
Copy this Formula ( Notice the Cell References ) Down to Cell J50.
This will give you the Numbers 1 to 49 ( in Cells I2:I50 ) and the Associated Total Number of Times Each Number has Appeared ( in Cells J2:J50 ) Since the Beginning of your Lotto. Change the Cell References Accordingly.
You can Use the Formula like this, as Per your Example :-
=COUNTIF(B$2:G$1001,1)
=COUNTIF(B$2:G$1001,2)
=COUNTIF(B$2:G$1001,3)
...
=COUNTIF(B$2:G$1001,49)
But this would Mean that you would have to Physically go through and Change the Number you are Checking, and Really, there is NO Need to do this.
Let me Know how you get on.
All the Best.
PAB
Counting problem
As I look at your post, bloubul, one thing came up to my mind.
Data in the second set is always higher than in the first one and almost always by a similar ratio. This can point to a difference in the range used.
A - If your range is not equal in both cases (more draws in the second set), you could expect such a difference.
B - Another possibility could be the bonus number (considered or not). If you add the values you quoted in the first set (59+58+55...), you get 451 and 529 for the second set. If you divide 451 by 6 and multiply by 7, you get 526.2, this is what you would expect if the bonus number was now being considered.
The 2 values 526 and 529 are very near and it could be just a coincidence. But you can verify this by adding up values for all 49 numbers in both sets.
By the way, am I right in assuming that your database has about 460-465 draws?
As I look at your post, bloubul, one thing came up to my mind.
Data in the second set is always higher than in the first one and almost always by a similar ratio. This can point to a difference in the range used.
A - If your range is not equal in both cases (more draws in the second set), you could expect such a difference.
B - Another possibility could be the bonus number (considered or not). If you add the values you quoted in the first set (59+58+55...), you get 451 and 529 for the second set. If you divide 451 by 6 and multiply by 7, you get 526.2, this is what you would expect if the bonus number was now being considered.
The 2 values 526 and 529 are very near and it could be just a coincidence. But you can verify this by adding up values for all 49 numbers in both sets.
By the way, am I right in assuming that your database has about 460-465 draws?