Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula, Conditional Formula Needed
I have a spreadsheet where I have a value in cell n7,n19 and n31 I want to
have the cell of those 3 with the lowest number between them to turn green and the other cells stay as they are. Thanks Karl |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula, Conditional Formula Needed
Select range N7:N31
Conditional Formatting =AND(MOD(ROW(N7),12)=7,MIN($N$7,$N$19,$N$31)=N7) Format as Green "Karl" wrote: I have a spreadsheet where I have a value in cell n7,n19 and n31 I want to have the cell of those 3 with the lowest number between them to turn green and the other cells stay as they are. Thanks Karl |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula, Conditional Formula Needed
Try this:
Select the cells of interest: N7, N19, N31 Goto the menu FormatConditional Formatting Formula Is: =INDIRECT(ADDRESS(ROW(),COLUMN()))=MIN(N$7,N$19,N$ 31) Click the Format button Select the style(s) desired OK out Note: if all 3 of those cells are *empty* the format will be applied to all 3 cells. Post back if you need to account for that. Biff "Karl" wrote in message ... I have a spreadsheet where I have a value in cell n7,n19 and n31 I want to have the cell of those 3 with the lowest number between them to turn green and the other cells stay as they are. Thanks Karl |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula, Conditional Formula Needed
I do not have a formula at hand, but I can give you a conditional formatting
that will work using the smart way, but you have to follow all the instructions as is : 1-format n7, n19 and n31 with color green. 1-click on n7 and go to conditional formatting and do this: conditonal formatting value is greater than(then click on n19)and format with automatic color. add another conditional formatting value is greater than( then click on n31)and format with automatic color. Do exactly the same procedure for n19 and for n31( remember 2 conditional formatting for each cell, using greater than... in both and using automatic color for both in both for a grand total of 6 conditional formatting. Test it!!! "Karl" wrote: I have a spreadsheet where I have a value in cell n7,n19 and n31 I want to have the cell of those 3 with the lowest number between them to turn green and the other cells stay as they are. Thanks Karl |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula, Conditional Formula Needed
Thanks for the formula. It works, and less work.
"T. Valko" wrote: Try this: Select the cells of interest: N7, N19, N31 Goto the menu FormatConditional Formatting Formula Is: =INDIRECT(ADDRESS(ROW(),COLUMN()))=MIN(N$7,N$19,N$ 31) Click the Format button Select the style(s) desired OK out Note: if all 3 of those cells are *empty* the format will be applied to all 3 cells. Post back if you need to account for that. Biff "Karl" wrote in message ... I have a spreadsheet where I have a value in cell n7,n19 and n31 I want to have the cell of those 3 with the lowest number between them to turn green and the other cells stay as they are. Thanks Karl |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula, Conditional Formula Needed
You're welcome!
Biff "FC" wrote in message ... Thanks for the formula. It works, and less work. "T. Valko" wrote: Try this: Select the cells of interest: N7, N19, N31 Goto the menu FormatConditional Formatting Formula Is: =INDIRECT(ADDRESS(ROW(),COLUMN()))=MIN(N$7,N$19,N$ 31) Click the Format button Select the style(s) desired OK out Note: if all 3 of those cells are *empty* the format will be applied to all 3 cells. Post back if you need to account for that. Biff "Karl" wrote in message ... I have a spreadsheet where I have a value in cell n7,n19 and n31 I want to have the cell of those 3 with the lowest number between them to turn green and the other cells stay as they are. Thanks Karl |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula, Conditional Formula Needed
To all the experts. I am a beginner however would rhere be any thing wrong with the following? Conditional Formating Use Formula to Determine which cells to format =N7=MIN($N$7,$N$19,$N$31) OK Applies to =$N$7,$N$19,$N$31 Format Color Fill with Color of Choice If all cells are blank, all will be colored. If only one cell has a value, it will show color Bob M. "FC" wrote: Thanks for the formula. It works, and less work. "T. Valko" wrote: Try this: Select the cells of interest: N7, N19, N31 Goto the menu FormatConditional Formatting Formula Is: =INDIRECT(ADDRESS(ROW(),COLUMN()))=MIN(N$7,N$19,N$ 31) Click the Format button Select the style(s) desired OK out Note: if all 3 of those cells are *empty* the format will be applied to all 3 cells. Post back if you need to account for that. Biff "Karl" wrote in message ... I have a spreadsheet where I have a value in cell n7,n19 and n31 I want to have the cell of those 3 with the lowest number between them to turn green and the other cells stay as they are. Thanks Karl |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula, Conditional Formula Needed
I don't know. It looks like these are steps from Excel 2007. I don't have
Excel 2007. Did you try it and it works? Is it acceptable if all the cells are empty then they get colored? Biff "robert morris" wrote in message ... To all the experts. I am a beginner however would rhere be any thing wrong with the following? Conditional Formating Use Formula to Determine which cells to format =N7=MIN($N$7,$N$19,$N$31) OK Applies to =$N$7,$N$19,$N$31 Format Color Fill with Color of Choice If all cells are blank, all will be colored. If only one cell has a value, it will show color Bob M. "FC" wrote: Thanks for the formula. It works, and less work. "T. Valko" wrote: Try this: Select the cells of interest: N7, N19, N31 Goto the menu FormatConditional Formatting Formula Is: =INDIRECT(ADDRESS(ROW(),COLUMN()))=MIN(N$7,N$19,N$ 31) Click the Format button Select the style(s) desired OK out Note: if all 3 of those cells are *empty* the format will be applied to all 3 cells. Post back if you need to account for that. Biff "Karl" wrote in message ... I have a spreadsheet where I have a value in cell n7,n19 and n31 I want to have the cell of those 3 with the lowest number between them to turn green and the other cells stay as they are. Thanks Karl |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula, Conditional Formula Needed
Thanks for your reply. You are correct as to Office 2007 and, yes, I have tried it and it works beautifully. As I said, all three cells are colored if all three are empty. Bob M. "T. Valko" wrote: I don't know. It looks like these are steps from Excel 2007. I don't have Excel 2007. Did you try it and it works? Is it acceptable if all the cells are empty then they get colored? Biff "robert morris" wrote in message ... To all the experts. I am a beginner however would rhere be any thing wrong with the following? Conditional Formating Use Formula to Determine which cells to format =N7=MIN($N$7,$N$19,$N$31) OK Applies to =$N$7,$N$19,$N$31 Format Color Fill with Color of Choice If all cells are blank, all will be colored. If only one cell has a value, it will show color Bob M. "FC" wrote: Thanks for the formula. It works, and less work. "T. Valko" wrote: Try this: Select the cells of interest: N7, N19, N31 Goto the menu FormatConditional Formatting Formula Is: =INDIRECT(ADDRESS(ROW(),COLUMN()))=MIN(N$7,N$19,N$ 31) Click the Format button Select the style(s) desired OK out Note: if all 3 of those cells are *empty* the format will be applied to all 3 cells. Post back if you need to account for that. Biff "Karl" wrote in message ... I have a spreadsheet where I have a value in cell n7,n19 and n31 I want to have the cell of those 3 with the lowest number between them to turn green and the other cells stay as they are. Thanks Karl |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula, Conditional Formula Needed
FC your sugestion worked could not get the =inderect formula also posted to
work I don't have 2007 though again Thanks "FC" wrote: I do not have a formula at hand, but I can give you a conditional formatting that will work using the smart way, but you have to follow all the instructions as is : 1-format n7, n19 and n31 with color green. 1-click on n7 and go to conditional formatting and do this: conditonal formatting value is greater than(then click on n19)and format with automatic color. add another conditional formatting value is greater than( then click on n31)and format with automatic color. Do exactly the same procedure for n19 and for n31( remember 2 conditional formatting for each cell, using greater than... in both and using automatic color for both in both for a grand total of 6 conditional formatting. Test it!!! "Karl" wrote: I have a spreadsheet where I have a value in cell n7,n19 and n31 I want to have the cell of those 3 with the lowest number between them to turn green and the other cells stay as they are. Thanks Karl |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula, Conditional Formula Needed
Here's a better method.
Select the 3 cells of interest from bottom to top. That is, select cell N31 first, hold down the CTRL key then select N19 and N7. Cell N7 is the active cell. It doesn't have any fill color while N19 and N31 appear "blueish". Goto FormatConditional Formatting Formula Is: =AND(COUNT(N$7,N$19,N$31)0,N7=MIN(N$7,N$19,N$31)) Click the Format button Select the style(s) desired OK out Biff "Karl" wrote in message ... FC your sugestion worked could not get the =inderect formula also posted to work I don't have 2007 though again Thanks "FC" wrote: I do not have a formula at hand, but I can give you a conditional formatting that will work using the smart way, but you have to follow all the instructions as is : 1-format n7, n19 and n31 with color green. 1-click on n7 and go to conditional formatting and do this: conditonal formatting value is greater than(then click on n19)and format with automatic color. add another conditional formatting value is greater than( then click on n31)and format with automatic color. Do exactly the same procedure for n19 and for n31( remember 2 conditional formatting for each cell, using greater than... in both and using automatic color for both in both for a grand total of 6 conditional formatting. Test it!!! "Karl" wrote: I have a spreadsheet where I have a value in cell n7,n19 and n31 I want to have the cell of those 3 with the lowest number between them to turn green and the other cells stay as they are. Thanks Karl |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula, Conditional Formula Needed
This also worked Thank You very much Karl
"T. Valko" wrote: Here's a better method. Select the 3 cells of interest from bottom to top. That is, select cell N31 first, hold down the CTRL key then select N19 and N7. Cell N7 is the active cell. It doesn't have any fill color while N19 and N31 appear "blueish". Goto FormatConditional Formatting Formula Is: =AND(COUNT(N$7,N$19,N$31)0,N7=MIN(N$7,N$19,N$31)) Click the Format button Select the style(s) desired OK out Biff "Karl" wrote in message ... FC your sugestion worked could not get the =inderect formula also posted to work I don't have 2007 though again Thanks "FC" wrote: I do not have a formula at hand, but I can give you a conditional formatting that will work using the smart way, but you have to follow all the instructions as is : 1-format n7, n19 and n31 with color green. 1-click on n7 and go to conditional formatting and do this: conditonal formatting value is greater than(then click on n19)and format with automatic color. add another conditional formatting value is greater than( then click on n31)and format with automatic color. Do exactly the same procedure for n19 and for n31( remember 2 conditional formatting for each cell, using greater than... in both and using automatic color for both in both for a grand total of 6 conditional formatting. Test it!!! "Karl" wrote: I have a spreadsheet where I have a value in cell n7,n19 and n31 I want to have the cell of those 3 with the lowest number between them to turn green and the other cells stay as they are. Thanks Karl |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula, Conditional Formula Needed
You're welcome. Thanks for the feedback!
Biff "Karl" wrote in message ... This also worked Thank You very much Karl "T. Valko" wrote: Here's a better method. Select the 3 cells of interest from bottom to top. That is, select cell N31 first, hold down the CTRL key then select N19 and N7. Cell N7 is the active cell. It doesn't have any fill color while N19 and N31 appear "blueish". Goto FormatConditional Formatting Formula Is: =AND(COUNT(N$7,N$19,N$31)0,N7=MIN(N$7,N$19,N$31)) Click the Format button Select the style(s) desired OK out Biff "Karl" wrote in message ... FC your sugestion worked could not get the =inderect formula also posted to work I don't have 2007 though again Thanks "FC" wrote: I do not have a formula at hand, but I can give you a conditional formatting that will work using the smart way, but you have to follow all the instructions as is : 1-format n7, n19 and n31 with color green. 1-click on n7 and go to conditional formatting and do this: conditonal formatting value is greater than(then click on n19)and format with automatic color. add another conditional formatting value is greater than( then click on n31)and format with automatic color. Do exactly the same procedure for n19 and for n31( remember 2 conditional formatting for each cell, using greater than... in both and using automatic color for both in both for a grand total of 6 conditional formatting. Test it!!! "Karl" wrote: I have a spreadsheet where I have a value in cell n7,n19 and n31 I want to have the cell of those 3 with the lowest number between them to turn green and the other cells stay as they are. Thanks Karl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula or Conditional Formatting Help needed. | Excel Worksheet Functions | |||
Formula needed - is this possible? | Excel Discussion (Misc queries) | |||
Conditional formula - language needed | Excel Worksheet Functions | |||
Conditional Format Formula Needed | Excel Worksheet Functions | |||
Help! Formula Needed | Excel Discussion (Misc queries) |