not sure if I'm using the correct formula for the result I want
 

I'm trying to calculate a 'low net' score. A persons 'actual score' minus
'their handicap' = 'low net' score.

I have this worksheet setup like this;

A4:A23 = list of names
Columns B-U have the weekly calculated low net scores
B4:U23 = the calculated 'low net' scores using this formula
=SUM(Scorecard!B4-Scorecard!Y4); (this is for player one)
Scorecard!B4 is player one's first week 'actual score', Scorecard!Y4 is
player one's calculated 'handicap'.

In Row B25:U25 I have this formula {=MIN(IF(B$4:B$230,B$4:B$23))}, to pull
the lowest score of all persons for that week.

My problem is this;
I'm getting the #DIV/0 error and I don't know how to get around that because
we do have occational zero's "0" for scores when people don't show up. The
error is coming from my "handicap" formula
=((SUM(B4:C4)/COUNTIF(B4:C4,"0"))-36)*0.8

I have two bits of data to work around;
How can I write a formula to work above zero AND ignore the #DIV/0 cells?
I'm referring to B25:U25 formula.

Thanks,
Brad

not sure if I'm using the correct formula for the result I want
 

First let's tidy up =SUM(Scorecard!B4-Scorecard!Y4)
There is no need to use SUM when doing simple math operations, so use
=Scorecard!B4-Scorecard!Y4

Your problem with =((SUM(B4:C4)/COUNTIF(B4:C4,"0"))-36)*0.8 arises when the
two values B4 and C4 are both zero?
How about =IF(COUNTIF(B4:C4,"0")0,
((SUM(B4:C4)/COUNTIF(B4:C4,"0"))-36)*0.8, "")
Alternative =IF(OR(B40,C40), ((SUM(B4:C4)/COUNTIF(B4:C4,"0")-36))*0.8,
"")

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Brad" wrote in message
...
I'm trying to calculate a 'low net' score. A persons 'actual score' minus
'their handicap' = 'low net' score.

I have this worksheet setup like this;

A4:A23 = list of names
Columns B-U have the weekly calculated low net scores
B4:U23 = the calculated 'low net' scores using this formula
=SUM(Scorecard!B4-Scorecard!Y4); (this is for player one)
Scorecard!B4 is player one's first week 'actual score', Scorecard!Y4 is
player one's calculated 'handicap'.

In Row B25:U25 I have this formula {=MIN(IF(B$4:B$230,B$4:B$23))}, to
pull
the lowest score of all persons for that week.

My problem is this;
I'm getting the #DIV/0 error and I don't know how to get around that
because
we do have occational zero's "0" for scores when people don't show up. The
error is coming from my "handicap" formula
=((SUM(B4:C4)/COUNTIF(B4:C4,"0"))-36)*0.8

I have two bits of data to work around;
How can I write a formula to work above zero AND ignore the #DIV/0 cells?
I'm referring to B25:U25 formula.

Thanks,
Brad

not sure if I'm using the correct formula for the result I want
 

Bernard,

I see what you're say about eliminating the #DIV/0 from one of my
worksheets, the weekly formula I have calculated/setup is for the whole
season (thru to 9/19/09, twenty weeks 'B' thru 'U').

And yes I've noticed the "two weeks in a row" problem with that error also,
that was my first occurance of that error, Thanks for explaining why.

Now my question I have is this, based on that error message being there;
How can I write a formula that would ignore that error but still read the
rest of the column/cell data?

Based on this formula I'm currently trying to use:
"In Row B25:U25 I have this formula {=MIN(IF(B$4:B$230,B$4:B$23))}, to
pull the lowest score of all persons for that week."


* The problem I currently have is IF there is a #DIV/0 error, my result for
that formula is the #DIV/0 error. Is there a formula that ignores that error
and would give me an accurate result?



** The formula {=MIN(IF(B$4:B$230,B$4:B$23))}, continues B25 thru U25, so I
have 20 weeks to have someone 'not' show up two weeks in a row.



*** I substituted your formula =IF(COUNTIF(B4:C4,"0")0,
((SUM(B4:C4)/COUNTIF(B4:C4,"0"))-36)*0.8, ""), for my formula on my
"Scorecard" worksheet and it got rid of the #DIV/0 errors for the 'unplayed
future' weeks, (over two 0's in a row), but now my "Low Net" worksheet error
changed from #DIV/0 to #Value!. (Low Net sheet references my Scorecard
sheet)





I hope that makes a little more sense,

Brad






"Bernard Liengme" wrote in message
...
First let's tidy up =SUM(Scorecard!B4-Scorecard!Y4)
There is no need to use SUM when doing simple math operations, so use
=Scorecard!B4-Scorecard!Y4

