How to calculate best bowling figure (cricket)

**ahmedleo414** · 08-10-2021, 10:29 PM

**DocAElstein** · 08-11-2021, 01:43 AM

Hello ahmedleo414
Welcome to ExcelFox

I personally would not know how to do this with a formula. It may be possible, but could be quite difficult. I am not too good at formulas

Using Excel VBA, there are probably lots of different ways to do it. There are also probably infinite ways to give the output.

It would be easier for us to give you some ideas if you could give us a sample file, instead of an image.

Maybe you could tell us a bit more, as it is not easy to know what help you actually need. Tell us a bit about yourself: What is your knowledge level of Excel and VBA? What/ where do you want the output.
If an Excel VBA solution is acceptable to you, then what should trigger the macro coding? Should it start automatically when new data is added, or do you want to start the macro yourself, ( if you know how to). Do you want a button somewhere to start the macro coding.

**ahmedleo414** · 08-11-2021, 03:58 AM

**DocAElstein** · 08-11-2021, 12:14 PM

Hi
My knowledge of cricket is not so good, but I think I see what you want to do.

From what you have said and comparing your image with your uploaded template file, it looks like Sheet 3 from your Template file ( https://excelfox.com/forum/showthrea...ll=1#post15574 ) is what we are interested in. I have added some sample numbers: (For the next time: Its always a good idea for you to supply some numbers with the typical results you want, as this makes things easier for us to see what you want, and also we can check any macro we do for you).

I will try to guide you a bit , but you will appreciate that VBA code writing is a massive subject, so I can’t do a year of tuition in a forum Thread. But I think it is very commendable that you want to try it yourself. I might have to talk you through code examples to get us moving a bit quicker though.

You have regular sections which simplifies a couple of points
_ more usually in uses of VBA coding, sizes of data may vary and we would need some initial coding to determine any particular data size. But in your case we can ignore that. I am not a computer professional myself, but I think the computer jargon is … we can “hard code” things in your case. This means we may have a lot of fixed numbers in our coding in places where more typically variables would be used. Those variables would initially in the initial coding be set to match the current data size. I might still use variables for those numbers, just as a conventional way of doing things. That’s personal choice really.
Example: You have 20 Matches, so likely some number like 20 is going to be used somewhere, but from personal choice I might decide to define a Long type variable, Mtchs , and assign the value of 20 to it. Other variables associated with data sections wont always be the same value, so using variables is a good idea for those, as we usually want to make a macro efficient and not coding for each section: More likely we would try to arrange that the same coding is used for all sections, and to enable this, somehow the values in the variables would be changed to suit the current section being worked on.
It is usually a good idea to set variables anyway, because, it is very easy for VBA coding to get in a mess and mix up worksheets and cells and end up doing things to the wrong cells. So its usually a good idea to keep variables set to a specific thing towards the start of any macro. Personal choice again, and I would tend to have some initial code section , I would section it under Rem 1 typically. Personal choice again. There are usually hundreds of different ways to write a VBA code to do something.
_ because you have regular sections we can quite easily arrange a macro so that things are automatically updated when data is added into a particular section. I would suggest we leave out making the thing kick in automatically initially, as we can easily modify a macro later to get this to happen.

That should all be more clearer when we've finished

Let me start with a macro for the first section, initially to see if I have understood correctly what you want. Then we can take it from there later, when I pop by again.

Rem 1
This is defining and setting some variables for my personal convenience

'2a
I am getting the max value in column E (wickets). There must be infinite ways to do this.
As Excel has an in built spreadsheet function, I will use that, as I assume the makers of Excel are better programmers than me and their way works very efficiently.
In VBA I can use any Excel spreadsheet function using the VBA Evaluate(“ “) function thing. This allows me to build up in the text string “ “ bit; a string formula: The basic formula I want is something like
=MAX(E2:E21)
In fact that will often work just like, Evaluate("=MAX(E2:E21)")
But that is dangerous, since, as I mentioned, VBA coding can easily get mixed up not knowing where exactly a cell might be, for example it might go to the wrong worksheet.
Also I want to use variables rather than fixed numbers, as I explained above.

'2b
This uses the simplest form of VBA coding to do the basic logic to get the Min value in column D ( Runs ). It is very inefficient as is it interacts a lot with the spreadsheet, and each interaction is like slamming the brakes on in a code run. But it’s the easiest to understand, a simple loop and it is almost self explanatory what’s going on

Rem 3
This simply gives you your wanted output in the correct place. I am using variables rather than hard coding actual numbers, as I am thinking ahead a bit for us, for a final macro which does all sections
( I use a _ rather than a – because Excel has a nasty habit of sometimes thinking that a number set is a date if you use a – which can give you some screwy results. If you must use a – then no problem , but we would need to add some extra stuff to make sure Excel does what we want , and not sometimes what it thinks we want)

Code:

Sub CricketWickets_1() ' https://excelfox.com/forum/showthread.php/2756-How-to-calculate-best-bowling-figure-(cricket)?p=15573&viewfull=1#post15573
Rem 1 Worksheets info
Dim Ws3 As Worksheet: Set Ws3 = ThisWorkbook.Worksheets("Sheet3")
Dim Mtchs As Long: Let Mtchs = 20
Dim FstMtch As Long: Let FstMtch = 2
Dim MxE As Long
Rem 2
'2a   =MAX(E2:E21)
' Let MxE = Evaluate("=MAX(E2:E21)") ' This basic formula will work, if you are lucky, but probably better to do it like in the next line
 Let MxE = Ws3.Evaluate("=MAX(E" & FstMtch & ":E" & FstMtch + (Mtchs - 1) & ")")
'2b
Dim MnD As Long: Let MnD = 9999
Dim Cnt As Long
    For Cnt = FstMtch To FstMtch + (Mtchs - 1) Step 1
     If Ws3.Range("E" & Cnt & "").Value = MxE And Ws3.Range("D" & Cnt & "").Value < MnD Then Let MnD = Ws3.Range("D" & Cnt & "").Value   ' If we are at a maximum E value row And if the value of D in this row is < the current held lowest D value, Then make the current held lowest D value =  to this value  
    Next Cnt
Rem 3 Output
' Let Ws3.Range("M22").Value = MnD & "_" & MxE
 Let Ws3.Range("M" & FstMtch + Mtchs & "").Value = MnD & "_" & MxE '  ... using variables rather than hard coding 22, as I am thinking ahead a bit for us, for a final macro which does all sections
End Sub

See how you get on with that, as a starter. If you run it, it should give you 32_3 in cell M22 using the sample numbers that I added
I am not on the computer so much just now, but I will pop by again tomorrow.

Alan

**ahmedleo414** · 08-11-2021, 11:06 PM

This is brilliant, exactly what I am looking for. I was able to perfectly follow your login until we got to 2b. (I have some troubles with loops so just bare with me)

If I understand it correctly, here is how you did it.

First in 2a, you found the max value from E2-E21 and stored that as MxE

in 2b, you created a loop called Cnt, which will run from FstMtch (in this case 2), until FstMtch+(Mtchs-1) [in this case 21].

for each loop, you added an If statement,

If the Range E & Cnt = MxE (in our case it is 3) and D & Cnt < MnD (which was origanlly set as 99999) then update MnD with the value which is currenly in D & cnt..

then the loop repeats itself until it is over.

I had to write it out in order to fully understand the loop, this is great. I really appreciate your help and your explanation. I should be able to use this and get the values for the rest of the players as well.

Thanks again.

You also mentioned that there is a way to auto update when the values are adjusted, are you able to help me with that, if not it's not necessary, this will do perfectly fine.

**DocAElstein** · 08-12-2021, 04:05 PM

Hi
You have understood the Logic and code working perfectly.
( I also like to write out in words what I am doing, to help me remember later what I did. I find the green comments that can go a long way off to the right useful. But that is all personal choice. Most people and all advanced programmers find the green comments annoying )
( Cnt would often be referred to as something like the loop bound index variable )
You probably guessed that MnD is set initially to some arbitrary large number, because otherwise its default value is 0 and the logic won’t work – It will always stay at zero.

Bear in mind that there will be thousands of different ways in VBA to do what you want to do. This way I chose was the first simple idea I came up with.

Originally Posted by ahmedleo414

...... a way to auto update when the values are adjusted.....

OK, Lets talk about automating stuff…

You know that we have coding that helps us do things, and that, approximately speaking, we can say that the coding is kept in a “code module”. We can have lots of code modules, and the code modules you will be familiar with are sometime referred to as “normal code modules”

You may not know that we can also approximately say that Excel, or rather Microsoft Office, is just lots of coding in code modules. In allowing us access to VBA coding, Microsoft is letting us use the very stuff they use to make Microsoft Office work.
Microsoft prefer not to show us all their code modules and / or all their VBA coding in them, because otherwise we could just copy it all, run it, and never have to buy their software.

Microsoft let us see their worksheets object code modules. They don’t show us all their coding in them.
They do let us add some more coding into some of their existing macros, in particular to those macros often referred to as something like “Event coding” or “Event macros”.
For example, we can put some coding in the macro that kicks in every time something is changed in a worksheet.

The very simplest solution for your automation would be to put the existing coding I did for you in that existing Microsoft “Event macros”.
I have done that in the uploaded workbook , Stats_Template SimpleEvent.xls
That works, but it’s very inefficient.
In the next post ( https://excelfox.com/forum/showthrea...ll=1#post15579 ) , I will take it a bit further, coming a bit closer to the more usual way of doing things

Here are a few picks illustrating how I went about making Stats_Template SimpleEvent.xls

Right Click View Sheet3 object coding.JPG http://i.imgur.com/wmXpTdW.jpg
Right Click View Sheet3 object coding.JPG

Sheet3 Object Code Module.JPG http://i.imgur.com/dfgmGQo.jpg
Sheet3 Object Code Module.JPG

Select Worksheet macros.JPG http://i.imgur.com/ltVwoMH.jpg
( Ignore the Worksheet_SelectionChange macro that may open up )
Select Worksheet macros.JPG

Open worksheet change macro.JPG http://i.imgur.com/nHUUnAe.jpg
Open worksheet change macro.jpg

**Paste Coding inside existing Worksheet Change macro.JPG http://i.imgur.com/eiW8lgc.jpg
** Don’t include the first and last line bits ( – miss out Sub CricketWickets_1() and End Sub )

That is almost all you have to do. There is just one problem. A big problem. This problem catches people out a lot when using these sorts of macros.
To explain:-
This coding we see is just a text copy of the coding that is run every time a change is made to the worksheet.
The macro that runs writes a value into the worksheet ( it pastes into cell M22 ). That changes the worksheet. That causes another copy of our macro to run. That macro writes a value into the worksheet ( it pastes into cell M22 ). That changes the worksheet. That causes another copy of our macro to run. That macro writes a value into the worksheet ( it pastes into cell M22 ). That changes the worksheet. That causes another copy of our macro to run. That macro writes a value into the worksheet ( it pastes into cell M22 ). That changes the worksheet. That causes another copy of our macro to run. That macro writes a value into the worksheet ( it pastes into cell M22 ). That changes the worksheet. That causes another copy of our macro to run. That macro writes a value into the worksheet ( it pastes into cell M22 ). ……….. and so on, going on for ever, or more likely until either your computer or Excel crashes!!!!
We overcome this problem by temporarily disabling the event coding around the line that causes the problem. So we replace this

Code:

 Let Ws3.Range("M" & FstMtch + Mtchs & "").Value = MnD & "_" & MxE '  ... using variables rather than hard coding 22, as I am thinking ahead a bit for us, for a final macro which does all sections

With something like this

Code:

 Let Application.EnableEvents = False
 Let Ws3.Range("M" & FstMtch + Mtchs & "").Value = MnD & "_" & MxE '  ... using variables rather than hard coding 22, as I am thinking ahead a bit for us, for a final macro which does all sections
 Let Application.EnableEvents = True

Setting EnableEvents to false, effectively tells Excel not to start any more Event type macros. It's important to set it back to True, or else the macro will not run again the next time you change something in the worksheet. Whilst set to False , the problem code line will not cause another copy of the macro to be started

**DocAElstein** · 08-12-2021, 04:09 PM

_...... continued from last post https://excelfox.com/forum/showthrea...ll=1#post15578

In most practical situation that last event coding would likely cause problems as it will be running many times unnecessarily: It runs every time you change anything anywhere in Sheet 3 ( This is not the problem discussed at the end of the last post: It's a seperate issue )

We would generally prefer to limit the coding to when we change something in a restricted area. We can achieve that in many ways.
Most conveniently, Excel gives us a way to know in the coding which range was changed: Just before the Event macro runs, the range object of the range changed is assigned to that variable Target in the first code line, Private Sub Worksheet_Change(ByVal Target As Range)
Often towards the start of the macro we would have some If thing to see if the area of interest was changed. I have done that in the uploaded file, Stats_Template Event_1.xls

In the macro I have done for you, the important line is this one:

Code:

   If Application.Intersect(Target, DtaRng) Is Nothing Then ' ------------------------------------------------------------

To explain
I have arranged that DtaRng is the range of column D and E with your run and wicket data in it.
Target , as I explained, is assigned by Excel already to be the range that was changed.
The Intersect function is used to return a range object equal to that where any supplied ranges ( in our case DtaRng and Target) coincide ( where they overlap or intersect ). If, however the ranges don’t overlap, then the Intersect function returns us Nothing. The range you change , Target , and the data range, DtaRng , will not overlap ( intersect ) if you did not change a cell in the data range.
So I am testing to see if it returns nothing.
__ If it returns Nothing, Then I Do Nothing
__ Otherwise ( Else ) the coding is done.

Here the full macro:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)   ' Target will be filled just before this macro starts , with the range object of the cell or cells that were changed
Rem 0 In this section are some things and ideas that might be useful if we extend this coding to an efficient macro for all sections.
Dim N As Long ' For this macro this is a single value.  It might be a loop bound index variable in a further macro.
 Let N = 1
Dim StrtRw As Long: Let StrtRw = ((N - 1) * 23) + 1 '
Dim Rws As Long: Let Rws = 21
Dim DtaRng As Range: Set DtaRng = Range("D" & StrtRw + 1 & ":E" & StrtRw + (Rws - 1) & "") ' this will be the data range  in columns D and E in a section
    
   If Application.Intersect(Target, DtaRng) Is Nothing Then ' ------------------------------------------------------------
   ' Do Nothing
   Else ' We will come here if we changed something in DtaRng, because there was a overlap / (intersect) in Target and the data range, DtaRng
    Rem 1 Worksheets info
    Dim Ws3 As Worksheet: Set Ws3 = ThisWorkbook.Worksheets("Sheet3")
    Dim Mtchs As Long: Let Mtchs = 20
    Dim FstMtch As Long: Let FstMtch = 2
    Dim MxE As Long
    Rem 2
    '2a   =MAX(E2:E21)
    ' Let MxE = Evaluate("=MAX(E2:E21)") ' This basic formula will work, if you are lucky, but probably better to do it like in the next line
     Let MxE = Ws3.Evaluate("=MAX(E" & FstMtch & ":E" & FstMtch + (Mtchs - 1) & ")")
    '2b
    Dim MnD As Long: Let MnD = 9999
    Dim Cnt As Long
        For Cnt = FstMtch To FstMtch + (Mtchs - 1) Step 1
         If Ws3.Range("E" & Cnt & "").Value = MxE And Ws3.Range("D" & Cnt & "").Value < MnD Then Let MnD = Ws3.Range("D" & Cnt & "").Value
        Next Cnt
    Rem 3 Output
    ' Let Ws3.Range("M22").Value = MnD & "_" & MxE
     Let Application.EnableEvents = False
     Let Ws3.Range("M" & FstMtch + Mtchs & "").Value = MnD & "_" & MxE '  ... using variables rather than hard coding 22, as I am thinking ahead a bit for us, for a final macro which does all sections
     Let Application.EnableEvents = True
    End If ' -------------------------------------------------------------------------------------------------------------

End Sub

I am happy to help further and / or explain anything further, as long as you are not in a hurry, as I am only sporadically near the forum just now
( By the way, if you are new to forums, you may not know that there are some other more busy forums such as excelforum.com and mrexcel.com. These are often better if you need a quick answer as there are lots of people there all the time eager to give a quick solution. You may not always get such detailed explanation. If you have the time and want to learn more, then probably here at excelfox.com is better, but just now there are less people here, so you probably won’t get a quick response. )

Alan

**ahmedleo414** · 08-13-2021, 01:59 AM

Originally Posted by DocAElstein

I am happy to help further and / or explain anything further, as long as you are not in a hurry, as I am only sporadically near the forum just now
( By the way, if you are new to forums, you may not know that there are some other more busy forums such as excelforum.com and mrexcel.com. These are often better if you need a quick answer as there are lots of people there all the time eager to give a quick solution. You may not always get such detailed explanation. If you have the time and want to learn more, then probably here at excelfox.com is better, but just now there are less people here, so you probably won’t get a quick response. )

Alan

I am in no rush at all, this is just for my personal use on some league that I am running.

I am still going through everything you have explained in your last posts, just so I can get a full understanding of what is going on. This has been extremely helpful and I really do appreciate you taking the time explaining everything in detail

**Chidambaram3983** · 09-02-2021, 09:59 AM

Originally Posted by ahmedleo414

I need to figure out a way to calculate the best bowling figure, Most wickets taken with least number of runs

In this case the best figure would be from Match 3, and the result would be 32-3

Hi,

Try This
IF YOU ARE USING OFFICE 365 NO NEED TO PRESS CTRL + SHIFT + ENTER OTHERWISE AFTER ENTERING FORMULA PRESS CTRL + SHIFT + ENTER

=SMALL(IF(E2:E21=MAX(E2:E21),$D$2:$D$21,""),1)&"-"&MAX(E2:E21)

Code for UDF

Function BBI(wkts_range As Range, runs_range As Range) As String
Dim runs_array As Variant
ReDim runs_array(1 To wkts_range.Count)
maxwkt = Application.WorksheetFunction.Max(wkts_range)
Row = 1
For i = 1 To wkts_range.Count
If wkts_range.Cells(i, 1) = maxwkt Then
runs_array(Row) = runs_range.Cells(i, 1)
Row = Row + 1
End If
Next i
minruns = Application.WorksheetFunction.Min(runs_array)
BBI = minruns & "-" & maxwkt
End Function

Code:

Function BBI(wkts_range As Range, runs_range As Range) As String
Dim runs_array As Variant
ReDim runs_array(1 To wkts_range.Count)
    maxwkt = Application.WorksheetFunction.Max(wkts_range)
    Row = 1
    For i = 1 To wkts_range.Count
        If wkts_range.Cells(i, 1) = maxwkt Then
            runs_array(Row) = runs_range.Cells(i, 1)
            Row = Row + 1
        End If
    Next i
minruns = Application.WorksheetFunction.Min(runs_array)
BBI = minruns & "-" & maxwkt
End Function

=bbi(E2:E21,D2:D21)