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


0
bs2171 (10)
5/15/2007 6:26:59 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
522 Views

Similar Articles

[PageSpeed] 32

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
> 
> 
> 
0
AKphidelt (33)
5/15/2007 6:35:02 PM
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
> 
> 
> 
0
AKphidelt (33)
5/15/2007 6:38:01 PM
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" <AKphidelt@discussions.microsoft.com> wrote in message
> news:A24F3571-03E1-4E5C-86D2-449DD0EC60AC@microsoft.com...
> > 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
> > >
> > >
> > >
> 
> 
> 
0
AKphidelt (33)
5/15/2007 7:23:01 PM
What are the $ for in formulas? How do they work? I've seen them in formulas
before but don't know why.
Brad


"AKphidelt" <AKphidelt@discussions.microsoft.com> wrote in message
news:A24F3571-03E1-4E5C-86D2-449DD0EC60AC@microsoft.com...
> 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
> >
> >
> >


0
bs2171 (10)
5/15/2007 7:49:30 PM
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" <AKphidelt@discussions.microsoft.com> wrote in message
news:58AC3A8C-539D-42B8-9515-00423A0375A7@microsoft.com...
> 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
> >
> >
> >


0
bs2171 (10)
5/15/2007 8:23:46 PM
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" <AKphidelt@discussions.microsoft.com> wrote in message
news:9EB4626C-FD2E-4771-B1B8-A9F79CBAE8AA@microsoft.com...
> 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" <AKphidelt@discussions.microsoft.com> wrote in message
> > news:A24F3571-03E1-4E5C-86D2-449DD0EC60AC@microsoft.com...
> > > 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
> > > >
> > > >
> > > >
> >
> >
> >


0
bs2171 (10)
5/16/2007 8:08:05 PM
Reply:

Similar Artilces: