Need Help With Formula for Baseball Innings Pitched

Hi, I'm new to the Forum, and have found it be a great place for information. Hopefully somebody can help me with my problem. I have had Excel for a few years, but have never tried anything beyond the basic functions.

What I am trying to do is modify my statistics spreadsheet for baseball and softball. I want to have a column that (A) uses .1 and .2 for 1/3 and 2/3 of an inning pitched, and (B) also uses dashes in cells that have no data.

In other words something like this:

If Column D was the Innings Pitched column, D2:D27 would contain some cells with only dashes, and the rest with numbers (example: 7.0, 5.2. 4.1, 0.2, and 6.1). Cell D28 would total all of the numbers, and using the numbers listed in the example, would show 24.0.

I have found several formulas for the .1 and .2 problem, but have not found any that will also ignore text in the column. I have tried converting them, but run into errors when I do. One of the formulas begins to occasionally show things like 21.3 innings pitched (instead of 22.0), and others will drop a certain number of innings when going above certain numbers (for instance, above 45.0 it will drop 2.1 innings).

So far I have found these:

(from here)
=DOLLARFR(SUMPRODUCT(B2:B6+MOD(B2:B6,1)*7/3),3)
(unfortunately, this sometimes produces results like 71.3 innings instead of 72.0)


(from here)
=DOLLARFR(ROUND(SUMPRODUCT(INT(D69:D73)+DOLLARDE(MOD(D69:D73,1),3)),8),3)
(this works, but will not ignore text in cells)



I have tried to modify the second formula listed above by using SUM:

=DOLLARFR(ROUND(SUMPRODUCT(INT(SUM(F6:F37))+DOLLARDE(MOD(SUM(F6:F37),1),3)),8),3)

This works, but at some point will begin dropping innings. For instance, during one series of tests when the innings total went over 45.0, the formula consistently dropped 2.1 innings. If I got to 45.0 and tried to add just 1.0, the result was 43.2 instead of 46.0.

Can anybody help with this?

To attach a Workbook
(please do not post pictures of worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

Par of the solution is to use custom number formatting. If you want zeros to show as dashes, use:

Please Login or Register  to view this content.

Type your zero values; they will show as dashes, but compute as zeros.
Try with your previous formula:

Please Login or Register  to view this content.

Thanks, protonLeah! I would have responded sooner, but I was away from my computer all day yesterday. I've been busily trying custom number formatting since then.

You definitely put me on the right track. The code you posted worked. I also employed the formula that you recommended. I began converting all of stats worksheets to use these today.

The only problem I encountered is that the custom number format code doesn't allow the numbers or dashes to be aligned in the center of the column. I discovered how to add spaces to the code so that the dash will move to the middle of the column, but that didn't help right away with numbers that aren't zeros.

Through some Googling for custom number format, alignment, etc., I finally found a custom number format in this post on another site which both formats "0"s as dashes, and also allows the numbers and dashes to be aligned in the center using the alignment buttons on the ribbon.

The code is:

Please Login or Register  to view this content.

I am now using the formula that you recommended, and the custom number format that I found after you pointed me in the right direction. Now everything now works perfectly!!!