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

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

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

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.


"Brad" wrote:

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

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

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

"Brad" wrote:

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

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

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" wrote:

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


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

"Brad" wrote:

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

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

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


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

"Brad" wrote:

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

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

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" wrote in message
...
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.


"Brad" wrote:

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

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

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

"AKphidelt" wrote in message
...
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" wrote:

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


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

"Brad" wrote:

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