Baseball Innings Pitched

[americanpie3]

Hi everyone,

Is there a way in excel to go to the next digit once 0.3 is reached?

Example. A pitcher throws on Monday and pitches 7.2 innings. The same pitcher throws on Sunday and pitches 5.2 innings. I would like excel to calculate 13.1 innings pitched for the week which is what baseball recognizes instead of 12.4 innings.

Thanks

 

[farmerscott]

Hello,

have you tried the ROUNDUP() function? It should take you to the next whole number.

FarmerScott

 

[Peter_SSs]

Like this?

Excel Workbook
	A	B
1
2	Monday	7.2
3	Sunday	5.2
4	Total	13.1
Innings Pitched

 

[americanpie3]

That might be tough to implement in my formula

=IFERROR(INDEX(PitchingDay1!$M$2:$M$600,MATCH(O23,PitchingDay1!$A$2:$A$600,0)),0)+IFERROR(INDEX(PitchingDay2!$M$2:$M$600,MATCH(O23,PitchingDay2!$A$2:$A$600,0)),0)+IFERROR(INDEX(PitchingDay3!$M$2:$M$600,MATCH(O23,PitchingDay3!$A$2:$A$600,0)),0)+IFERROR(INDEX(PitchingDay4!$M$2:$M$600,MATCH(O23,PitchingDay4!$A$2:$A$600,0)),0)+IFERROR(INDEX(PitchingDay5!$M$2:$M$600,MATCH(O23,PitchingDay5!$A$2:$A$600,0)),0)+IFERROR(INDEX(PitchingDay6!$M$2:$M$600,MATCH(O23,PitchingDay6!$A$2:$A$600,0)),0)+IFERROR(INDEX(PitchingDay7!$M$2:$M$600,MATCH(O23,PitchingDay7!$A$2:$A$600,0)),0)

 

[Peter_SSs]

That might be tough to implement in my formula

Yes, that would be some formula!!

I can see a couple of options.

1. Extract the numbers from the PitchingDay sheets then use my formula structure.

To do that you could use a list of the sheets and an INDIRECT formula like I've shown in C3 below. That formula is copied down to C9.
INDIRECT is a volatile function so may have an impact on your sheet performance if used a lot.
If that is an issue you could use 7 manually edited formulas like I've shown two of in column D.

C11 is my previous formula adapted to this new range.


2. Use a User-Defined-Function (UDF)

Place the following code in a standard Module and use a formula as shown in C13 below.
Note that this UDF has no error-checking and assumes ..
- That your PitchingDay sheets are consecutive sheets within your workbook
- That the ranges to lookup/extract data from in each sheet are the same

The arguments in the function are:
- First and last pitchingday sheet names separated by a colon (& enclosed in double quotes)
- Cell reference (or text enclosed in double quotes) for the value being looked for in each sheet.
- Range address (double quotes) where the value is to be looked for
- Column where the result is to be extracted from in each sheet.

Function Pitched(ShtRng As String, LookupValue As String, LookupRange As String, ResultCol As String) As Double
  Dim ShtStart As Long, ShtEnd As Long, i As Long
  Dim t As Double, p As Double
  Dim Found As Range
  
  Application.Volatile
  ShtStart = Sheets(Split(ShtRng, ":")(0)).Index
  ShtEnd = Sheets(Split(ShtRng, ":")(1)).Index
  For i = ShtStart To ShtEnd
    With Sheets(i)
      Set Found = .Range(LookupRange).Find(What:=LookupValue, LookIn:=xlValues, lookat:=xlWhole, _
        MatchCase:=False, SearchFormat:=False)
      If Not Found Is Nothing Then
        p = Intersect(Found.EntireRow, .Columns(ResultCol)).Value
        t = Int(t) * 3 + (t - Int(t)) * 10 + Int(p) * 3 + (p - Int(p)) * 10
        t = Int(t / 3) + (t Mod 3) / 10
      End If
    End With
  Next i
  Pitched = t
End Function

Excel Workbook
	B	C	D
3	PitchingDay1	7.2	7.2
4	PitchingDay2	0.0	0.0
5	PitchingDay3	1.1
6	PitchingDay4	0.0
7	PitchingDay5	1.0
8	PitchingDay6	3.2
9	PitchingDay7	0.0
10
11	Total	13.2
12
13	Total	13.2
Sheet2