Discussion Excel 2016 Formula - skins in Excel
https://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/334861#M25440
<P>I am working on a formula that will calculate a Skins game for golf. The criteria is that players participating in the skins game will have a y (for participating in skins) in column c. Each column is calculated individually for lowest score in the column. The lowest unrepeated score that has a y in column c wins the skin for that hole and that score is highlighted. </P><P> </P><P>The 2nd formula calculates the total number of skins won for the entire spreadsheet and puts that answer in column R3.</P><P> </P><P>In the example attached Player3 column f8 is the winner with a low score of 2 and the score of 2 is not repeated in column f rows 6 to 11, this player is highlighted yellow as the winner. Column H has no winner because players who are participating in the skin tie for the lowest score.</P><P> </P><P>My formula falls apart when a player that does not have a y in column c (player is not participating in skins) has the lowest score (this player is highlighted yellow and should not be). (Example: column G row 8 should be highlighted. This is lowest score for a participating skin player).</P><P>My formula falls apart again when a player who is participating has the same lowest score as a player who is not participating. In this case the player who is participating should be highlighted in yellow. (Example Column I row 8 should be highlighted yellow as the winner).</P>Tue, 12 Feb 2019 01:31:23 GMTstevesteph12342019-02-12T01:31:23ZExcel 2016 Formula - skins
https://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/334861#M25440
<P>I am working on a formula that will calculate a Skins game for golf. The criteria is that players participating in the skins game will have a y (for participating in skins) in column c. Each column is calculated individually for lowest score in the column. The lowest unrepeated score that has a y in column c wins the skin for that hole and that score is highlighted. </P><P> </P><P>The 2nd formula calculates the total number of skins won for the entire spreadsheet and puts that answer in column R3.</P><P> </P><P>In the example attached Player3 column f8 is the winner with a low score of 2 and the score of 2 is not repeated in column f rows 6 to 11, this player is highlighted yellow as the winner. Column H has no winner because players who are participating in the skin tie for the lowest score.</P><P> </P><P>My formula falls apart when a player that does not have a y in column c (player is not participating in skins) has the lowest score (this player is highlighted yellow and should not be). (Example: column G row 8 should be highlighted. This is lowest score for a participating skin player).</P><P>My formula falls apart again when a player who is participating has the same lowest score as a player who is not participating. In this case the player who is participating should be highlighted in yellow. (Example Column I row 8 should be highlighted yellow as the winner).</P>Tue, 12 Feb 2019 01:31:23 GMThttps://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/334861#M25440stevesteph12342019-02-12T01:31:23ZRe: Excel 2016 Formula - skins
https://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/334864#M25442
My formular is in conditional formatting =AND(C6="y",F6=MIN($F$6:$F$15),COUNTIF($F$6:$F$15,F6)=1)<BR />Tue, 12 Feb 2019 01:38:03 GMThttps://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/334864#M25442stevesteph12342019-02-12T01:38:03ZRe: Excel 2016 Formula - skins
https://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/335022#M25453
<P>Hi,</P>
<P> </P>
<P>In your formula you find MIN for all cells in the columns, not only for the cells for which in column C is "y".</P>
<P> </P>
<P>If you are on Office365 or 2019 you may use MINIFS as</P>
<PRE>=AND($C6="y",F6=MINIFS(F$6:F$15,$C$6:$C$15,"y"),COUNTIF(F$6:F$15,F6)=1)</PRE>
<P>(second sheet attached), if not that could be</P>
<PRE>=AND($C6="y",F6=AGGREGATE(15,6,1/($C$6:$C$15="y")/(F$6:F$15>0)*F$6:F$15,1),COUNTIF(F$6:F$15,F6)=1)</PRE>
<P>and it's not necessary to use separate rule for each column, you may apply one rule for entire range. </P>Tue, 12 Feb 2019 10:34:43 GMThttps://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/335022#M25453Sergei Baklan2019-02-12T10:34:43ZRe: Excel 2016 Formula - skins
https://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/337799#M25488
<P>Thank you so much for your help Sergei. I have one additional problem. For Column I, Player 3 is participating in Skins (c8 is a y) and the score is 3. Player 4 is not participating and also has a 3 in Column I row 9. Player 3 should be the winner and be highlighted. When 2 players have the lowest score and one player has a y in column c and the other player does not, the player with the y in column c is the winner. How do I add that to the formula that you gave me? </P><P> </P><P>Thanks again for all of your help.</P>Wed, 13 Feb 2019 01:05:08 GMThttps://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/337799#M25488stevesteph12342019-02-13T01:05:08ZRe: Excel 2016 Formula - skins
https://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/338413#M25502
<P>Okay, so we shall count with the condition</P>
<PRE>=AND($C6="y",F6=MINIFS(F$6:F$15,$C$6:$C$15,"y"),COUNTIFS($C$6:$C$15,"y",F$6:F$15,F6)=1)</PRE>
<P>Updated file is attached</P>Wed, 13 Feb 2019 10:43:14 GMThttps://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/338413#M25502Sergei Baklan2019-02-13T10:43:14ZRe: Excel 2016 Formula - skins
https://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/339034#M25550
<P>Thank you so much Sergei for the excellent answer. The formula is perfect and I appreciate all your hard work.</P>Thu, 14 Feb 2019 00:35:09 GMThttps://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/339034#M25550stevesteph12342019-02-14T00:35:09ZRe: Excel 2016 Formula - skins
https://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/358917#M26546
<P>The skins are now calculating correctly. My last task is to count the total skins and place them in column P row 4. For example, there are 3 skins highlighted as wins. Can you help me with the formula to put the 3 wins in column P row 4. </P><P> </P><P>Thanks so much for your help.</P>Fri, 01 Mar 2019 13:35:12 GMThttps://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/358917#M26546stevesteph12342019-03-01T13:35:12ZRe: Excel 2016 Formula - skins
https://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/359081#M26571
<P>I'd do that with helper row - add formula as</P>
<PRE>=SUMPRODUCT(($C6:$C15="y")*(F6:F15=MINIFS(F$6:F$15,$C$6:$C$15,"y"))*(COUNTIFS($C$6:$C$15,"y",F$6:F$15,F6:F15)=1))</PRE>
<P>in that row, column by column, and sum to P3.</P>
<P> </P>
<P>In attached above formula is in F5:N5</P>Fri, 01 Mar 2019 17:40:32 GMThttps://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/359081#M26571Sergei Baklan2019-03-01T17:40:32ZRe: Excel 2016 Formula - skins
https://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/359893#M26693
<P>My requirements for the skins game has changed once again. I need to total the number of skins game won by each player. I am attaching the Example spreadsheet. Can you help me with the formula that would take each player and total their wins. Player 1 has won no skins game so his #Won by Player Column ), Row 6 is 0. Player 3 has won 3 games (as highlighted) his total in Column O Row 8 is 3.</P>Mon, 04 Mar 2019 17:08:54 GMThttps://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/359893#M26693stevesteph12342019-03-04T17:08:54ZRe: Excel 2016 Formula - skins
https://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/364527#M27070
<P>We may add to helper row (#5) calculation of player on which position won</P>
<PRE>=IFNA(MATCH(1,INDEX(($C6:$C15="y")*(F6:F15=MINIFS(F$6:F$15,$C$6:$C$15,"y"))*(COUNTIFS($C$6:$C$15,"y",F$6:F$15,F6:F15)=1),0),0),0)</PRE>
<P>after that calculate total wins for each player as</P>
<PRE>=SUMPRODUCT(--($F$5:$N$5=(ROW()-ROW($E$5))))</PRE>Mon, 11 Mar 2019 22:35:48 GMThttps://techcommunity.microsoft.com/t5/excel/excel-2016-formula-skins/m-p/364527#M27070Sergei Baklan2019-03-11T22:35:48Z