LinkBack URL
About LinkBacksI 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.
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.
--
Originally Posted by Tommy
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.
--
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.
--
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks