continued ...........................
We need some formulas for the benefit of the macros which will list the ball count maxima, the second maxima and third maxima values per ball. these will fit nicely now on rows60 , 61 and 62.
in cell C60 enter formula:- =MAX(C8:C56) copy this along the row to AY60
the next formula is an ARRAY FORMULA so you enter it by holding down the ctrl and shift keys whilst also pressing <enter>
in cell C61 enter formula =MAX(IF(ISNA(MATCH(C8:C56,C$59:C59,0)),C8:C56))
if you have done it correctly you will see :- {=MAX(IF(ISNA(MATCH(C8:C56,C$59:C59,0)),C8:C56))} curly brackets appear to show its an array formula.
In cell C62 is another ARRAY FORMULA ..enter . =IF(ISERROR(LARGE(C8:C56,COUNTIF(C8:C56,">="&LARGE(C8:C56, COUNTIF(C8:C56, MAX(C8:C56)) + 1))+1)),"N/A",LARGE(C8:C56,COUNTIF(C8:C56,">="&LARGE(C8:C56, COUNTIF(C8:C56, MAX(C8:C56)) + 1))+1))
Again if youve done the three key entry method you will see .... {=IF(ISERROR(LARGE(C8:C56,COUNTIF(C8:C56,">="&LARGE(C8:C56, COUNTIF(C8:C56, MAX(C8:C56)) + 1))+1)),"N/A",LARGE(C8:C56,COUNTIF(C8:C56,">="&LARGE(C8:C56, COUNTIF(C8:C56, MAX(C8:C56)) + 1))+1))}
The reason these are so compilcated is because you cant use the LARGE function to find second and third largest when there are repeats in the list, it doesn't work properly !
If you opted to have an autofilter across the top of the 49 x49 table then these three results should match the top 3 returned by the filter.
These two new formulas need copying across their rows to column AY and they should show sensible readings PROVIDED that the 49 x 49 table above hasn't got a max value less than 1 in it or hasn't got 3 different values in it. Otherwise you will see N/A in some cells. (If 1 is the max, the second max is 0 ..there is no third max ! ) Thats why high values and lots of different values work better here.
As usual, the two newest formula rows need named ranges, so the macros can read them
Name cell B60 next1
Name cell B61 next2
Note that we already have a named range called max which the macros can use, we don't need another. You can label the three cells B60,B61, B62 Max, next1 and next2.
Now for new formulas beneath the mintable :
in cell C126 enter the formula =MIN(C8:C56)
in cell C127 enter the ARRAY formula =MIN(IF(ISNA(MATCH(C8:C56,C$126:C126,0)),C8:C56)) ...should end up with the { } around it (ctrl+shift+enter).
in cell C128 enter the ARRAY formula =IF(ISERR(SMALL(C8:C56,COUNTIF(C8:C56,"<="&SMALL(C8:C56, COUNTIF(C8:C56, MIN(C8:C56))
+1))+1)),"N/A",SMALL(C8:C56,COUNTIF(C8:C56,"<="&SMALL(C8:C56, COUNTIF(C8:C56, MIN(C8:C56)) +1))+1))
again if you entered it correctly you'll see it surrounded by curly brackets. { }
These should return sensibe figures for Min, next higher count, and next higher again. Copy the formulas across their rows to column AY. Again if you get any N/A results they should only happen where the figures in the 49 x49 table don't have three different values in that column.
If you opted to have an autofilter across the top of the 49 x49 table then these three results should match the bottom 3 returned by the filter.
As usual, the two newest formula rows need named ranges, so the macros can read them
Name cell B127 min1
Name cell B128 min2
Note that we already have a named range called min which the macros can use, we don't need another. you can label the three cells B126,B127, B128 Min, min1 and min2
Now there will be times when some cells on Rows 61,62 127,128 will display N/A and the macros need to know this and take action. So we need to check for this condition. If any one cell across those rows contains N/A, we can detect it. We only need to check for the 3rd highest and lowest having this problem.
So in cell AZ62 enter the formula =COUNTIF(C62:AY62,"=N/A") . Name this cell maxerror.
In cell AZ128 enter the formula =COUNTIF(C128:AY128,"=N/A") . Name this cell minerror.
Hopefully these will now have a zero in them, meaning all is well with the finding top and bottom 3
Now the moment has come ... to insert 2 sets of option group buttons. One for maxtable and one for mintable. Each group will have 3 buttons one above the other, such that button1 returns a 1, button 2 returns a 2, button 3 returns a 3. It is important to create them in this order.
First option button group
(Because I will be linking all 3 to cell AH66 and covering it up by the buttons )
I first created abackground colour in the cells where the button group would live, a kind of light grey range AF64:AI67 so thats 4 rows high 4 cols wide.
draw your option group box (which will contain the buttons) over the range AF64:AI67 make it bigger if you like. Now draw your option buttons in order starting from the top. Link all three to cell AH66. If you prefer to choose another cell to link to outside the group to make the result easier to see, then do so. This linked cell must be named choice1.
I located mine inside the group outline, then changed the font to make it invisible (after testing).
Change the text on button1 to read Top3, change button 2 text to Top2, button3 text to Max only.
The option group text can read Max choices.
Test your buttons, when you pick Top3 you get a 1, pick top2 you get a 2, pick Max only you get a 3.
Second option button group
(Because I will be linking to cell AH98 and covering it up by the buttons )
I first created a background colour in the cells where the button group would live, a kind of light grey range AF96:AI99 so thats 4 rows high 4 cols wide.
draw your option group box (which will contain the buttons) over the range AF96:AI99 make it bigger if you like. Now draw your option buttons in order starting from the top. Link all three to cell AH98. If you prefer to choose another cell to link to outside the group, then do so. This linked cell must be named choice.
Change the text on button1 to read Bottom3, change button 2 text to Bottom2, button3 text to Minima only. The option group text can read Minima choices.
Test your buttons, when you pick Bottom3 you get a 1, pick Bottom2 you get a 2, pick Minima only you get a 3.
Okay.. I think we are ready for 2 modified macros. The option buttons will set and run macros CreateVariableMaxlist and CreateVariableMinlist.
continued in the next post.................
