counting cells in a row with color highlight

  • Thread starter Thread starter mapesii
  • Start date Start date
M

mapesii

I am new to doing this program so if i sound dumb sorry. if you know golf
this might be simple to understand. i have several rows with names and 18
colums ( 1 for each 18 holes). I am trying to do a count of skins won by each
player at the end of each players row.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
18
name 1 4 5 4 6 3 4 5 5 5 5 6 4 6 4 4 4 4
4
name 2 4 4 4 4 4 4 4 4 4 3 3 5 5 5 5 5 5
5
name 3 3 4 4 4 3 5 5 5 5 4 5 5 5 5 5 4 4
3
name 4 4 4 4 4 4 4 3 5 5 5 5 5 5 5 3 4 4
5

I have the sheet set to highlight the lowest value in each column if there
is only one lowest value. here is where it gets confusing. if there is more
than on low value then it would carry over to the next column until it comes
to a column with only one
highlighted. here is what the total would be in the ex. at the end of each
row.
name 1 total is 3
name 2 total is 4
name 3 total is 4
name 4 total is 7

I am mostly doing this just to learn excel but it will also make it easier
for my little golfing group to figure the the winners.

thanks for any direction.
 
Counting highlighted cells is very difficult in Excel, and it doesn't help
the learning experience. I would do something different.

As a golfer who knows Excel, here's what I would do:
1. Add a row after Name4 called Skins. This would calculated the skins
awarded that hole.
2. Add another row called Carryover. This would calculate the consecutive
holes tied (or what the hole is worth in TV Skins language).
3. Add a column for Skins Won.

To calculate the Carryover, if the count of the min is greater than one, add
one to the previous total, else it's zero.
To calculate the Skins, if the count of the min is one, then it's the
previous hole's carryover plus one.
To calculate the Skins Won, it's a Sumproduct of the min of the column times
the Skins (because where there's a tie, the Skin is zero, so won't affect
the total).

If you don't want the extra rows to show, simply hide them.

As you want to learn Excel, I'll leave the formula development to you. If
you need help, post back.

Regards,
Fred.
 
Counting highlighted cells is very difficult in Excel, and it doesn't help
the learning experience.
Click to expand...

I completely agree and to add to that, it also doesn't work like poeple
think/expect it to work due to formatting of cells doesn't trigger a
calculation. Not to mention that it's also exponentially more complicated
when trying to count cells that have been colored using conditional
formatting.

Following your suggestion of using helper formulas (I don't know if can be
done without helpers. I tried for about half an hour then gave up! You might
be able to do it using combinations of OFFSET, MMULT, SUBTOTAL, FREQUENCY)

B1:S1 = hole numbers
A2:A5 = player names
B2:S5 = scores

Enter this formula in B6 and copy across to S6. This will return the number
of skins won for each hole:

=IF(COUNTIF(B2:B5,MIN(B2:B5))=1,B1-SUM($A6:A6),"")

Enter this formula in B7 and copy across to S7. This will return the wining
score for each hole that a skin is awarded:

=IF(B6<>"",MIN(B2:B5),"")

Enter this formula in T2 and copy down to T5. This will calculate each
players skins:

=SUMPRODUCT(--(B2:S2=B$7:S$7),B$6:S$6)

Now, the only problem is when no one wins the last few (several) holes. For
example, the last skin was won on hole 15 and the remaining holes have been
tied.
 
thanks fred for the insight. i will give it a whirl over the next few days
and see if i can get it done
 
thanks T. Valko for your input as well. there is so much to learn in this
program it almost is overwellming
 
See if this puts things into perspective:

I've been using spreadsheet programs since the early 80's and I'm *still*
learning!
 
I used your formulas with a few cell reference modifications to fit my sheet
and it appears to have worked. Totalling each players skins won in the last
column for each player. Can't say I completely understand it but I still
learned from it. I am doing a workbook with a scorecard that can be modified
to any course with sheets to caculate course handicap and handicap indexes.
Thanks again
 
Back
Top