Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 410K Members
- 9,652 Online
- 466K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Excel 2016 Formula - skins

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 05:31 PM

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.

The 2nd formula calculates the total number of skins won for the entire spreadsheet and puts that answer in column R3.

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.

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).

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).

Labels:

9 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 05:38 PM

My formular is in conditional formatting =AND(C6="y",F6=MIN($F$6:$F$15),COUNTIF($F$6:$F$15,F6)=1)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-12-2019 02:34 AM

Hi,

In your formula you find MIN for all cells in the columns, not only for the cells for which in column C is "y".

If you are on Office365 or 2019 you may use MINIFS as

=AND($C6="y",F6=MINIFS(F$6:F$15,$C$6:$C$15,"y"),COUNTIF(F$6:F$15,F6)=1)

(second sheet attached), if not that could be

=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)

and it's not necessary to use separate rule for each column, you may apply one rule for entire range.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-12-2019 05:05 PM

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?

Thanks again for all of your help.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-13-2019 02:43 AM

Okay, so we shall count with the condition

=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)

Updated file is attached

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-13-2019 04:35 PM

Thank you so much Sergei for the excellent answer. The formula is perfect and I appreciate all your hard work.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-01-2019 05:35 AM

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.

Thanks so much for your help.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-01-2019 09:40 AM

I'd do that with helper row - add formula as

=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))

in that row, column by column, and sum to P3.

In attached above formula is in F5:N5

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-04-2019 09:08 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-11-2019 03:35 PM

We may add to helper row (#5) calculation of player on which position won

=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)

after that calculate total wins for each player as

=SUMPRODUCT(--($F$5:$N$5=(ROW()-ROW($E$5))))

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
24.6K
Views

1 Likes

35 Replies

flashing a white screen while open new tab

cntvertex
in
Discussions
on
10-05-2019
23.2K
Views

10 Likes

13 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
136K
Views

6 Likes

28 Replies

What is Canary ring in Windows insider program? and how do we get them?

HotCakeX
in
Windows Insider Program
on
09-27-2019
11K
Views

0 Likes

9 Replies

How to download windows server 2019 update to 1903

Cmakar37
in
Windows Server for IT Pro
on
07-03-2019
23K
Views

0 Likes

7 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft