counting 0 in a row  
Author Message
Shelleen





PostPosted: Sun Nov 06 11:28:44 CST 2005 Top

worksheet functions >> counting 0 in a row

hallo

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

i have this problem i kant solve


example of a row with the points


day1 day2 day3 day4 day5 total clas****t
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 scor
from the total
the lower your points the better you are
the first to arrive gets one point the second two points the ther
three points etc..

in row two the guy was won two of the 5 events there and normaly he ha
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 th
classemet collum NC


greatings ra

--
RAF JANSSEN
-----------------------------------------------------------------------
RAF JANSSENS's Profile: http://www.hide-link.com/ ;userid=2859
View this thread: http://www.hide-link.com/

Excel450  
 
 
Ian





PostPosted: Sun Nov 06 11:28:44 CST 2005 Top

worksheet functions >> counting 0 in a row =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
--



>
> 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 clas****t
> 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.hide-link.com/ ;userid=28598
> View this thread: http://www.hide-link.com/
>


 
 
Bob





PostPosted: Sun Nov 06 11:54:59 CST 2005 Top

worksheet functions >> counting 0 in a row 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:E10),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)





>
> 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 clas****t
> 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.hide-link.com/ ;userid=28598
> View this thread: http://www.hide-link.com/
>


 
 
RAF





PostPosted: Mon Nov 07 10:38:31 CST 2005 Top

worksheet functions >> counting 0 in a row
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 ra

--
RAF JANSSEN
-----------------------------------------------------------------------
RAF JANSSENS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2859
View this thread: http://www.excelforum.com/showthread.php?threadid=48264

 
 
Bob





PostPosted: Mon Nov 07 11:41:06 CST 2005 Top

worksheet functions >> counting 0 in a row 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)





>
> 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?action=getinfo&userid=28598
> View this thread: http://www.excelforum.com/showthread.php?threadid=482646
>


 
 
RAF





PostPosted: Mon Nov 07 11:53:01 CST 2005 Top

worksheet functions >> counting 0 in a row
gallo

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


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

regards ra

--
RAF JANSSEN
-----------------------------------------------------------------------
RAF JANSSENS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2859
View this thread: http://www.excelforum.com/showthread.php?threadid=48264

 
 
Bob





PostPosted: Mon Nov 07 12:37:37 CST 2005 Top

worksheet functions >> counting 0 in a row 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)





>
> 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?action=getinfo&userid=28598
> View this thread: http://www.excelforum.com/showthread.php?threadid=482646
>


 
 
RAF





PostPosted: Tue Nov 08 09:35:41 CST 2005 Top

worksheet functions >> counting 0 in a row
hallo

if i paste the formula in to the right cell i get an erro from excel

i think the version of excel is missing som things

that you all have and i don't

how kan i get the problem solved???

regards raf


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

 
 
Bob





PostPosted: Tue Nov 08 10:44:40 CST 2005 Top

worksheet functions >> counting 0 in a row RAF,

Do you have English or Dutch Excel?
Do you have English/American or Continental separators (, for English, ; for
continental)

If English language, English separators, you need to use

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

If English language, Continental separators, you need to use

=IF(COUNTIF(F5:J5,0)>2;"NC";SUM(F5:J5)-MAX(F5:J5))

If you have dutch langauge, you probabloy have continental separators, which
means you 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)





>
> hallo
>
> if i paste the formula in to the right cell i get an erro from excel
>
> i think the version of excel is missing som things
>
> that you all have and i don't
>
> how kan i get the problem solved???
>
> regards raf
>
>
> --
> RAF JANSSENS
> ------------------------------------------------------------------------
> RAF JANSSENS's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=28598
> View this thread: http://www.excelforum.com/showthread.php?threadid=482646
>


 
 
RAF





PostPosted: Tue Nov 08 11:56:29 CST 2005 Top

worksheet functions >> counting 0 in a row
hallo

dutch language and contineltal separations

ik kee having the same fold message


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


excel keeps having te same problem with te red colord part in the
string
has someone trying the sheet on the website with the string on the
right place K5

http://users.pandora.be/mycl/excel/
it's down loadeble and without a virus i ame using antivir guard xp

if the problem is non existing with an other execl prog then the
problem is here with this version of excel

regards raf


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

 
 
Ron





PostPosted: Tue Nov 08 14:56:39 CST 2005 Top

worksheet functions >> counting 0 in a row Hi Raf

in Dutch you must use

=ALS(AANTAL.ALS(F5:J5;0)>2;"NC";SOM(F5:J5)-MAX(F5:J5))


--
Regards Ron de Bruin
http://www.rondebruin.nl




>
> hallo
>
> dutch language and contineltal separations
>
> ik kee having the same fold message
>
>
> =IF(AANTAL.ANS(F5:J5,0)>2;"NC";SOM(F5:J5)-MAX(F5:J5))
>
>
> excel keeps having te same problem with te red colord part in the
> string
> has someone trying the sheet on the website with the string on the
> right place K5
>
> http://users.pandora.be/mycl/excel/
> it's down loadeble and without a virus i ame using antivir guard xp
>
> if the problem is non existing with an other execl prog then the
> problem is here with this version of excel
>
> regards raf
>
>
> --
> RAF JANSSENS
> ------------------------------------------------------------------------
> RAF JANSSENS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28598
> View this thread: http://www.excelforum.com/showthread.php?threadid=482646
>


 
 
RAF





PostPosted: Tue Nov 08 15:09:11 CST 2005 Top

worksheet functions >> counting 0 in a row
hallo


oef thanks it is working

not so simple the differens betwee al those excel program's
and language's

a great problem is solved thaks to you guy's



regards ra

--
RAF JANSSEN
-----------------------------------------------------------------------
RAF JANSSENS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2859
View this thread: http://www.excelforum.com/showthread.php?threadid=48264

 
 
Ron





PostPosted: Tue Nov 08 15:16:21 CST 2005 Top

worksheet functions >> counting 0 in a row Hi Raf

I believe Bob send you a workbook with this formula?
Do you have errors in the formula cells when you open that workbook ??

Groeten Ron


--
Regards Ron de Bruin
http://www.rondebruin.nl




>
> hallo
>
>
> oef thanks it is working
>
> not so simple the differens betwee al those excel program's
> and language's
>
> a great problem is solved thaks to you guy's
>
>
>
> regards raf
>
>
> --
> RAF JANSSENS
> ------------------------------------------------------------------------
> RAF JANSSENS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28598
> View this thread: http://www.excelforum.com/showthread.php?threadid=482646
>


 
 
Bob





PostPosted: Tue Nov 08 16:45:14 CST 2005 Top

worksheet functions >> counting 0 in a row Thanks Ron.

Bob



> Hi Raf
>
> in Dutch you must use
>
> =ALS(AANTAL.ALS(F5:J5;0)>2;"NC";SOM(F5:J5)-MAX(F5:J5))
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>



> >
> > hallo
> >
> > dutch language and contineltal separations
> >
> > ik kee having the same fold message
> >
> >
> > =IF(AANTAL.ANS(F5:J5,0)>2;"NC";SOM(F5:J5)-MAX(F5:J5))
> >
> >
> > excel keeps having te same problem with te red colord part in the
> > string
> > has someone trying the sheet on the website with the string on the
> > right place K5
> >
> > http://users.pandora.be/mycl/excel/
> > it's down loadeble and without a virus i ame using antivir guard xp
> >
> > if the problem is non existing with an other execl prog then the
> > problem is here with this version of excel
> >
> > regards raf
> >
> >
> > --
> > RAF JANSSENS
> > ------------------------------------------------------------------------
> > RAF JANSSENS's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=28598
> > View this thread:
http://www.excelforum.com/showthread.php?threadid=482646
> >
>
>