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

[Brad]

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$23>0,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

 

[Bernard Liengme]

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(B4>0,C4>0), ((SUM(B4:C4)/COUNTIF(B4:C4,">0")-36))*0.8,
"")

best wishes

 

[Brad]

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$23>0,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$23>0,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]

If you still have not found a fix, send me (private email a sample file with
the problem
best wishes