Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's sintaxe or formula, please
Hi!
I have: Time in A2, text in B2, another time in C2 and another text in D2. In A and C columns, the times are incremented one minute. In B column the text is fixed. In C column the text is variable. In Conditionall Format in D2 cell I use the next formulae: =IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0) to write D2 red if condition is true, and work fine, row by row. Now, I would like change formulae to work with a block, for example A2:D100, but I don't know the correct sintax. For another words: A B C D 2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval time between A2 and C2 is < 00:05) 3 09:01 Text2 09:01 Text10 (D3 don't red) 4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval time is < 00:05 between C5 and A5 both with Text1) 5 09:03 Text4 09:03 Text12 6 09:04 Text6 09:04 ... 7 09:05 Text7 09:05 8 09:06 Text8 09:06 9 09:07 Text9 09:07 Text1 (Don't write red because the interval time is 00:05:00 between C9 and A2 both with Text1) .... I thought about =IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF(C2:C100=A2:A100+"00:05:00";1;0) );0) But don't work. I woul like any idea. Thanks in advance. an |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's sintaxe or formula, please
Why is 4 red but 3 isn't?
Do you want them red if all rows match? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "an" wrote in message ... Hi! I have: Time in A2, text in B2, another time in C2 and another text in D2. In A and C columns, the times are incremented one minute. In B column the text is fixed. In C column the text is variable. In Conditionall Format in D2 cell I use the next formulae: =IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0) to write D2 red if condition is true, and work fine, row by row. Now, I would like change formulae to work with a block, for example A2:D100, but I don't know the correct sintax. For another words: A B C D 2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval time between A2 and C2 is < 00:05) 3 09:01 Text2 09:01 Text10 (D3 don't red) 4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval time is < 00:05 between C5 and A5 both with Text1) 5 09:03 Text4 09:03 Text12 6 09:04 Text6 09:04 ... 7 09:05 Text7 09:05 8 09:06 Text8 09:06 9 09:07 Text9 09:07 Text1 (Don't write red because the interval time is 00:05:00 between C9 and A2 both with Text1) ... I thought about =IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF (C2:C100=A2:A100+"00:05:00";1;0));0) But don't work. I woul like any idea. Thanks in advance. an |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's sintaxe or formula, please
Sorry for my delay.
3 isn't red because D4<B4 (Text11<Text3) of where, it does not interest the hour. It only interests. T write red if two equal texts will be in an interval < 5 min Thanks.an "Bob Phillips" wrote: Why is 4 red but 3 isn't? Do you want them red if all rows match? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "an" wrote in message ... Hi! I have: Time in A2, text in B2, another time in C2 and another text in D2. In A and C columns, the times are incremented one minute. In B column the text is fixed. In C column the text is variable. In Conditionall Format in D2 cell I use the next formulae: =IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0) to write D2 red if condition is true, and work fine, row by row. Now, I would like change formulae to work with a block, for example A2:D100, but I don't know the correct sintax. For another words: A B C D 2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval time between A2 and C2 is < 00:05) 3 09:01 Text2 09:01 Text10 (D3 don't red) 4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval time is < 00:05 between C5 and A5 both with Text1) 5 09:03 Text4 09:03 Text12 6 09:04 Text6 09:04 ... 7 09:05 Text7 09:05 8 09:06 Text8 09:06 9 09:07 Text9 09:07 Text1 (Don't write red because the interval time is 00:05:00 between C9 and A2 both with Text1) ... I thought about =IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF (C2:C100=A2:A100+"00:05:00";1;0));0) But don't work. I woul like any idea. Thanks in advance. an |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's sintaxe or formula, please
3 isn't red because D4<B4 (Text11<Text3)
of where, it does not interest the hour. It only interests write red if two equal texts will be in an interval < 5 min Thanks. an "Bob Phillips" wrote: Why is 4 red but 3 isn't? Do you want them red if all rows match? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "an" wrote in message ... Hi! I have: Time in A2, text in B2, another time in C2 and another text in D2. In A and C columns, the times are incremented one minute. In B column the text is fixed. In C column the text is variable. In Conditionall Format in D2 cell I use the next formulae: =IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0) to write D2 red if condition is true, and work fine, row by row. Now, I would like change formulae to work with a block, for example A2:D100, but I don't know the correct sintax. For another words: A B C D 2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval time between A2 and C2 is < 00:05) 3 09:01 Text2 09:01 Text10 (D3 don't red) 4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval time is < 00:05 between C5 and A5 both with Text1) 5 09:03 Text4 09:03 Text12 6 09:04 Text6 09:04 ... 7 09:05 Text7 09:05 8 09:06 Text8 09:06 9 09:07 Text9 09:07 Text1 (Don't write red because the interval time is 00:05:00 between C9 and A2 both with Text1) ... I thought about =IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF (C2:C100=A2:A100+"00:05:00";1;0));0) But don't work. I woul like any idea. Thanks in advance. an |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's sintaxe or formula, please
Sorry, I don't understand. Text11 is not in your example, and you don't
answer whether all cells must be equal in your modified function. I guess English is not your first language. Is there not an Excel forum in your native language? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "an" wrote in message ... 3 isn't red because D4<B4 (Text11<Text3) of where, it does not interest the hour. It only interests write red if two equal texts will be in an interval < 5 min Thanks. an "Bob Phillips" wrote: Why is 4 red but 3 isn't? Do you want them red if all rows match? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "an" wrote in message ... Hi! I have: Time in A2, text in B2, another time in C2 and another text in D2. In A and C columns, the times are incremented one minute. In B column the text is fixed. In C column the text is variable. In Conditionall Format in D2 cell I use the next formulae: =IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0) to write D2 red if condition is true, and work fine, row by row. Now, I would like change formulae to work with a block, for example A2:D100, but I don't know the correct sintax. For another words: A B C D 2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval time between A2 and C2 is < 00:05) 3 09:01 Text2 09:01 Text10 (D3 don't red) 4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval time is < 00:05 between C5 and A5 both with Text1) 5 09:03 Text4 09:03 Text12 6 09:04 Text6 09:04 ... 7 09:05 Text7 09:05 8 09:06 Text8 09:06 9 09:07 Text9 09:07 Text1 (Don't write red because the interval time is 00:05:00 between C9 and A2 both with Text1) ... I thought about =IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF (C2:C100=A2:A100+"00:05:00";1;0));0) But don't work. I woul like any idea. Thanks in advance. an |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's sintaxe or formula, please
Sorry for my English and your patience.
D3 isn't red because A3=C3 but D3<B3 Thanks. an "Bob Phillips" wrote: Sorry, I don't understand. Text11 is not in your example, and you don't answer whether all cells must be equal in your modified function. I guess English is not your first language. Is there not an Excel forum in your native language? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "an" wrote in message ... 3 isn't red because D4<B4 (Text11<Text3) of where, it does not interest the hour. It only interests write red if two equal texts will be in an interval < 5 min Thanks. an "Bob Phillips" wrote: Why is 4 red but 3 isn't? Do you want them red if all rows match? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "an" wrote in message ... Hi! I have: Time in A2, text in B2, another time in C2 and another text in D2. In A and C columns, the times are incremented one minute. In B column the text is fixed. In C column the text is variable. In Conditionall Format in D2 cell I use the next formulae: =IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0) to write D2 red if condition is true, and work fine, row by row. Now, I would like change formulae to work with a block, for example A2:D100, but I don't know the correct sintax. For another words: A B C D 2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval time between A2 and C2 is < 00:05) 3 09:01 Text2 09:01 Text10 (D3 don't red) 4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval time is < 00:05 between C5 and A5 both with Text1) 5 09:03 Text4 09:03 Text12 6 09:04 Text6 09:04 ... 7 09:05 Text7 09:05 8 09:06 Text8 09:06 9 09:07 Text9 09:07 Text1 (Don't write red because the interval time is 00:05:00 between C9 and A2 both with Text1) ... I thought about =IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF (C2:C100=A2:A100+"00:05:00";1;0));0) But don't work. I woul like any idea. Thanks in advance. an |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's sintaxe or formula, please
But A4=C4 and D4<B4 as well, so why one red and the other not?
And I still do not know what you want to achieve. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "an" wrote in message ... Sorry for my English and your patience. D3 isn't red because A3=C3 but D3<B3 Thanks. an "Bob Phillips" wrote: Sorry, I don't understand. Text11 is not in your example, and you don't answer whether all cells must be equal in your modified function. I guess English is not your first language. Is there not an Excel forum in your native language? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "an" wrote in message ... 3 isn't red because D4<B4 (Text11<Text3) of where, it does not interest the hour. It only interests write red if two equal texts will be in an interval < 5 min Thanks. an "Bob Phillips" wrote: Why is 4 red but 3 isn't? Do you want them red if all rows match? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "an" wrote in message ... Hi! I have: Time in A2, text in B2, another time in C2 and another text in D2. In A and C columns, the times are incremented one minute. In B column the text is fixed. In C column the text is variable. In Conditionall Format in D2 cell I use the next formulae: =IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0) to write D2 red if condition is true, and work fine, row by row. Now, I would like change formulae to work with a block, for example A2:D100, but I don't know the correct sintax. For another words: A B C D 2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval time between A2 and C2 is < 00:05) 3 09:01 Text2 09:01 Text10 (D3 don't red) 4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval time is < 00:05 between C5 and A5 both with Text1) 5 09:03 Text4 09:03 Text12 6 09:04 Text6 09:04 ... 7 09:05 Text7 09:05 8 09:06 Text8 09:06 9 09:07 Text9 09:07 Text1 (Don't write red because the interval time is 00:05:00 between C9 and A2 both with Text1) ... I thought about =IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF (C2:C100=A2:A100+"00:05:00";1;0));0) But don't work. I woul like any idea. Thanks in advance. an |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
What's sintaxe or formula, please
Ok.
I would like: In an interval + or - 00:05, the texts in D column cannot be equal of the texts in B column (fixed texts) and change to red colour. For another words: It cannot have same texts in B (fixed) and D columns. Only if intervals times 00:05 If the times for same text will be 00:05 or < 00:05, don't have problem. Yhank you. an "Bob Phillips" wrote: But A4=C4 and D4<B4 as well, so why one red and the other not? And I still do not know what you want to achieve. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "an" wrote in message ... Sorry for my English and your patience. D3 isn't red because A3=C3 but D3<B3 Thanks. an "Bob Phillips" wrote: Sorry, I don't understand. Text11 is not in your example, and you don't answer whether all cells must be equal in your modified function. I guess English is not your first language. Is there not an Excel forum in your native language? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "an" wrote in message ... 3 isn't red because D4<B4 (Text11<Text3) of where, it does not interest the hour. It only interests write red if two equal texts will be in an interval < 5 min Thanks. an "Bob Phillips" wrote: Why is 4 red but 3 isn't? Do you want them red if all rows match? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "an" wrote in message ... Hi! I have: Time in A2, text in B2, another time in C2 and another text in D2. In A and C columns, the times are incremented one minute. In B column the text is fixed. In C column the text is variable. In Conditionall Format in D2 cell I use the next formulae: =IF(D2=B2;IF(B2=D2;IF(C2=A2-"00:05";2;1);IF(C2=A2+"00:05";1;0));0) to write D2 red if condition is true, and work fine, row by row. Now, I would like change formulae to work with a block, for example A2:D100, but I don't know the correct sintax. For another words: A B C D 2 09:00 Text1 09:00 Text1 (D2 is red because B2=D1 and the interval time between A2 and C2 is < 00:05) 3 09:01 Text2 09:01 Text10 (D3 don't red) 4 09:02 Text3 09:02 Text1 (D4 is red because B4<D4 but the interval time is < 00:05 between C5 and A5 both with Text1) 5 09:03 Text4 09:03 Text12 6 09:04 Text6 09:04 ... 7 09:05 Text7 09:05 8 09:06 Text8 09:06 9 09:07 Text9 09:07 Text1 (Don't write red because the interval time is 00:05:00 between C9 and A2 both with Text1) ... I thought about =IF(D2:D100=B2:B100;IF(B2:B100=D2:D100;IF(C2:C100= A2:A100-"00:05:00";2;1);IF (C2:C100=A2:A100+"00:05:00";1;0));0) But don't work. I woul like any idea. Thanks in advance. an |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |