I have 5 players on your team and I must throw out the highest score, i know
how to do that, but if only 4 players show up it only give me a total for 3
players and all 4, how do i get it to see the 0 score entered and not messup
my formula.

Without seeing your formula it would be hard to say, however, if you set
the 'score cell' to a format of ##0 you can then use something like
=COUNTIF(A:A,"<")
to count the zero values too.

You will need to adjust the A:A reference to suit your needs.

--

Tommy Wrote:
I have 5 players on your team and I must throw out the highest score, i
know
how to do that, but if only 4 players show up it only give me a total
for 3
players and all 4, how do i get it to see the 0 score entered and not
messup
my formula.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530224

here is my formula
=sum(b4:b8)-max(b4:b8)

again it will give me only the total of 3 golfers if only 4 play, sometimes
the fifth doesn't show up and a zero is entered. So I do I 4 scores but
again only 3 are added. I have to throw out the highest if all 5 players
show up.


"Bryan Hessey" wrote:


Without seeing your formula it would be hard to say, however, if you set
the 'score cell' to a format of ##0 you can then use something like
=COUNTIF(A:A,"<")
to count the zero values too.

You will need to adjust the A:A reference to suit your needs.

--

Tommy Wrote:
I have 5 players on your team and I must throw out the highest score, i
know
how to do that, but if only 4 players show up it only give me a total
for 3
players and all 4, how do i get it to see the 0 score entered and not
messup
my formula.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530224

I don't quite understand how you can tell whether the zero was sum'd or
not, perhaps you mean that you only want to discard the highest score
if more than 4 people play, but count all (four) scores if 4 or less
play, this would be:

=IF(COUNTIF(B4:B9,"0")4,SUM(B4:B8)-MAX(B4:B8),SUM(B4:B8))

which works even if two reach the same (max) score.

Hope this helps.

--

Tommy Wrote:
here is my formula
=sum(b4:b8)-max(b4:b8)

again it will give me only the total of 3 golfers if only 4 play,
sometimes
the fifth doesn't show up and a zero is entered. So I do I 4 scores
but
again only 3 are added. I have to throw out the highest if all 5
players
show up.


"Bryan Hessey" wrote:


Without seeing your formula it would be hard to say, however, if you
set
the 'score cell' to a format of ##0 you can then use something like
=COUNTIF(A:A,"<")
to count the zero values too.

You will need to adjust the A:A reference to suit your needs.

--

Tommy Wrote:
I have 5 players on your team and I must throw out the highest
score, i
know
how to do that, but if only 4 players show up it only give me a
total
for 3
players and all 4, how do i get it to see the 0 score entered and
not
messup
my formula.


--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=530224




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530224

Bryan

Thank you it worked perfect. Do you know how I can take the scores off of
the first page with names and scores, and it put them in order on the second
page with lowest score first, this will be all scores for every player.

Thank You

"Bryan Hessey" wrote:


I don't quite understand how you can tell whether the zero was sum'd or
not, perhaps you mean that you only want to discard the highest score
if more than 4 people play, but count all (four) scores if 4 or less
play, this would be:

=IF(COUNTIF(B4:B9,"0")4,SUM(B4:B8)-MAX(B4:B8),SUM(B4:B8))

which works even if two reach the same (max) score.

Hope this helps.

--

Tommy Wrote:
here is my formula
=sum(b4:b8)-max(b4:b8)

again it will give me only the total of 3 golfers if only 4 play,
sometimes
the fifth doesn't show up and a zero is entered. So I do I 4 scores
but
again only 3 are added. I have to throw out the highest if all 5
players
show up.


"Bryan Hessey" wrote:


Without seeing your formula it would be hard to say, however, if you
set
the 'score cell' to a format of ##0 you can then use something like
=COUNTIF(A:A,"<")
to count the zero values too.

You will need to adjust the A:A reference to suit your needs.

--

Tommy Wrote:
I have 5 players on your team and I must throw out the highest
score, i
know
how to do that, but if only 4 players show up it only give me a
total
for 3
players and all 4, how do i get it to see the 0 score entered and
not
messup
my formula.


--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=530224




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530224

Without knowing the format of your worksheet it's difficult to guess the
best method to copy. Either select required rows (and CTRL-Select
additional rows) and copy-paste, or use the Data, Filter, Auto-Filter
feature to select required rows, copy and paste (or paste special -
Values) to sheet 2.

You can then sort sheet 2 on the required column.

Alternately you could use the Small function to select the smallest
according to the row number, but if your 5 player teams are
interspersed with headers this might not perform as one would hope.

--

Tommy Wrote:
Bryan

Thank you it worked perfect. Do you know how I can take the scores off
of
the first page with names and scores, and it put them in order on the
second
page with lowest score first, this will be all scores for every
player.

Thank You

"Bryan Hessey" wrote:


I don't quite understand how you can tell whether the zero was sum'd
or
not, perhaps you mean that you only want to discard the highest
score
if more than 4 people play, but count all (four) scores if 4 or less
play, this would be:

=IF(COUNTIF(B4:B9,"0")4,SUM(B4:B8)-MAX(B4:B8),SUM(B4:B8))

which works even if two reach the same (max) score.

Hope this helps.

--

Tommy Wrote:
here is my formula
=sum(b4:b8)-max(b4:b8)

again it will give me only the total of 3 golfers if only 4 play,
sometimes
the fifth doesn't show up and a zero is entered. So I do I 4
scores
but
again only 3 are added. I have to throw out the highest if all 5
players
show up.


"Bryan Hessey" wrote:


Without seeing your formula it would be hard to say, however, if
you
set
the 'score cell' to a format of ##0 you can then use something
like
=COUNTIF(A:A,"<")
to count the zero values too.

You will need to adjust the A:A reference to suit your needs.

--

Tommy Wrote:
I have 5 players on your team and I must throw out the highest
score, i
know
how to do that, but if only 4 players show up it only give me
a
total
for 3
players and all 4, how do i get it to see the 0 score entered
and
not
messup
my formula.


--
Bryan Hessey


------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=530224




--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=530224




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530224