Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula is correct, but result will not show | Excel Worksheet Functions | |||
#value! on spreadsheet when formula palette gets correct result | Excel Discussion (Misc queries) | |||
Not obtaining the correct result | Excel Discussion (Misc queries) | |||
copied formula has correct cell reference, but result of original | Excel Worksheet Functions | |||
Formula Result Correct but value in the cell is wrong | Excel Worksheet Functions |