If a column contains only 1 lowest score...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please help me do the following...

If a column data contains only 1 "lowest value", then change that cell
"background color" to "yellow" and the font to "red bold", else do "nothing".
 
Jerry,

Select your cell:-

Format|conditional formula|Cell value is|Equal to
enter a 1 in the next field
Set you cell colour, font colour and bold by pressing the Format button.

This format can be painted into other rcells using the format painter.

Mike
 
Try this:

Select the range of cells to be impacted
(I'll assume A1:A10, with A1 as the active cell)

From the Excel main menu:
<format><conditional formatting>
Formula is: =AND(COUNTIF($A$1:$A$10,MIN($A$1:$A$10))=1,A1=MIN($A$1:$A$10))
Click the [format] button and set the pattern and font
Click the [OK] buttons

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
What do you mean by "lowest value"? Do you mean if there is only one
minimum in the range then colour it, but if there are, say, 3 cells
which are all minima then don't bother?

Pete
 
Try this:

Assume the range of interest is A1:A10
Select the range A1:A10
Goto the menu Format>Conditional Formatting
Formula Is:
=AND(ISNUMBER(A1),COUNTIF(A$1:A$10,A1)=1,A1=MIN(A$1:A$10))
Click the Format button
Select the desired style(s)
OK out
 
In playing with various combinations of numbers, blanks and text, my posted
CF formula can fail.

This one works much better:
=AND(A1=MIN(A$1:A$10),A1<>"",COUNTIF(A$1:A$10,A1)=1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


Ron Coderre said:
Try this:

Select the range of cells to be impacted
(I'll assume A1:A10, with A1 as the active cell)

From the Excel main menu:
<format><conditional formatting>
Formula is: =AND(COUNTIF($A$1:$A$10,MIN($A$1:$A$10))=1,A1=MIN($A$1:$A$10))
Click the [format] button and set the pattern and font
Click the [OK] buttons

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


JerryW said:
Please help me do the following...

If a column data contains only 1 "lowest value", then change that cell
"background color" to "yellow" and the font to "red bold", else do "nothing".
Click to expand...
Click to expand...
 
Thanks to all of you that replied to my 1st ever, excel posting. Based on
your responses I think that your answers were very helpful. However, I
believe that I need to make my question clearer and more detailed. What I
believe I need here is to create a FUNCTION. So here goes my description…

What I have here is a “golf score sheet†which does many, many tasks. In
this current task, I need to identify the “lowest score†on any of the 18
holes, which are NOT equaled by any of the other 32 players in the field.
(Two tie… All tie.)

If no other player has “tied†the “lowest†score on any given hole, then I
need to highlight that cell by changing the “background†color and increase
the font size and also change it to “redâ€.

There are “always†32 rows of players, (Not more OR less.), followed by
columns 1-9, “skip†a column, and then holes 10-18. (Some lower column cells
may be “blankâ€, due to less than 32 palyers.)

The following function (NOT WRITTEN BY ME), is used to compute “Calcutta
Pointsâ€, (This may give you a general idea as to what the spreadsheet looks
like.) and is based on the following values:

2 under par or better = 8 pts.
1 under par = 5 pts.
Par = 3 pts.
Bogey = 2 pts.
And Double Bogey = 1 pt.
Any other scores greater than Double Bogey = 0 pts.

+++++++++++++++++++++++++++++++++++++++++
Function CalcuttaCalc(FrontNine As Range, BackNine As Range) As Integer
Dim points As Integer
Dim score As Integer
Dim hole As Integer

'FrontNine.
For score = 1 To 9
points = points + CalculatePoints(FrontNine.Cells(1, score), _
Sheet1.Cells(3, score + 26))
Next score

'BackNine.
For score = 1 To 9
points = points + CalculatePoints(BackNine.Cells(1, score), _
Sheet1.Cells(3, score + 36))
Next score

CalcuttaCalc = points
End Function

Private Function CalculatePoints(iScore As Integer, iPar As Integer)
'If the entered score is 0 or not entered, return 0 and exit.
If iScore = 0 Then
CalculatePoints = 0
Exit Function
End If

Dim score As Integer
Dim points As Integer
score = iScore - iPar

Select Case score
Case Is <= -2 'Eagle or better
points = 8
Case -1 'Birdie
points = 5
Case 0 'Par
points = 3
Case 1 'Bogey
points = 2
Case 2 'Double Bogey
points = 1
Case Else 'Anything else
points = 0
End Select
CalculatePoints = points
End Function
++++++++++++++++++++++++++++++++

But wait… There’s more that needs to be done…

Hole pars are in listed “above†EACH column in row #3. If a player “wins†a
hole with a birdie “or better†then he gets credit for “two†skins rather
than “one†skin. Total Number of skins are TOTALED in (R5,CZ:R36,CZ) to the
left of the first column of hole #1.

What are your thoughts…?

Thanks,
Jerry

(If there is someplace that I can “post†the actual spreadsheet, please let
me know where and how I can do it.)
+++++++++++++++++++
 
Back
Top