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?
 

Try replacing your handicap formula with:

=IF(COUNTIF(B4:C4,"0"),((SUM(B4:C4)/COUNTIF(B4:C4,"0"))-36)*0.8,0)

to avoid the #DIV/0 error...and get a 0 instead.


--
NBVC

Where there is a will there are many ways.
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=107430