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

  • Thread starter Thread starter Brad
  • Start date Start date
B

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(B4:D4)-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
 
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.
 
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.
 
What are the $ for in formulas? How do they work? I've seen them in formulas
before but don't know why.
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
 
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
 
Back
Top