Need help with Excel...

[kjasmin]

Anyone in here really good with formulas in Excel? Trying to create a bowling league spreadsheet to store individual, team stats and game scores... might even expand it to a iphone app.

 

[Golf4food]

I'm decent. What are you trying to do.

 

[kjasmin]

Tx for the response. So the league last 30 weeks, and 3 games per week. I am trying to average out the scores per games bowled and not by week. Unfortunately the AVERAGE expression would look up all the columns, add them and then divide based on amount of weeks. I only want to average them if each row (weekly game) has scores and average those scores if 0 then ignore. This make sense?

 

[Hercules10]

I'm not too blown with EXCEL...but try the =VLOOKUP() formula.

 

[Jestocost]

In one cell, do =countif(range,">0")

For range you will want to include the cells that have the scores in them, so like A2:G2 would include cells in row two, columns A through G. The countif formula will give you a total of the cells in which a score is entered. You could label that column Total Games Bowled, or something. Let's say this goes in cell H2 at the end of the row.

In the cell to the right of that column, do =sum(range)/H2

The range in this would be the same as the range in the formula above, so it would total all scores listed. Then it would divide by H2, which is the number of games bowled.

 

[kjasmin]

In one cell, do =countif(range,">0")

For range you will want to include the cells that have the scores in them, so like A2:G2 would include cells in row two, columns A through G. The countif formula will give you a total of the cells in which a score is entered. You could label that column Total Games Bowled, or something. Let's say this goes in cell H2 at the end of the row.

In the cell to the right of that column, do =sum(range)/H2

The range in this would be the same as the range in the formula above, so it would total all scores listed. Then it would divide by H2, which is the number of games bowled.

PERFECT!!! thanks... Question: Is there a way to make this one expression vs using 2 cells? example: =SUM((D231)/(COUNTIF(D231,">0")))

 

[Jestocost]

PERFECT!!! thanks... Question: Is there a way to make this one expression vs using 2 cells? example: =SUM((D231)/(COUNTIF(D231,">0")))

I'm not sure that will work, but there's certainly no harm in trying. You could always hide the column containing the number of games bowled so that it doesn't show up, but it's still there for purposes of the calculation.

And if you try, I think the syntax most likely to work would be:

=SUM(D231)/COUNTIF(D231,">0")

 

[kjasmin]

=SUM(D231)/COUNTIF(D231,">0")

This worked... tx again

 

[pl71]

Unfortunately the AVERAGE expression would look up all the columns, add them and then divide based on amount of weeks. I only want to average them if each row (weekly game) has scores and average those scores if 0 then ignore. This make sense?

If I understand the question correctly, the simple solution is to use the AVERAGE function but don't use 0 for games not bowled, leave the cell blank. Blank cells are ignored in the average function. For example, if you average 2 cells with the numbers 0 and 1, the average is .5, if you average those same 2 cells using a blank and 1, the average will be 1.

 

[kjasmin]

If I understand the question correctly, the simple solution is to use the AVERAGE function but don't use 0 for games not bowled, leave the cell blank. Blank cells are ignored in the average function. For example, if you average 2 cells with the numbers 0 and 1, the average is .5, if you average those same 2 cells using a blank and 1, the average will be 1.

This is true and also worked... But the game value also has impact to scratch, handicap, and total of scratch handicapp which has default amount so value could not be blank. That is why I could not use just the average function

 

[kjasmin]

Part 2... I have completed the template (Thanks all!!!)... How do I apply this the main template to the rest of the workbook? So that, a: if changes are needed all I have to change is the template and this will update the rest of the tabs within the workbook, and b: spreadsheet layout stays the same through out the worksheet. Hope this makes sense...