Adding Cricket Overs with VBA with parameters

sooshil

Board Regular
Joined
Feb 21, 2013
Messages
104
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi

In Cricket Sports there are 6 legal balls a bowler bowls towards the batsman. Those 6 balls are called an over.
So, the sequence of the balls are counted as follows.
1 ball = 0.1 overs
2 balls = 0.2 overs
3 balls = 0.3 overs
4 balls = 0.4 overs
5 balls = 0.5 overs
6 balls = 1 over

7 balls = 1.1 overs
8 balls = 1.2 overs
9 balls = 1.3 overs
10 balls = 1.4 overs
11 balls = 1.5 overs
12 balls = 2 overs

13 balls = 2.1 overs

and so on

In multiple occasion while working with cricket scores, we need to add two overs scores such as 1.3 (9 balls) and 2.5 (17 balls)
Excel adds up this to 3.8, but we need it to be 4.2.
In 1.3 there are 9 balls and in 2.5 there are 17 balls.
Altogether there 26 balls which which is 4 overs (24 balls) and 0.2 overs (2 balls).

So, I want a VBA function that accepts parameters. As parameters I will inject individual overs and the function should return the sum of those overs.
For example;
=AddOvers(2.3,5.2,1.4,3) should return 12.3

It would be great if this function can take parameters in comma separated values or a given range of values exactly as SUM function takes.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
No need for a UDF, you can do this with Xl functions


Book1
A
91.3
102.5
114.2
End
Cell Formulas
RangeFormula
A11=DOLLARFR(DOLLARDE(A9,6)+DOLLARDE(A10,6),6)
 
Upvote 0
Input is a range of values and it will fail if value after "." is > 5, try:
Code:
Function AddOvers(ByRef rng As Range) As Double

    Dim r   As Range    
    Dim v   As Variant
    
    For Each r In rng
        v = 0
        If InStr(r.Value, ".") > 0 Then v = CLng(Split(r.Value, ".")(1))
        If v > 5 Then GoTo Invalid
        AddOvers = AddOvers + 6 * Int(r.Value) + v
    Next r
    
    AddOvers = Int(AddOvers / 6) + (AddOvers Mod 6) * 0.1
    Exit Function
    
Invalid:
    AddOvers = Err
    
End Function
I recommend @Fluff's suggestion, built in XL functions over UDF
 
Last edited:
Upvote 0
Thank you both for the answers.
The built in function DOLLARFE and DOLLARDE works quite bit. But I have to click every cell I want to add.
The VBA given by JackDanIce also adds up a contagious range.
But my requirement is quite different.
My data can be mix up of contagious range and single cells... something like this =AddOver(B3:B7,B11,C13)
The builtin SUM function can handle such situation. I want something similar.
Any way thanks a lot again.
 
Upvote 0
Unfortunately the Dollarde & dollarfr do not accept arrays.
 
Upvote 0
Hi @Fluff

Can JackDanIce's VBA be modified so that the function AddOvers() can take arguments as SUM function takes?
 
Last edited:
Upvote 0
How about
Code:
Function sooshil(R1 As Range, Optional R2 As Range, Optional R3 As Range) As Double
   Dim Cl As Range
   
   If Not R2 Is Nothing Then Set R1 = Union(R1, R2)
   If Not R3 Is Nothing Then Set R1 = Union(R1, R3)
   
   For Each Cl In R1
      sooshil = sooshil + Application.DollarDe(Cl, 6)
   Next Cl
   sooshil = Application.DollarFr(sooshil, 6)
End Function
This gives a max of three ranges
 
Upvote 0
Another option
Code:
Function sooshil(ParamArray R1() As Variant) As Double
   Dim i As Long
   Dim Itm As Variant

   For i = 0 To UBound(R1)
      For Each Itm In R1(i)
         sooshil = sooshil + Application.DollarDe(Itm, 6)
      Next Itm
   Next i
   sooshil = Application.DollarFr(sooshil, 6)
End Function
This will allow any number of ranges.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top