|
How do I conditional format for data that's repeated
I would like to highlight data using Conditional Formatting from a list of
numbers but only want to highlight the 2 smallest numbers. E.g. Data list = 6,7,8,1,9,2,4,2 The result should highlight the number 1 BUT ONLY THE FIRST 2 - I don't want both 2s highlighted. Thanks, |
Hi
if your data is in A1:A10 try the following: - select this range - goto the conditional format dialog - enter the following formula: =RANK(A1,$A$1:$A$10,TRUE)+COUNTIF($A$1:$A1,A1)-1<=2 - choose your color -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... I would like to highlight data using Conditional Formatting from a list of numbers but only want to highlight the 2 smallest numbers. E.g. Data list = 6,7,8,1,9,2,4,2 The result should highlight the number 1 BUT ONLY THE FIRST 2 - I don't want both 2s highlighted. Thanks, |
This still highlights the repeated number (2 in my example). HELP!
"Frank Kabel" wrote: Hi if your data is in A1:A10 try the following: - select this range - goto the conditional format dialog - enter the following formula: =RANK(A1,$A$1:$A$10,TRUE)+COUNTIF($A$1:$A1,A1)-1<=2 - choose your color -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... I would like to highlight data using Conditional Formatting from a list of numbers but only want to highlight the 2 smallest numbers. E.g. Data list = 6,7,8,1,9,2,4,2 The result should highlight the number 1 BUT ONLY THE FIRST 2 - I don't want both 2s highlighted. Thanks, |
Hi
I tested it for your data example and it worked for me: - in which cells is your data exactly - what is the exact formula you have entered -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... This still highlights the repeated number (2 in my example). HELP! "Frank Kabel" wrote: Hi if your data is in A1:A10 try the following: - select this range - goto the conditional format dialog - enter the following formula: =RANK(A1,$A$1:$A$10,TRUE)+COUNTIF($A$1:$A1,A1)-1<=2 - choose your color -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... I would like to highlight data using Conditional Formatting from a list of numbers but only want to highlight the 2 smallest numbers. E.g. Data list = 6,7,8,1,9,2,4,2 The result should highlight the number 1 BUT ONLY THE FIRST 2 - I don't want both 2s highlighted. Thanks, |
My data is in B3:R3 and I used:
=RANK(B3,$B$3:$R$3,TRUE)+COUNTIF($B$3:$B3,B3)-1<=2 and I will copy this formula to many rows below it. Many thanks ... Jeff "Frank Kabel" wrote: Hi I tested it for your data example and it worked for me: - in which cells is your data exactly - what is the exact formula you have entered -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... This still highlights the repeated number (2 in my example). HELP! "Frank Kabel" wrote: Hi if your data is in A1:A10 try the following: - select this range - goto the conditional format dialog - enter the following formula: =RANK(A1,$A$1:$A$10,TRUE)+COUNTIF($A$1:$A1,A1)-1<=2 - choose your color -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... I would like to highlight data using Conditional Formatting from a list of numbers but only want to highlight the 2 smallest numbers. E.g. Data list = 6,7,8,1,9,2,4,2 The result should highlight the number 1 BUT ONLY THE FIRST 2 - I don't want both 2s highlighted. Thanks, |
Woops I meant ...
=RANK(B3,$B3:$R3,TRUE)+COUNTIF($B3:$R3,B3)-1<=2 and my data is: 7,10,9,7,7,9,6,4,6,2,1,3,2,6,7,8,7 The 1 and the first 2 should get highlighted. Thanks again. "Putty" wrote: My data is in B3:R3 and I used: =RANK(B3,$B$3:$R$3,TRUE)+COUNTIF($B$3:$B3,B3)-1<=2 and I will copy this formula to many rows below it. Many thanks ... Jeff "Frank Kabel" wrote: Hi I tested it for your data example and it worked for me: - in which cells is your data exactly - what is the exact formula you have entered -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... This still highlights the repeated number (2 in my example). HELP! "Frank Kabel" wrote: Hi if your data is in A1:A10 try the following: - select this range - goto the conditional format dialog - enter the following formula: =RANK(A1,$A$1:$A$10,TRUE)+COUNTIF($A$1:$A1,A1)-1<=2 - choose your color -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... I would like to highlight data using Conditional Formatting from a list of numbers but only want to highlight the 2 smallest numbers. E.g. Data list = 6,7,8,1,9,2,4,2 The result should highlight the number 1 BUT ONLY THE FIRST 2 - I don't want both 2s highlighted. Thanks, |
To clarify ... it doesn't have to be the FIRST 2 ... but rather either 2.
"Putty" wrote: Woops I meant ... =RANK(B3,$B3:$R3,TRUE)+COUNTIF($B3:$R3,B3)-1<=2 and my data is: 7,10,9,7,7,9,6,4,6,2,1,3,2,6,7,8,7 The 1 and the first 2 should get highlighted. Thanks again. "Putty" wrote: My data is in B3:R3 and I used: =RANK(B3,$B$3:$R$3,TRUE)+COUNTIF($B$3:$B3,B3)-1<=2 and I will copy this formula to many rows below it. Many thanks ... Jeff "Frank Kabel" wrote: Hi I tested it for your data example and it worked for me: - in which cells is your data exactly - what is the exact formula you have entered -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... This still highlights the repeated number (2 in my example). HELP! "Frank Kabel" wrote: Hi if your data is in A1:A10 try the following: - select this range - goto the conditional format dialog - enter the following formula: =RANK(A1,$A$1:$A$10,TRUE)+COUNTIF($A$1:$A1,A1)-1<=2 - choose your color -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... I would like to highlight data using Conditional Formatting from a list of numbers but only want to highlight the 2 smallest numbers. E.g. Data list = 6,7,8,1,9,2,4,2 The result should highlight the number 1 BUT ONLY THE FIRST 2 - I don't want both 2s highlighted. Thanks, |
Hi
use: =RANK(B3,$B$3:$R$3,TRUE)+COUNTIF($B$3:B$3,B3)-1<=2 -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... My data is in B3:R3 and I used: =RANK(B3,$B$3:$R$3,TRUE)+COUNTIF($B$3:$B3,B3)-1<=2 and I will copy this formula to many rows below it. Many thanks ... Jeff "Frank Kabel" wrote: Hi I tested it for your data example and it worked for me: - in which cells is your data exactly - what is the exact formula you have entered -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... This still highlights the repeated number (2 in my example). HELP! "Frank Kabel" wrote: Hi if your data is in A1:A10 try the following: - select this range - goto the conditional format dialog - enter the following formula: =RANK(A1,$A$1:$A$10,TRUE)+COUNTIF($A$1:$A1,A1)-1<=2 - choose your color -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... I would like to highlight data using Conditional Formatting from a list of numbers but only want to highlight the 2 smallest numbers. E.g. Data list = 6,7,8,1,9,2,4,2 The result should highlight the number 1 BUT ONLY THE FIRST 2 - I don't want both 2s highlighted. Thanks, |
Frank ... Thank you so much! You the man!
Jeff "Frank Kabel" wrote: Hi use: =RANK(B3,$B$3:$R$3,TRUE)+COUNTIF($B$3:B$3,B3)-1<=2 -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... My data is in B3:R3 and I used: =RANK(B3,$B$3:$R$3,TRUE)+COUNTIF($B$3:$B3,B3)-1<=2 and I will copy this formula to many rows below it. Many thanks ... Jeff "Frank Kabel" wrote: Hi I tested it for your data example and it worked for me: - in which cells is your data exactly - what is the exact formula you have entered -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... This still highlights the repeated number (2 in my example). HELP! "Frank Kabel" wrote: Hi if your data is in A1:A10 try the following: - select this range - goto the conditional format dialog - enter the following formula: =RANK(A1,$A$1:$A$10,TRUE)+COUNTIF($A$1:$A1,A1)-1<=2 - choose your color -- Regards Frank Kabel Frankfurt, Germany "Putty" schrieb im Newsbeitrag ... I would like to highlight data using Conditional Formatting from a list of numbers but only want to highlight the 2 smallest numbers. E.g. Data list = 6,7,8,1,9,2,4,2 The result should highlight the number 1 BUT ONLY THE FIRST 2 - I don't want both 2s highlighted. Thanks, |
| All times are GMT +1. The time now is 01:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com