Adding Cricket Overs with VBA with parameters

[sooshil]

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.

 

[Fluff]

No need for a UDF, you can do this with Xl functions

Book1
	A
9	1.3
10	2.5
11	4.2
End

Cell Formulas
Range	Formula
A11	=DOLLARFR(DOLLARDE(A9,6)+DOLLARDE(A10,6),6)

 

[JackDanIce]

Input is a range of values and it will fail if value after "." is > 5, try:

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

 

[sooshil]

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.

 

[Fluff]

Unfortunately the Dollarde & dollarfr do not accept arrays.

 

[sooshil]

Hi @Fluff

Can JackDanIce's VBA be modified so that the function AddOvers() can take arguments as SUM function takes?

 

[Fluff]

How about

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

 

[Fluff]

Another option

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.