questions of Excel

[tommylamtikon]

Excel Question

.e.g
A
1 even
2 odd
3 even
4 even
5 odd
6 even
7 even
8 even
9 eee




1...When "even" had coutinusly occured three times then "A9" will output "eee"

Anybody know how to do that?


2...How can i make a field's background change to black colour when the field is equal "black"?


it does not work

this field i wrote a formula which is



"=IF(OR(A11=2,A11=4,A11=6,A11=8,A11=10,A11=11,A11=13,A11=15,A11=17,A11=20,A11=22,A11=24,A11=26,A11=28,A11=29,A11=31,A11=33,A11=35),"Black ","-")"

[Domenic]

1...When "even" had coutinusly occured three times then "A9" will output "eee"

=IF(ISNUMBER(MATCH(TRUE,COUNTIF(OFFSET(A1,ROW(INDIRECT("1:"&ROWS(A1:A8)-2))-1,0,3),"even")=3,0)),"eee","")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

2...How can i make a field's background change to black colour when the field is equal "black"?

Assuming the field in question is B1...

1) Select cell B1

2) Format > Conditional Formatting > Formula Is

3) Enter the following formula:

=$B$1="Black"

4) Choose your format

5) Click Ok

"=IF(OR(A11=2,A11=4,A11=6,A11=8,A11=10,A11=11,A11=13,A11=15,A11=17,A11=20,A11=22,A11=24,A11=26,A11=28,A11=29,A11=31,A11=33,A11=35),"Black ","-")"

Here are a couple of alternatives...

=IF(OR(A11={2,4,6,8,10,11,13,15,17,20,22,24,26,28,29,31,33,35}),"Black","-")

OR

=IF(ISNUMBER(MATCH(A11,{2,4,6,8,10,11,13,15,17,20,22,24,26,28,29,31,33,35},0)),"Black","-")

Hope this helps!