Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - multiple criteria
Using Excel 2003...
Trying to compare cells J4:Q4 to cell F4. F4 will be colored yellow if F4all cells in the range of J4:Q4 excluding blanks. If J4 is not , color the cell (or cells if multiple exist with the same value) that have the lowest price in the range J4:Q4. Can anyone suggest the proper formula? Regards, Jon |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - multiple criteria
Sorry - found an error in the original post.
Should be: If F4 is not , color the cell (or cells if multiple exist with the same value) that have the lowest price in the range J4:Q4. "jmcclain" wrote: Using Excel 2003... Trying to compare cells J4:Q4 to cell F4. F4 will be colored yellow if F4all cells in the range of J4:Q4 excluding blanks. If J4 is not , color the cell (or cells if multiple exist with the same value) that have the lowest price in the range J4:Q4. Can anyone suggest the proper formula? Regards, Jon |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - multiple criteria
1) Trying to compare cells J4:Q4 to cell F4. F4 will be colored yellow if
F4all cells in the range of J4:Q4 excluding blanks. Select F4 , open Format | Conditional Formatting and use Formulas Is: =SUMPRODUCT(--(F4J4:Q4))=COUNT(F4:Q4) 2) If F4is not , color the cell (or cells if multiple exist with the same value) that have the lowest price in the range J4:Q4. (I have assumed you meant F4 not J4 in the first few words) Select J4:Q4 and enter this for Formula Is =AND(J4=MIN($J$4:$Q$4),SUMPRODUCT(--($F$4$J$4:$Q$4))<COUNT($F$4:$Q$4)) Debra Dalgleish explains Cond Formatting http://www.contextures.com/xlCondFormat01.html best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "jmcclain" wrote in message ... Using Excel 2003... Trying to compare cells J4:Q4 to cell F4. F4 will be colored yellow if F4all cells in the range of J4:Q4 excluding blanks. If J4 is not , color the cell (or cells if multiple exist with the same value) that have the lowest price in the range J4:Q4. Can anyone suggest the proper formula? Regards, Jon |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - multiple criteria
Hi Bernard,
Let me re-state my issue - the more I re-read my original post, the more I think I could have stated it in a clearer fashion. F4 is our retail J4:Q4 is our competitors retails (some are blanks) I need to highlight F4 if we are the lowest price OR highlight the lowest price(s) in F4:Q4 Can you help? Much appreciation. Jon "Bernard Liengme" wrote: 1) Trying to compare cells J4:Q4 to cell F4. F4 will be colored yellow if F4all cells in the range of J4:Q4 excluding blanks. Select F4 , open Format | Conditional Formatting and use Formulas Is: =SUMPRODUCT(--(F4J4:Q4))=COUNT(F4:Q4) 2) If F4is not , color the cell (or cells if multiple exist with the same value) that have the lowest price in the range J4:Q4. (I have assumed you meant F4 not J4 in the first few words) Select J4:Q4 and enter this for Formula Is =AND(J4=MIN($J$4:$Q$4),SUMPRODUCT(--($F$4$J$4:$Q$4))<COUNT($F$4:$Q$4)) Debra Dalgleish explains Cond Formatting http://www.contextures.com/xlCondFormat01.html best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "jmcclain" wrote in message ... Using Excel 2003... Trying to compare cells J4:Q4 to cell F4. F4 will be colored yellow if F4all cells in the range of J4:Q4 excluding blanks. If J4 is not , color the cell (or cells if multiple exist with the same value) that have the lowest price in the range J4:Q4. Can anyone suggest the proper formula? Regards, Jon . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting - multiple criteria
Bernard,
I figured out the problem and it is working now. Thanks for the direction. "jmcclain" wrote: Hi Bernard, Let me re-state my issue - the more I re-read my original post, the more I think I could have stated it in a clearer fashion. F4 is our retail J4:Q4 is our competitors retails (some are blanks) I need to highlight F4 if we are the lowest price OR highlight the lowest price(s) in F4:Q4 Can you help? Much appreciation. Jon "Bernard Liengme" wrote: 1) Trying to compare cells J4:Q4 to cell F4. F4 will be colored yellow if F4all cells in the range of J4:Q4 excluding blanks. Select F4 , open Format | Conditional Formatting and use Formulas Is: =SUMPRODUCT(--(F4J4:Q4))=COUNT(F4:Q4) 2) If F4is not , color the cell (or cells if multiple exist with the same value) that have the lowest price in the range J4:Q4. (I have assumed you meant F4 not J4 in the first few words) Select J4:Q4 and enter this for Formula Is =AND(J4=MIN($J$4:$Q$4),SUMPRODUCT(--($F$4$J$4:$Q$4))<COUNT($F$4:$Q$4)) Debra Dalgleish explains Cond Formatting http://www.contextures.com/xlCondFormat01.html best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "jmcclain" wrote in message ... Using Excel 2003... Trying to compare cells J4:Q4 to cell F4. F4 will be colored yellow if F4all cells in the range of J4:Q4 excluding blanks. If J4 is not , color the cell (or cells if multiple exist with the same value) that have the lowest price in the range J4:Q4. Can anyone suggest the proper formula? Regards, Jon . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting with multiple criteria | Excel Discussion (Misc queries) | |||
Conditional Formatting, Multiple Criteria | Excel Discussion (Misc queries) | |||
conditional formatting, multiple criteria | Excel Worksheet Functions | |||
conditional formatting, multiple criteria | Excel Discussion (Misc queries) | |||
Multiple Criteria for Conditional Formatting | Excel Worksheet Functions |