I need a formula to calculate 2-3 columns but skip a column if it has a zero

[Brad]

I'm building a Golf League spreadsheet and we want to calculate handicaps
starting with the second week (calculates 2 weeks) and then after the 3rd
week calculates every 3 weeks.
Columns B-S are the individual weeks scores for each golfer (golfers listed
by row 4-33), each column (b-s) has a formula which pulls the golfers score
from the input worksheet(weekly input) to this worksheet(scorecard).
Starting with Column V we are trying to have the running handicap for the
year which changes each week.
Column W is the first week handicap =AVERAGE(B4-36)*0.8 (this ones easy)
Column X is the second week handicap =SUM(AVERAGE(B4:C4)-36)*0.8 (starting
here we get zeros)
Column Y-AN are the rest of the weeks, we want to calculate every 3 weeks
for handicap =SUM(AVERAGE(B44)-36)*0.8, =SUM(AVERAGE(C4:E4)-36)*0.8,
=SUM(AVERAGE(D4:F4)-36)*0.8 etc.

The problem or question I have is how can I avoid a cell when it has a zero
in it when someone doesn't show up to play? I am trying to avoid any manual
overrides to the spreadsheet and calculations.
We just want to calculate their handicap when they play and not have a
missed night affect their handicap.
Can this be done through a formula of some kind?

Brad

 

[Guest]

You might be able to do something like this

=((SUM($B$4:C4)/COUNTIF($B$4:C4,">0"))-36)*.8

IF you leave the $ signs on $B$4 then you can copy and paste across the
columns increasing the Range by 1 column. LMK if this works.

 

[Guest]

My bad on the $ signs, I didn't read far enough. Formula should still work
though

 

[Guest]

The $ keep Absolute References to the cells. So for a basic example... if you
have values in A1:A10 you can do

In B1 type =$A$1 then copy that and paste it down to B10... and all cells
from B1 to B10 well Equal the value of A1

If you went in B1 and type =A1 and paste it down to B10... the it will
change... so in B7 your formula would be =A7.

You can also have something like $A1... which keeps the column reference as
A no matter where you paste that formula.

 

[Brad]

What are the $ for in formulas? How do they work? I've seen them in formulas
before but don't know why.
Brad

 

[Brad]

Excellent!! Yes, that works. I removed the "$" and I was able to copy and
paste (drag) the formula down or across my worksheet. So far, so good.
Thank You very much.
Brad

 

[Brad]

AKphidelt,

THANK YOU very much for explaining that. I tried to find some information
about the use of the "$" but couldn't find it. Maybe I was looking in the
wrong places. That helps me to understand the formulas and how I've seen
them written and how they can / could be used.
Again, Thank You for clarifing how it's used. Also, your formula you gave me
is working great.

Brad