sorting golf scores
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. |
sorting golf scores
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 |
sorting golf scores
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 |
sorting golf scores
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 |
sorting golf scores
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 |
sorting golf scores
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 |
All times are GMT +1. The time now is 03:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com