| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditonal Formatting: Explain This Please
I am trying to repair a command to format conditionally one cell based on the
contents of another cell. Please interpret the following expression. =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1, DOUG ECKERT |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditonal Formatting: Explain This Please
It appears you're only showing part of an expression, or a badly written
formula, but of what shown... Both of the following criteria must be met to return a TRUE result: 1. There must be a total of 16 numbers in the ranges of H150:J150 and M150:Y150 (so, the other cells could be blank or text) 2. There can only be 1 or 0 blank cells in the range K150:L150. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DOUG" wrote: I am trying to repair a command to format conditionally one cell based on the contents of another cell. Please interpret the following expression. =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1, DOUG ECKERT |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditonal Formatting: Explain This Please
Luke: You are right, it is only part of a larger formula. Actually, the
entire thing looks like this... =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)= "FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1) )),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60) It has to do with discerning when people are coming due for training. I believe it says that if any of the dates displayed are within 60 days of today, then the little stoplight in the A column will turn red. (In other rows, some symbols are green, some yellow). There is conditional formatting in the subsequent date columns too. DOUG "Luke M" wrote: It appears you're only showing part of an expression, or a badly written formula, but of what shown... Both of the following criteria must be met to return a TRUE result: 1. There must be a total of 16 numbers in the ranges of H150:J150 and M150:Y150 (so, the other cells could be blank or text) 2. There can only be 1 or 0 blank cells in the range K150:L150. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DOUG" wrote: I am trying to repair a command to format conditionally one cell based on the contents of another cell. Please interpret the following expression. =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1, DOUG ECKERT |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditonal Formatting: Explain This Please
Most curious. First, there are parts of the formula that are useless/not
needed. For instance: COUNTBLANK(AA150)<=1 Is only checking one cell. The cell will either be blank (1) or not (0). either way, condition is true! ....AND(NOT(LEFT(G150,4)="FFZZ"),... This is included in an OR function that already checks this. No need to write it twice. Finally, the "-60" is in the false arguement by itself, not really doing anything. I'm *assuming* it's supposed to be part of the previous bit. Simplfied and re-written: =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1,OR(LEFT(G150,4)="FFZZ",COUNT(Z150)=1) ),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())-60) In English: If there are 16 numbers in first group of ranges, and there is only at most 1 blank in K150:L150, and one of the following is true: (G150 contains "FEZZ" or there is a number in Z150) If TRUE: Then, depending on if G150 contained FEZZ, either subtract today's date from smallest value in range, or 60 days prior to today from smallest value in range. As long as this results in any value not equal to zero, conditional formatting will be triggered. If FALSE: Return a "FALSE" value -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DOUG" wrote: Luke: You are right, it is only part of a larger formula. Actually, the entire thing looks like this... =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)= "FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1) )),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60) It has to do with discerning when people are coming due for training. I believe it says that if any of the dates displayed are within 60 days of today, then the little stoplight in the A column will turn red. (In other rows, some symbols are green, some yellow). There is conditional formatting in the subsequent date columns too. DOUG "Luke M" wrote: It appears you're only showing part of an expression, or a badly written formula, but of what shown... Both of the following criteria must be met to return a TRUE result: 1. There must be a total of 16 numbers in the ranges of H150:J150 and M150:Y150 (so, the other cells could be blank or text) 2. There can only be 1 or 0 blank cells in the range K150:L150. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DOUG" wrote: I am trying to repair a command to format conditionally one cell based on the contents of another cell. Please interpret the following expression. =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1, DOUG ECKERT |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditonal Formatting: Explain This Please
Luke: Wow! It sounds very logical and I shall give it a try.
Thank you very much! DOUG "Luke M" wrote: Most curious. First, there are parts of the formula that are useless/not needed. For instance: COUNTBLANK(AA150)<=1 Is only checking one cell. The cell will either be blank (1) or not (0). either way, condition is true! ...AND(NOT(LEFT(G150,4)="FFZZ"),... This is included in an OR function that already checks this. No need to write it twice. Finally, the "-60" is in the false arguement by itself, not really doing anything. I'm *assuming* it's supposed to be part of the previous bit. Simplfied and re-written: =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1,OR(LEFT(G150,4)="FFZZ",COUNT(Z150)=1) ),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())-60) In English: If there are 16 numbers in first group of ranges, and there is only at most 1 blank in K150:L150, and one of the following is true: (G150 contains "FEZZ" or there is a number in Z150) If TRUE: Then, depending on if G150 contained FEZZ, either subtract today's date from smallest value in range, or 60 days prior to today from smallest value in range. As long as this results in any value not equal to zero, conditional formatting will be triggered. If FALSE: Return a "FALSE" value -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DOUG" wrote: Luke: You are right, it is only part of a larger formula. Actually, the entire thing looks like this... =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)= "FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1) )),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60) It has to do with discerning when people are coming due for training. I believe it says that if any of the dates displayed are within 60 days of today, then the little stoplight in the A column will turn red. (In other rows, some symbols are green, some yellow). There is conditional formatting in the subsequent date columns too. DOUG "Luke M" wrote: It appears you're only showing part of an expression, or a badly written formula, but of what shown... Both of the following criteria must be met to return a TRUE result: 1. There must be a total of 16 numbers in the ranges of H150:J150 and M150:Y150 (so, the other cells could be blank or text) 2. There can only be 1 or 0 blank cells in the range K150:L150. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DOUG" wrote: I am trying to repair a command to format conditionally one cell based on the contents of another cell. Please interpret the following expression. =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1, DOUG ECKERT |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditonal Formatting: Explain This Please
Luke M: Your revised formula yielded "FALSE". I guess that could be
formatted to produce a red icon symbol, or red circle. But, the icon is supposed to produce symbols in a range of red, yellow and green. I am not sure how to do that... DOUG "Luke M" wrote: Most curious. First, there are parts of the formula that are useless/not needed. For instance: COUNTBLANK(AA150)<=1 Is only checking one cell. The cell will either be blank (1) or not (0). either way, condition is true! ...AND(NOT(LEFT(G150,4)="FFZZ"),... This is included in an OR function that already checks this. No need to write it twice. Finally, the "-60" is in the false arguement by itself, not really doing anything. I'm *assuming* it's supposed to be part of the previous bit. Simplfied and re-written: =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1,OR(LEFT(G150,4)="FFZZ",COUNT(Z150)=1) ),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())-60) In English: If there are 16 numbers in first group of ranges, and there is only at most 1 blank in K150:L150, and one of the following is true: (G150 contains "FEZZ" or there is a number in Z150) If TRUE: Then, depending on if G150 contained FEZZ, either subtract today's date from smallest value in range, or 60 days prior to today from smallest value in range. As long as this results in any value not equal to zero, conditional formatting will be triggered. If FALSE: Return a "FALSE" value -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DOUG" wrote: Luke: You are right, it is only part of a larger formula. Actually, the entire thing looks like this... =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)= "FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1) )),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60) It has to do with discerning when people are coming due for training. I believe it says that if any of the dates displayed are within 60 days of today, then the little stoplight in the A column will turn red. (In other rows, some symbols are green, some yellow). There is conditional formatting in the subsequent date columns too. DOUG "Luke M" wrote: It appears you're only showing part of an expression, or a badly written formula, but of what shown... Both of the following criteria must be met to return a TRUE result: 1. There must be a total of 16 numbers in the ranges of H150:J150 and M150:Y150 (so, the other cells could be blank or text) 2. There can only be 1 or 0 blank cells in the range K150:L150. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DOUG" wrote: I am trying to repair a command to format conditionally one cell based on the contents of another cell. Please interpret the following expression. =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1, DOUG ECKERT |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditonal Formatting: Explain This Please
You may want to read the help file on conditional formatting for more
details. A conditional format formula is like a switch, either ON or OFF. If you want 3 different results (red, yellow, green) you would need 3 different formulas. Because of the 60 in this formula, I'm assuming this formula was meant to check the "red" condition. Since it is returning false (based on the data) this is saying that the red light should be off. Again, the middle part of formula is very odd. It does a subtraction, but it doesn't compare it to anything. Generally, you want conditional format formulas to return either TRUE or FALSE. Returning a value is, to say the least, odd and difficult to interpret. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DOUG" wrote: Luke M: Your revised formula yielded "FALSE". I guess that could be formatted to produce a red icon symbol, or red circle. But, the icon is supposed to produce symbols in a range of red, yellow and green. I am not sure how to do that... DOUG "Luke M" wrote: Most curious. First, there are parts of the formula that are useless/not needed. For instance: COUNTBLANK(AA150)<=1 Is only checking one cell. The cell will either be blank (1) or not (0). either way, condition is true! ...AND(NOT(LEFT(G150,4)="FFZZ"),... This is included in an OR function that already checks this. No need to write it twice. Finally, the "-60" is in the false arguement by itself, not really doing anything. I'm *assuming* it's supposed to be part of the previous bit. Simplfied and re-written: =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1,OR(LEFT(G150,4)="FFZZ",COUNT(Z150)=1) ),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY())-60) In English: If there are 16 numbers in first group of ranges, and there is only at most 1 blank in K150:L150, and one of the following is true: (G150 contains "FEZZ" or there is a number in Z150) If TRUE: Then, depending on if G150 contained FEZZ, either subtract today's date from smallest value in range, or 60 days prior to today from smallest value in range. As long as this results in any value not equal to zero, conditional formatting will be triggered. If FALSE: Return a "FALSE" value -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DOUG" wrote: Luke: You are right, it is only part of a larger formula. Actually, the entire thing looks like this... =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1,COUNTBLANK(AA150)<=1,OR(LEFT(G150,4)= "FFZZ",AND(NOT(LEFT(G150,4)="FFZZ"),COUNT(Z150)=1) )),IF(LEFT(G150,4)="FFZZ",MIN(H150:J150,M150:Y150)-TODAY(),MIN(H150:J150,M150:Y150)-TODAY()),-60) It has to do with discerning when people are coming due for training. I believe it says that if any of the dates displayed are within 60 days of today, then the little stoplight in the A column will turn red. (In other rows, some symbols are green, some yellow). There is conditional formatting in the subsequent date columns too. DOUG "Luke M" wrote: It appears you're only showing part of an expression, or a badly written formula, but of what shown... Both of the following criteria must be met to return a TRUE result: 1. There must be a total of 16 numbers in the ranges of H150:J150 and M150:Y150 (so, the other cells could be blank or text) 2. There can only be 1 or 0 blank cells in the range K150:L150. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DOUG" wrote: I am trying to repair a command to format conditionally one cell based on the contents of another cell. Please interpret the following expression. =IF(AND(COUNT(H150:J150,M150:Y150)=16,COUNTBLANK(K 150:L150)<=1, DOUG ECKERT |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditonal formatting | Excel Worksheet Functions | |||
| Conditonal Formatting | Excel Discussion (Misc queries) | |||
| Conditonal Formatting | Excel Worksheet Functions | |||
| conditonal formatting in VB | Excel Worksheet Functions | |||
| Conditonal formatting | Excel Discussion (Misc queries) | |||
Hybrid Mode
