Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting using Formula
Hello,
I am trying to create a formula within excel 2003 which will format a cell depending on whether the criteria is met. So for example, I want to use cell a4, that contains a date value to calculate length in months using todays date then dependent on that outcome, turn cell a10 red, if the length of months is 0-2 and value of cell a10 is more than 240. if length is 3 months then turn red if more than 200 and if length is greater than 3 months, turn red if more than 190 and so on.. Im thinking this requires some VB scripting to work? There could be an easier way to do this. Thanks, WA |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting using Formula
Select A10. Under conditional formatting, first conditions, change first box
to "formula is:". Input this: =OR(AND(DATEDIF($A$4,TODAY(),"m")<3,A10240),AND(D ATEDIF($A$4,TODAY(),"m")=3,A10200),AND(DATEDIF($A $4,TODAY(),"m")3,A10190)) Format with red pattern. Note that this breaks down into 3 OR functions (rewritten for clarity): =OR( AND(DATEDIF($A$4,TODAY(),"m")<3,A10240), AND(DATEDIF($A$4,TODAY(),"m")=3,A10200), AND(DATEDIF($A$4,TODAY(),"m")3,A10190) ) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "FerrariWA" wrote: Hello, I am trying to create a formula within excel 2003 which will format a cell depending on whether the criteria is met. So for example, I want to use cell a4, that contains a date value to calculate length in months using todays date then dependent on that outcome, turn cell a10 red, if the length of months is 0-2 and value of cell a10 is more than 240. if length is 3 months then turn red if more than 200 and if length is greater than 3 months, turn red if more than 190 and so on.. Im thinking this requires some VB scripting to work? There could be an easier way to do this. Thanks, WA |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting using Formula
This does not require VB so long as we can develope one formula that returns
true or false based on the criteria being met. We need to set up a table of your criteria... I created this in Cells D1:E12. 30 240 60 240 90 200 120 190 150 180 180 170 210 160 240 150 270 140 300 130 330 120 360 110 In Cell A10 add the following conditonal format Format - Conditonal Format... Formulas is =A10INDEX($E$2:$E$12,MATCH(A4-TODAY(),$D$1:$D$12,1)) Select your pattern to be Red Select OK -- HTH... Jim Thomlinson "FerrariWA" wrote: Hello, I am trying to create a formula within excel 2003 which will format a cell depending on whether the criteria is met. So for example, I want to use cell a4, that contains a date value to calculate length in months using todays date then dependent on that outcome, turn cell a10 red, if the length of months is 0-2 and value of cell a10 is more than 240. if length is 3 months then turn red if more than 200 and if length is greater than 3 months, turn red if more than 190 and so on.. Im thinking this requires some VB scripting to work? There could be an easier way to do this. Thanks, WA |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting using Formula
Hello,
Thanks for your help however I forgot to add that I want to change the cell to a more colors if a different set of criteria is met. So if it length in months was 0-2 months and value of cell a10 is less than 240 then amber and if length is 0-2 months again and a10 is under 200 then green? "Luke M" wrote: Select A10. Under conditional formatting, first conditions, change first box to "formula is:". Input this: =OR(AND(DATEDIF($A$4,TODAY(),"m")<3,A10240),AND(D ATEDIF($A$4,TODAY(),"m")=3,A10200),AND(DATEDIF($A $4,TODAY(),"m")3,A10190)) Format with red pattern. Note that this breaks down into 3 OR functions (rewritten for clarity): =OR( AND(DATEDIF($A$4,TODAY(),"m")<3,A10240), AND(DATEDIF($A$4,TODAY(),"m")=3,A10200), AND(DATEDIF($A$4,TODAY(),"m")3,A10190) ) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "FerrariWA" wrote: Hello, I am trying to create a formula within excel 2003 which will format a cell depending on whether the criteria is met. So for example, I want to use cell a4, that contains a date value to calculate length in months using todays date then dependent on that outcome, turn cell a10 red, if the length of months is 0-2 and value of cell a10 is more than 240. if length is 3 months then turn red if more than 200 and if length is greater than 3 months, turn red if more than 190 and so on.. Im thinking this requires some VB scripting to work? There could be an easier way to do this. Thanks, WA |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting using Formula
Note that the formula I gave is just for condition 1. You still have 2 other
conditions for which you could create similar formulas. (note that with your example, green will never happen, as anything less than 200 is also less than 240) Jim Thomlinson has also provided a suggestion for dealing with long lists of possible criteria. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "FerrariWA" wrote: Hello, Thanks for your help however I forgot to add that I want to change the cell to a more colors if a different set of criteria is met. So if it length in months was 0-2 months and value of cell a10 is less than 240 then amber and if length is 0-2 months again and a10 is under 200 then green? "Luke M" wrote: Select A10. Under conditional formatting, first conditions, change first box to "formula is:". Input this: =OR(AND(DATEDIF($A$4,TODAY(),"m")<3,A10240),AND(D ATEDIF($A$4,TODAY(),"m")=3,A10200),AND(DATEDIF($A $4,TODAY(),"m")3,A10190)) Format with red pattern. Note that this breaks down into 3 OR functions (rewritten for clarity): =OR( AND(DATEDIF($A$4,TODAY(),"m")<3,A10240), AND(DATEDIF($A$4,TODAY(),"m")=3,A10200), AND(DATEDIF($A$4,TODAY(),"m")3,A10190) ) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "FerrariWA" wrote: Hello, I am trying to create a formula within excel 2003 which will format a cell depending on whether the criteria is met. So for example, I want to use cell a4, that contains a date value to calculate length in months using todays date then dependent on that outcome, turn cell a10 red, if the length of months is 0-2 and value of cell a10 is more than 240. if length is 3 months then turn red if more than 200 and if length is greater than 3 months, turn red if more than 190 and so on.. Im thinking this requires some VB scripting to work? There could be an easier way to do this. Thanks, WA |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting using Formula
Luke,
Thanks for your help so far. You are right, I was just using random data. Does excel have the ability to use a between function? I am trying to finish the formula off so just that bit left to do: =OR(AND(DATEDIF($C$7,TODAY(),"m")<3,M7240), AND(DATEDIF($C$7,TODAY(),"m")3,M7203),AND(DATEDI F($C$7,TODAY(),"m")3,M7190)) The second IF criteria needs to be between 4-6 months. Thanks, WA "Luke M" wrote: Note that the formula I gave is just for condition 1. You still have 2 other conditions for which you could create similar formulas. (note that with your example, green will never happen, as anything less than 200 is also less than 240) Jim Thomlinson has also provided a suggestion for dealing with long lists of possible criteria. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "FerrariWA" wrote: Hello, Thanks for your help however I forgot to add that I want to change the cell to a more colors if a different set of criteria is met. So if it length in months was 0-2 months and value of cell a10 is less than 240 then amber and if length is 0-2 months again and a10 is under 200 then green? "Luke M" wrote: Select A10. Under conditional formatting, first conditions, change first box to "formula is:". Input this: =OR(AND(DATEDIF($A$4,TODAY(),"m")<3,A10240),AND(D ATEDIF($A$4,TODAY(),"m")=3,A10200),AND(DATEDIF($A $4,TODAY(),"m")3,A10190)) Format with red pattern. Note that this breaks down into 3 OR functions (rewritten for clarity): =OR( AND(DATEDIF($A$4,TODAY(),"m")<3,A10240), AND(DATEDIF($A$4,TODAY(),"m")=3,A10200), AND(DATEDIF($A$4,TODAY(),"m")3,A10190) ) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "FerrariWA" wrote: Hello, I am trying to create a formula within excel 2003 which will format a cell depending on whether the criteria is met. So for example, I want to use cell a4, that contains a date value to calculate length in months using todays date then dependent on that outcome, turn cell a10 red, if the length of months is 0-2 and value of cell a10 is more than 240. if length is 3 months then turn red if more than 200 and if length is greater than 3 months, turn red if more than 190 and so on.. Im thinking this requires some VB scripting to work? There could be an easier way to do this. Thanks, WA |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting using Formula
There is not a specific "between" function, as it is created using the
structu AND(x=0,x<=5) This structure checks for x between (inclusive) 0 and 5. Trying to guess what your going for, add an additional conditional in the AND functions I listed, such as: AND(...,M7240,M7<270) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "FerrariWA" wrote: Luke, Thanks for your help so far. You are right, I was just using random data. Does excel have the ability to use a between function? I am trying to finish the formula off so just that bit left to do: =OR(AND(DATEDIF($C$7,TODAY(),"m")<3,M7240), AND(DATEDIF($C$7,TODAY(),"m")3,M7203),AND(DATEDI F($C$7,TODAY(),"m")3,M7190)) The second IF criteria needs to be between 4-6 months. Thanks, WA "Luke M" wrote: Note that the formula I gave is just for condition 1. You still have 2 other conditions for which you could create similar formulas. (note that with your example, green will never happen, as anything less than 200 is also less than 240) Jim Thomlinson has also provided a suggestion for dealing with long lists of possible criteria. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "FerrariWA" wrote: Hello, Thanks for your help however I forgot to add that I want to change the cell to a more colors if a different set of criteria is met. So if it length in months was 0-2 months and value of cell a10 is less than 240 then amber and if length is 0-2 months again and a10 is under 200 then green? "Luke M" wrote: Select A10. Under conditional formatting, first conditions, change first box to "formula is:". Input this: =OR(AND(DATEDIF($A$4,TODAY(),"m")<3,A10240),AND(D ATEDIF($A$4,TODAY(),"m")=3,A10200),AND(DATEDIF($A $4,TODAY(),"m")3,A10190)) Format with red pattern. Note that this breaks down into 3 OR functions (rewritten for clarity): =OR( AND(DATEDIF($A$4,TODAY(),"m")<3,A10240), AND(DATEDIF($A$4,TODAY(),"m")=3,A10200), AND(DATEDIF($A$4,TODAY(),"m")3,A10190) ) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "FerrariWA" wrote: Hello, I am trying to create a formula within excel 2003 which will format a cell depending on whether the criteria is met. So for example, I want to use cell a4, that contains a date value to calculate length in months using todays date then dependent on that outcome, turn cell a10 red, if the length of months is 0-2 and value of cell a10 is more than 240. if length is 3 months then turn red if more than 200 and if length is greater than 3 months, turn red if more than 190 and so on.. Im thinking this requires some VB scripting to work? There could be an easier way to do this. Thanks, WA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help with a formula or conditional formatting | Excel Worksheet Functions | |||
what formula do I use for conditional formatting? | Excel Worksheet Functions | |||
Conditional formatting is set with a formula, but now I need to ch | Excel Discussion (Misc queries) | |||
Conditional formatting formula | Excel Worksheet Functions | |||
conditional formatting with FORMULA... Please HELP! | Excel Discussion (Misc queries) |