Your problem with =((SUM(B4:C4)/COUNTIF(B4:C4,"0"))-36)*0.8 arises when
the two values B4 and C4 are both zero?
How about =IF(COUNTIF(B4:C4,"0")0,
((SUM(B4:C4)/COUNTIF(B4:C4,"0"))-36)*0.8, "")
Alternative =IF(OR(B40,C40), ((SUM(B4:C4)/COUNTIF(B4:C4,"0")-36))*0.8,
"")

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Brad" wrote in message
...
I'm trying to calculate a 'low net' score. A persons 'actual score' minus
'their handicap' = 'low net' score.

I have this worksheet setup like this;

A4:A23 = list of names
Columns B-U have the weekly calculated low net scores
B4:U23 = the calculated 'low net' scores using this formula
=SUM(Scorecard!B4-Scorecard!Y4); (this is for player one)
Scorecard!B4 is player one's first week 'actual score', Scorecard!Y4 is
player one's calculated 'handicap'.

In Row B25:U25 I have this formula {=MIN(IF(B$4:B$230,B$4:B$23))}, to
pull
the lowest score of all persons for that week.

My problem is this;
I'm getting the #DIV/0 error and I don't know how to get around that
because
we do have occational zero's "0" for scores when people don't show up.
The
error is coming from my "handicap" formula
=((SUM(B4:C4)/COUNTIF(B4:C4,"0"))-36)*0.8

I have two bits of data to work around;
How can I write a formula to work above zero AND ignore the #DIV/0 cells?
I'm referring to B25:U25 formula.

Thanks,
Brad

not sure if I'm using the correct formula for the result I want
 

If you still have not found a fix, send me (private email a sample file with
the problem
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Brad" wrote in message
...
Bernard,

I see what you're say about eliminating the #DIV/0 from one of my
worksheets, the weekly formula I have calculated/setup is for the whole
season (thru to 9/19/09, twenty weeks 'B' thru 'U').

And yes I've noticed the "two weeks in a row" problem with that error
also, that was my first occurance of that error, Thanks for explaining
why.

Now my question I have is this, based on that error message being there;
How can I write a formula that would ignore that error but still read the
rest of the column/cell data?

Based on this formula I'm currently trying to use:
"In Row B25:U25 I have this formula {=MIN(IF(B$4:B$230,B$4:B$23))}, to
pull the lowest score of all persons for that week."


* The problem I currently have is IF there is a #DIV/0 error, my result
for that formula is the #DIV/0 error. Is there a formula that ignores that
error and would give me an accurate result?



** The formula {=MIN(IF(B$4:B$230,B$4:B$23))}, continues B25 thru U25, so
I have 20 weeks to have someone 'not' show up two weeks in a row.



*** I substituted your formula =IF(COUNTIF(B4:C4,"0")0,
((SUM(B4:C4)/COUNTIF(B4:C4,"0"))-36)*0.8, ""), for my formula on my
"Scorecard" worksheet and it got rid of the #DIV/0 errors for the
'unplayed future' weeks, (over two 0's in a row), but now my "Low Net"
worksheet error changed from #DIV/0 to #Value!. (Low Net sheet references
my Scorecard sheet)





I hope that makes a little more sense,

Brad






"Bernard Liengme" wrote in message
...
First let's tidy up =SUM(Scorecard!B4-Scorecard!Y4)
There is no need to use SUM when doing simple math operations, so use
=Scorecard!B4-Scorecard!Y4

Your problem with =((SUM(B4:C4)/COUNTIF(B4:C4,"0"))-36)*0.8 arises when
the two values B4 and C4 are both zero?
How about =IF(COUNTIF(B4:C4,"0")0,
((SUM(B4:C4)/COUNTIF(B4:C4,"0"))-36)*0.8, "")
Alternative =IF(OR(B40,C40), ((SUM(B4:C4)/COUNTIF(B4:C4,"0")-36))*0.8,
"")

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Brad" wrote in message
...
I'm trying to calculate a 'low net' score. A persons 'actual score'
minus
'their handicap' = 'low net' score.

I have this worksheet setup like this;

A4:A23 = list of names
Columns B-U have the weekly calculated low net scores
B4:U23 = the calculated 'low net' scores using this formula
=SUM(Scorecard!B4-Scorecard!Y4); (this is for player one)
Scorecard!B4 is player one's first week 'actual score', Scorecard!Y4 is
player one's calculated 'handicap'.

In Row B25:U25 I have this formula {=MIN(IF(B$4:B$230,B$4:B$23))}, to
pull
the lowest score of all persons for that week.

My problem is this;
I'm getting the #DIV/0 error and I don't know how to get around that
because
we do have occational zero's "0" for scores when people don't show up.
The
error is coming from my "handicap" formula
=((SUM(B4:C4)/COUNTIF(B4:C4,"0"))-36)*0.8

I have two bits of data to work around;
How can I write a formula to work above zero AND ignore the #DIV/0
cells?
I'm referring to B25:U25 formula.

Thanks,
Brad