Excel 2007 : Problem with empty spaces in formula

[stephen1310]

I hope I can be clear about the problem I'm having. I'm doing a spreadsheet for my son's baseball team's stats and I have a column for innings pitched. The formula uses the DOLLARFR and DOLLARDE functions to round each 1/3 of an inning to .1 and totals them up correctly. Example - 1/3 + 2/3 + 2/3 = 1 2/3 or 1.2.

The problem I'm having is that this formula is interfering with another formula which may or may not produce an empty cell. The "empty cell" isn't empty, it has another formula inside, just no result, which is calculated from another cell entry.

Start to finish - Each game, I only have to input stats in 1 place, the game. Scrimmage 1 or 2 in this example. The rest of the stats are populated automatically. My innings pitched is calculated fine for each game (Cell B33 - Scrimmage 1 or 2) but the kids individual tabs are a different story. Because of the formulas on the kids individual game logs, the cell isn't empty if the kid doesn't pitch, it just doesn't show a value (cell range B97:B102 in the kids game log). The total innings pitched formula (cell B103 on the kids individual logs or B38 on the Scrimmage Stats tab) gets confused by this and gives a #VALUE! instead of the total innings pitched. Obviously the entire sheet is a little more involved but this part is the only thing I'm having trouble with. It will work perfectly as long as there is a value in each cell for the inning pitched on the kids game log but again, that's all calculated based on my inputs for each game.


Formula for determining who pitched =IF(ISERROR(DGET(S1P,'Scrimmage 1'!B23,$A$5:$A$6))=TRUE,"",DGET(S1P,'Scrimmage 1'!B23,$A$5:$A$6))


Formula for calculating Innings Pitched =DOLLARFR(SUMPRODUCT(INT(B97:B102)+DOLLARDE(MOD(B97:B102,1),3)),3) - For each individual game log

If we have 6 games and a particular kid only pitches 2 of them, it leaves empty cells in the DOLLARFR cell range for his individual game log, which produces a #VALUE! in the cell for the range totals (B103 of individual game log).

How can I make the DOLLARFR formula ignore the empty spaces and just count the number of innings pitched? Like I said, the formula works fine with blanks, it just doesn't like the empty result of the formula. I could go in and manually manipulate it each game but that's more hassle, it seems like there is an easy solution I'm just stumped.

I have attached an example. Reece pitches in game 2 but not game 1, all I have to do is enter the data for each game (Scrimmage 1, 2 etc) and Reece's individual game log is populated as is the Total Season Stats. Cell B38 in the Scrimmage Stats or B103 in the kids game log tabs are the ones that are giving me trouble (they're both the same formula so if the game log gets fixed, it will automatically fix the other one). Cells B24-B37 (Scrimmage Stats) and B97-B102 (kids game log) is the cell range for the formula and the #VALUE! entries in there are confusing the formula in B38 (Scrimmage Stats) or B103 (kids game log). B38 should be showing 6.2 but it isn't, B103 for Reece should be showing 4.2 but it isn't. The same DOLLARFR formula works fine if there is simply no entry, use Scrimmage 1 or 2 tab, cell B33 as an example.

Any help is greatly appreciated.

Stephen