hallo

i use a excel sheet to count the points gaint in an competition with
model sail boats

i have this problem i kant solve


example of a row with the points


day1 day2 day3 day4 day5 total classement
4 2 6 4 0 10 2
0 0 1 1 0 2 1


the total collum counts the points and then takes the highest score
from the total
the lower your points the better you are
the first to arrive gets one point the second two points the therd
three points etc..

in row two the guy was won two of the 5 events there and normaly he has
won but due to the three times he was not there he is not classyfiet

how doe i count the two or three zero's in the row and put in the
classemet collum NC


greatings raf


--
RAF JANSSENS
------------------------------------------------------------------------
RAF JANSSENS's Profile: http://www.excelforum.com/member.php...o&userid=28598
View this thread: http://www.excelforum.com/showthread...hreadid=482646

=IF(COUNTIF(A11:E11,0)1,"NC",your original calculation here)

From your message I assume one 0 is OK so I've set the formula to flag NC if
1.
--
Ian
--
"RAF JANSSENS"
wrote in message
news:RAF.JANSSENS.1y349y_1131296402.3271@excelforu m-nospam.com...

hallo

i use a excel sheet to count the points gaint in an competition with
model sail boats

i have this problem i kant solve


example of a row with the points


day1 day2 day3 day4 day5 total classement
4 2 6 4 0 10 2
0 0 1 1 0 2 1


the total collum counts the points and then takes the highest score
from the total
the lower your points the better you are
the first to arrive gets one point the second two points the therd
three points etc..

in row two the guy was won two of the 5 events there and normaly he has
won but due to the three times he was not there he is not classyfiet

how doe i count the two or three zero's in the row and put in the
classemet collum NC


greatings raf


--
RAF JANSSENS
------------------------------------------------------------------------
RAF JANSSENS's Profile:
http://www.excelforum.com/member.php...o&userid=28598
View this thread: http://www.excelforum.com/showthread...hreadid=482646

This may be over-complicating it a bit, but what it does is check if there
are more than 2 zeroes, if so it substitutes 99 for those zeroes

=SUM(IF(COUNTIF(A10:E10,0)2,IF(A10:E10=0,99,A10:E 10),A10:E10))-MAX(IF(COUNT
IF(A10:E10,0)2,IF(A10:E10=0,99,A10:E10),A10:E10))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RAF JANSSENS"
wrote in message
news:RAF.JANSSENS.1y349y_1131296402.3271@excelforu m-nospam.com...

hallo

i use a excel sheet to count the points gaint in an competition with
model sail boats

i have this problem i kant solve


example of a row with the points


day1 day2 day3 day4 day5 total classement
4 2 6 4 0 10 2
0 0 1 1 0 2 1


the total collum counts the points and then takes the highest score
from the total
the lower your points the better you are
the first to arrive gets one point the second two points the therd
three points etc..

in row two the guy was won two of the 5 events there and normaly he has
won but due to the three times he was not there he is not classyfiet

how doe i count the two or three zero's in the row and put in the
classemet collum NC


greatings raf


--
RAF JANSSENS
------------------------------------------------------------------------
RAF JANSSENS's Profile:
http://www.excelforum.com/member.php...o&userid=28598
View this thread: http://www.excelforum.com/showthread...hreadid=482646

hallo


the proposed help isnt working

i put the tabel online in thiS directory

http://users.pandora.be/mycl/excel/


THE RESULT OF THE CALCULATION IS DUE IN THE COLLUM K


sorry that the sheet is in dutch but thats the language i ame speaking

hope somone find a sollution for this problem
the version of excel i ame using is 2000

regards raf


--
RAF JANSSENS
------------------------------------------------------------------------
RAF JANSSENS's Profile: http://www.excelforum.com/member.php...o&userid=28598
View this thread: http://www.excelforum.com/showthread...hreadid=482646

Ian's formula seems to do it

=IF(COUNTIF(F5:J5,"***")2,"NC",SUM(F5:J5)-MAX(F5:J5))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RAF JANSSENS"
wrote in message
news:RAF.JANSSENS.1y4y0n_1131381618.4508@excelforu m-nospam.com...

hallo


the proposed help isnt working

i put the tabel online in thiS directory

http://users.pandora.be/mycl/excel/


THE RESULT OF THE CALCULATION IS DUE IN THE COLLUM K


sorry that the sheet is in dutch but thats the language i ame speaking

hope somone find a sollution for this problem
the version of excel i ame using is 2000

regards raf


--
RAF JANSSENS
------------------------------------------------------------------------
RAF JANSSENS's Profile:
http://www.excelforum.com/member.php...o&userid=28598
View this thread: http://www.excelforum.com/showthread...hreadid=482646

gallo

the three "***" are normalie zero's but this wil do
exept that i keep having aan failur message from excel if i past the
line into the colum K


traing to put everithing in dutch
hoping that it wil work then

regards raf


--
RAF JANSSENS
------------------------------------------------------------------------
RAF JANSSENS's Profile: http://www.excelforum.com/member.php...o&userid=28598
View this thread: http://www.excelforum.com/showthread...hreadid=482646

You probably need

=IF(AANTAL.ANS(F5:J5,0)2;"NC";SOM(F5:J5)-MAX(F5:J5))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RAF JANSSENS"
wrote in message
news:RAF.JANSSENS.1y51hm_1131386103.6551@excelforu m-nospam.com...

gallo

the three "***" are normalie zero's but this wil do
exept that i keep having aan failur message from excel if i past the
line into the colum K


traing to put everithing in dutch
hoping that it wil work then

regards raf


--
RAF JANSSENS
------------------------------------------------------------------------
RAF JANSSENS's Profile:
http://www.excelforum.com/member.php...o&userid=28598
View this thread: http://www.excelforum.com/showthread...hreadid=482646