More help with conditional formatting

JRolph · Mar 10th, 2002, 05:54 PM

The formula +AND(A1=MIN(A:A),COUNTIF(A:A,A1)=1) works very well to find the single lowest number in a column on a worksheet without column headers. My problem is that my worksheet needs column numbers (skin game sheet for golf) 1-18 and handicap numbers for each hole. How can I get this formula to ignore row 1 & row 2?

JRolph · Mar 10th, 2002, 05:56 PM

Sorry! I put + instead of = to start the formula.

Derek · Mar 10th, 2002, 06:31 PM

Hi
What is in A1? This formula only returns True or False for me, not a number.
regards
Derek

JRolph · Mar 10th, 2002, 09:10 PM

The conditional formatting looks for the single lowest number in the column. If there is one lowest, the format changes to dark grey with white font. If more than one, does nothing.

Derek · Mar 10th, 2002, 11:34 PM

Hi
Sorry, I didn't realise your formula was in your conditional formatting.

Select Column A and delete all conditional formatting.
Select A3 and conditionally format it with the following formula (this covers the range A3:A200 - increase the 200 if you use more rows)

=AND(A3=MIN($A$3:$A$200),COUNTIF($A$3:$A$200,A3)=1)

Select A3:A200, select conditional formatting and click OK

That should fix it for you
regards
Derek

JRolph · Mar 11th, 2002, 06:52 AM

Derek,

Thank you! This works just the way I want it to.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 10th, 2002, 05:54 PM	#1
JRolph New Member Join Date: Mar 2002 Posts: 14	The formula +AND(A1=MIN(A:A),COUNTIF(A:A,A1)=1) works very well to find the single lowest number in a column on a worksheet without column headers. My problem is that my worksheet needs column numbers (skin game sheet for golf) 1-18 and handicap numbers for each hole. How can I get this formula to ignore row 1 & row 2?

Mar 10th, 2002, 05:56 PM	#2
JRolph New Member Join Date: Mar 2002 Posts: 14	Sorry! I put + instead of = to start the formula.

Mar 10th, 2002, 06:31 PM	#3
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi What is in A1? This formula only returns True or False for me, not a number. regards Derek

Mar 10th, 2002, 09:10 PM	#4
JRolph New Member Join Date: Mar 2002 Posts: 14	The conditional formatting looks for the single lowest number in the column. If there is one lowest, the format changes to dark grey with white font. If more than one, does nothing.

Mar 10th, 2002, 11:34 PM	#5
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi Sorry, I didn't realise your formula was in your conditional formatting. Select Column A and delete all conditional formatting. Select A3 and conditionally format it with the following formula (this covers the range A3:A200 - increase the 200 if you use more rows) =AND(A3=MIN($A$3:$A$200),COUNTIF($A$3:$A$200,A3)=1) Select A3:A200, select conditional formatting and click OK That should fix it for you regards Derek

Mar 11th, 2002, 06:52 AM	#6
JRolph New Member Join Date: Mar 2002 Posts: 14	Derek, Thank you! This works just the way I want it to.