Excel Worksheet Question

[the Saint]

I'm putting the finishing touches on a spreadsheet for fantasy football and have one problem. I want an average for weekly scores as the season progresses. I typed the formula in for averages (=AVERAGE(D5:K5, D23:L23), but that gives me a figure divisible by 17.

How do I keep a number input into one of those positions (zero, because that week of the season is yet to be played), from figuring into the above equation. For instance, after week four I just want the cumulative point total divided by four.

Anyone got an answer?

 

[mhayden]

While I'll hammer out how to do it if you really need me to (though I suspect someone will beat me to it), why not just leave weeks that don't have scores blank so they don't average in rather than making them 0?

 

[LosIllini]

Can you just sum the data points and divide by the week number? ie- SUM(D5:K5, D23:L23) / Week #?

 

[HoustonHorn]

This might be kind of sloppy, but would it be possible to create an extra row that would tabulate how many non-zero values there are.

For example, and I may have the excel syntax wrong, but it should be something like =if(D5=0,0,1).

I don't know if this will work, but for example.

Week 1... 2... 3... 4... 5... 6
score 80. 75. 84. .0. . 0. . 0 sum(80,75,84,0,0,0) 239
xRow .1 .. 1 .. 1.. 0 .. 0 .. 0 sum(1,1,1,0,0,0) 3
Average AVG(sumscore/sumxRow)

That way, you have a single cell at the end that totals the value from the extra row and calculates the number of non-zero values you have, which is the number you want to divide by. To clean it up, simply hide that row/column.

Explaining excel is difficult, so if you want to e-mail me the file, I'll take a look at it. Shoot me a PM

 

[Agent007]

or use the count function.