detect the numbers in each group, number the
- Thread starter jack
- Start date
Frank
Member
Jack I notice that the last few projects you have posted have all done the same thing. Find how many balls fall into a certain 'family' of balls and count them.
Instead of expecting someone else to do the work, isnt it time you learned to do it yourself ? If you had tried when I first joined this forum nine years ago you'd be pretty good by now and less demanding of others.
Theres always more than one way of doing things in Excel, I would normally use tha MATCH function but it has its complications when it doesnt find a match, so instead a beginner can just as easily use the COUNTIF function. Put simply, COUNTIF counts how many times a number or a letter occurs in a range. That is EXACTLY what you want to know.
If you enter =COUNTIF($A5:$G5,I$4) in cell I5, it is asking how many times the contents of cell I$4 appears in the range $A5:$G5. As it is a result range, it can only appear either once or zero times. The result will be either 0 or 1.
Do you know the importance of $ signs in formulas ? Normally if you copy a formula along a row or down a column, Excel thinks that you want to change the formula addresses to match how far to the left or right or up and down you have just copied.
Often you dont want that to happen, as in this case. What you want when you copy cell I5 along the row to S5, is the ball number being tested (on row 4) to change to match which column the formula is in. So the column part of the address I4 is left without a dollar sign in front of it, to allow it to change with formula position.
But when you copy cell I5 downwards you DO want the fomula to be 'anchored' on checking the value on row 4, regardless of how far down the sheet the formula is copied. So you need to use a $ sign before the 4 in cell I4. That is why the formula contains I$4.
For the same reason, when you copy formulas along the rows you need to anchor the result range COLS A to G in your formula. Thats why the range being counted $A5:$G5 has the dollar signs before the column letters, it anchors the range regardless of where you copy along to row to..
So if you copy cell I5 across to column S, then copy range I5:S5 down the spreadsheet, you will notice that (if you check any formula)
1. the range being counted is always within columns A and S
2. the ball being checked is always the one directly above on row 4.
You will create a grid of 0's and 1's where the 1 replaces your X.
Sometimes the 0's can be a distraction and there are various ways of making them disappear. One way is to format the range to make them invisible. (you can google how to do that).
Another way is to test the result to see if its going to be zero and if so, replace it with "" which is an empty cell. The IF statement does the test, format is =IF(test critera, do this, or do something else).
The formula in cell I5 could be replaced with =IF(COUNTIF($A5:$G5,I$4)>0,1,""). The test condition asks "is COUNTIF($A5:$G5,I$4)>0 ?
If it is ... enter 1 in the cell. Otherwise enter "" in the cell. If you use this instead of the first formula, you will just get a grid of 1's, when you copy across the columns and then copy that row of formulas down the sheet.
That one formula can be copied and pasted to cell U5 and also copied and pasted to cell AF5. Those cells can then be copied across and downwards to complete the checking grids.
All that remains is to SUM the number of 1's on the relevant rows of the families. The formula =SUM(I5:S5) in cell T5 does the first row of the yellow zone. Note no dollar signs needed here. you can copy that cell downwards. I'll leave you to figure out how to sum the blue and green zones.
The information in columns AR to AT are just copies of the sum results in columns T AE and AP. So in cell AR5 you need =T5. You can figure the rest.
I hope you realise Jack that it has taken me four times longer to write this, than it would have to just "do it" and upload to mediafire. Its cruel to be kind, don't you think its time you took Excel seriously?
Instead of expecting someone else to do the work, isnt it time you learned to do it yourself ? If you had tried when I first joined this forum nine years ago you'd be pretty good by now and less demanding of others.
Theres always more than one way of doing things in Excel, I would normally use tha MATCH function but it has its complications when it doesnt find a match, so instead a beginner can just as easily use the COUNTIF function. Put simply, COUNTIF counts how many times a number or a letter occurs in a range. That is EXACTLY what you want to know.
If you enter =COUNTIF($A5:$G5,I$4) in cell I5, it is asking how many times the contents of cell I$4 appears in the range $A5:$G5. As it is a result range, it can only appear either once or zero times. The result will be either 0 or 1.
Do you know the importance of $ signs in formulas ? Normally if you copy a formula along a row or down a column, Excel thinks that you want to change the formula addresses to match how far to the left or right or up and down you have just copied.
Often you dont want that to happen, as in this case. What you want when you copy cell I5 along the row to S5, is the ball number being tested (on row 4) to change to match which column the formula is in. So the column part of the address I4 is left without a dollar sign in front of it, to allow it to change with formula position.
But when you copy cell I5 downwards you DO want the fomula to be 'anchored' on checking the value on row 4, regardless of how far down the sheet the formula is copied. So you need to use a $ sign before the 4 in cell I4. That is why the formula contains I$4.
For the same reason, when you copy formulas along the rows you need to anchor the result range COLS A to G in your formula. Thats why the range being counted $A5:$G5 has the dollar signs before the column letters, it anchors the range regardless of where you copy along to row to..
So if you copy cell I5 across to column S, then copy range I5:S5 down the spreadsheet, you will notice that (if you check any formula)
1. the range being counted is always within columns A and S
2. the ball being checked is always the one directly above on row 4.
You will create a grid of 0's and 1's where the 1 replaces your X.
Sometimes the 0's can be a distraction and there are various ways of making them disappear. One way is to format the range to make them invisible. (you can google how to do that).
Another way is to test the result to see if its going to be zero and if so, replace it with "" which is an empty cell. The IF statement does the test, format is =IF(test critera, do this, or do something else).
The formula in cell I5 could be replaced with =IF(COUNTIF($A5:$G5,I$4)>0,1,""). The test condition asks "is COUNTIF($A5:$G5,I$4)>0 ?
If it is ... enter 1 in the cell. Otherwise enter "" in the cell. If you use this instead of the first formula, you will just get a grid of 1's, when you copy across the columns and then copy that row of formulas down the sheet.
That one formula can be copied and pasted to cell U5 and also copied and pasted to cell AF5. Those cells can then be copied across and downwards to complete the checking grids.
All that remains is to SUM the number of 1's on the relevant rows of the families. The formula =SUM(I5:S5) in cell T5 does the first row of the yellow zone. Note no dollar signs needed here. you can copy that cell downwards. I'll leave you to figure out how to sum the blue and green zones.
The information in columns AR to AT are just copies of the sum results in columns T AE and AP. So in cell AR5 you need =T5. You can figure the rest.
I hope you realise Jack that it has taken me four times longer to write this, than it would have to just "do it" and upload to mediafire. Its cruel to be kind, don't you think its time you took Excel seriously?
hello frank, I agree, it's me I have to miss in excel it takes time,
* the more experienced make it easy, I delay a life, then the forum is for this same, as it says, link, excel help, it may be that one day you hit the maximum prize there will certainly be rewarded, I know that much benefit from any idea of mine
* and your help in excel asim voce exercita
thoughts, the problem is when Frank leaves, but it will be a long time
* thank you frank
* the more experienced make it easy, I delay a life, then the forum is for this same, as it says, link, excel help, it may be that one day you hit the maximum prize there will certainly be rewarded, I know that much benefit from any idea of mine
* and your help in excel asim voce exercita
thoughts, the problem is when Frank leaves, but it will be a long time
* thank you frank