Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting with formulas
Nothing here answeres my question so I was hoping someone could help.
For example I have a cell that uses lookup formulas to grab a performance indicator from another spreadsheet. I want the cell to change in the following manor: if cell equals the goal of 17 - background turns green if cell is within 10% of 17 (above or below but not equal to 17) - turn yellow if cell is beyond that 10% (greater than 10% above or below but not equal to 17) - turn red. I couldn't get it to work so I just started with the first condition and I have a cell that equals 17 exactly but does not turn green. If I type 17 over the formula whose result is 17 the cell does turn green. What am I doing wrong? Is it the difference between "Formula is" and "Cell Value is"? What should the formulas be to make this stop light coloring work? Thanks again for all your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting with formulas
Use the Formula Is option.
Let's assume you're dealing with cell A1. Condition 1 - GREEN =A1=17 Condition 2 - YELLOW =AND(A1=15.3,A1<=18.7) Condition 3 - RED =OR(AND(A1<"",A1<15.3),AND(COUNT(A1),A118.7)) -- Biff Microsoft Excel MVP "JICDB" wrote in message ... Nothing here answeres my question so I was hoping someone could help. For example I have a cell that uses lookup formulas to grab a performance indicator from another spreadsheet. I want the cell to change in the following manor: if cell equals the goal of 17 - background turns green if cell is within 10% of 17 (above or below but not equal to 17) - turn yellow if cell is beyond that 10% (greater than 10% above or below but not equal to 17) - turn red. I couldn't get it to work so I just started with the first condition and I have a cell that equals 17 exactly but does not turn green. If I type 17 over the formula whose result is 17 the cell does turn green. What am I doing wrong? Is it the difference between "Formula is" and "Cell Value is"? What should the formulas be to make this stop light coloring work? Thanks again for all your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting with formulas
Thank you so much. I first tried condition 1 and it didn't work. I figure
you know what your talking about so what am I doing wrong. It turns out that if I extend the number of decibles out, the answer isn't really exactly 17 but 16.99. Thank you for helping me and more importantly for restoring my sanity - I thought I was going crazy. "T. Valko" wrote: Use the Formula Is option. Let's assume you're dealing with cell A1. Condition 1 - GREEN =A1=17 Condition 2 - YELLOW =AND(A1=15.3,A1<=18.7) Condition 3 - RED =OR(AND(A1<"",A1<15.3),AND(COUNT(A1),A118.7)) -- Biff Microsoft Excel MVP "JICDB" wrote in message ... Nothing here answeres my question so I was hoping someone could help. For example I have a cell that uses lookup formulas to grab a performance indicator from another spreadsheet. I want the cell to change in the following manor: if cell equals the goal of 17 - background turns green if cell is within 10% of 17 (above or below but not equal to 17) - turn yellow if cell is beyond that 10% (greater than 10% above or below but not equal to 17) - turn red. I couldn't get it to work so I just started with the first condition and I have a cell that equals 17 exactly but does not turn green. If I type 17 over the formula whose result is 17 the cell does turn green. What am I doing wrong? Is it the difference between "Formula is" and "Cell Value is"? What should the formulas be to make this stop light coloring work? Thanks again for all your help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting with formulas
Yeah, those unseen extra decimal places can reek havoc on your sanity!
So, I take it you got it sorted out? If not, just let us know. You might have to do some sort of rounding. -- Biff Microsoft Excel MVP "JICDB" wrote in message ... Thank you so much. I first tried condition 1 and it didn't work. I figure you know what your talking about so what am I doing wrong. It turns out that if I extend the number of decibles out, the answer isn't really exactly 17 but 16.99. Thank you for helping me and more importantly for restoring my sanity - I thought I was going crazy. "T. Valko" wrote: Use the Formula Is option. Let's assume you're dealing with cell A1. Condition 1 - GREEN =A1=17 Condition 2 - YELLOW =AND(A1=15.3,A1<=18.7) Condition 3 - RED =OR(AND(A1<"",A1<15.3),AND(COUNT(A1),A118.7)) -- Biff Microsoft Excel MVP "JICDB" wrote in message ... Nothing here answeres my question so I was hoping someone could help. For example I have a cell that uses lookup formulas to grab a performance indicator from another spreadsheet. I want the cell to change in the following manor: if cell equals the goal of 17 - background turns green if cell is within 10% of 17 (above or below but not equal to 17) - turn yellow if cell is beyond that 10% (greater than 10% above or below but not equal to 17) - turn red. I couldn't get it to work so I just started with the first condition and I have a cell that equals 17 exactly but does not turn green. If I type 17 over the formula whose result is 17 the cell does turn green. What am I doing wrong? Is it the difference between "Formula is" and "Cell Value is"? What should the formulas be to make this stop light coloring work? Thanks again for all your help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting with formulas
Got it - thanks to your formulas. Thanks!
"T. Valko" wrote: Yeah, those unseen extra decimal places can reek havoc on your sanity! So, I take it you got it sorted out? If not, just let us know. You might have to do some sort of rounding. -- Biff Microsoft Excel MVP "JICDB" wrote in message ... Thank you so much. I first tried condition 1 and it didn't work. I figure you know what your talking about so what am I doing wrong. It turns out that if I extend the number of decibles out, the answer isn't really exactly 17 but 16.99. Thank you for helping me and more importantly for restoring my sanity - I thought I was going crazy. "T. Valko" wrote: Use the Formula Is option. Let's assume you're dealing with cell A1. Condition 1 - GREEN =A1=17 Condition 2 - YELLOW =AND(A1=15.3,A1<=18.7) Condition 3 - RED =OR(AND(A1<"",A1<15.3),AND(COUNT(A1),A118.7)) -- Biff Microsoft Excel MVP "JICDB" wrote in message ... Nothing here answeres my question so I was hoping someone could help. For example I have a cell that uses lookup formulas to grab a performance indicator from another spreadsheet. I want the cell to change in the following manor: if cell equals the goal of 17 - background turns green if cell is within 10% of 17 (above or below but not equal to 17) - turn yellow if cell is beyond that 10% (greater than 10% above or below but not equal to 17) - turn red. I couldn't get it to work so I just started with the first condition and I have a cell that equals 17 exactly but does not turn green. If I type 17 over the formula whose result is 17 the cell does turn green. What am I doing wrong? Is it the difference between "Formula is" and "Cell Value is"? What should the formulas be to make this stop light coloring work? Thanks again for all your help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting with formulas
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "JICDB" wrote in message ... Got it - thanks to your formulas. Thanks! "T. Valko" wrote: Yeah, those unseen extra decimal places can reek havoc on your sanity! So, I take it you got it sorted out? If not, just let us know. You might have to do some sort of rounding. -- Biff Microsoft Excel MVP "JICDB" wrote in message ... Thank you so much. I first tried condition 1 and it didn't work. I figure you know what your talking about so what am I doing wrong. It turns out that if I extend the number of decibles out, the answer isn't really exactly 17 but 16.99. Thank you for helping me and more importantly for restoring my sanity - I thought I was going crazy. "T. Valko" wrote: Use the Formula Is option. Let's assume you're dealing with cell A1. Condition 1 - GREEN =A1=17 Condition 2 - YELLOW =AND(A1=15.3,A1<=18.7) Condition 3 - RED =OR(AND(A1<"",A1<15.3),AND(COUNT(A1),A118.7)) -- Biff Microsoft Excel MVP "JICDB" wrote in message ... Nothing here answeres my question so I was hoping someone could help. For example I have a cell that uses lookup formulas to grab a performance indicator from another spreadsheet. I want the cell to change in the following manor: if cell equals the goal of 17 - background turns green if cell is within 10% of 17 (above or below but not equal to 17) - turn yellow if cell is beyond that 10% (greater than 10% above or below but not equal to 17) - turn red. I couldn't get it to work so I just started with the first condition and I have a cell that equals 17 exactly but does not turn green. If I type 17 over the formula whose result is 17 the cell does turn green. What am I doing wrong? Is it the difference between "Formula is" and "Cell Value is"? What should the formulas be to make this stop light coloring work? Thanks again for all your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formulas in conditional formatting | Excel Worksheet Functions | |||
Conditional Formatting-No Formulas | Excel Worksheet Functions | |||
Conditional formatting formulas | Excel Discussion (Misc queries) | |||
Conditional Formatting for Formulas | Excel Discussion (Misc queries) | |||
Conditional Formatting - Formulas | Excel Discussion (Misc queries) |