help with calculation formula ....

**ujjawal** · 07-27-2009, 07:38 AM

This following array displays the text "True" in one of the cells only if the letter "a" is entered 2 times between cell L7 to CE7...

i would like to change into 4 times... not 2 times...

I tried (+3,0) instead of (+1, 0) in the following formula but it works only in the first 4 cells...

=IF(SUMPRODUCT(--(ISNUMBER(MATCH(IF($L$7:$CE$7="a",COLUMN($L$7:$CE$7)),IF($L$7:$CE$7="a",COLUMN($L$7:$CE$7))+3,0))))>0,"TRUE")

many thanks....

**Ron Coderre** · 07-27-2009, 08:02 AM

Try this:

Please Login or Register  to view this content.

Does that help?

**ujjawal** · 07-27-2009, 10:00 AM

Hi Ron,

thx for the reply ...

the formula you mentioned will highlight into red.. even if there is break in between the "a"... lets say it will count the "a" from the entire rows...

but i want the name field to appear red only if "a" is entered 4 times continuously (consequitively).... not if there there is "p" in between any of 4 "a"...

at the moment lets say: i enter "a" is cell L7, M7, then the name field changes to red... and if I change the "a" in M7 to "p" the redness disappears... but if I enter "a" in cell the following cell the redness comes back again... so i mean it counts the "A from the cells... which is not the requirement...

which is why the conditional formatting refers to cell CF7...(=CF7="True" (then change the colour to red....) ...

is there is any other aleternatives i would appreciate it...

I got the idea from this.... check it out...

http://www.eggheadcafe.com/fileuploa...nsecutiveA.jpg

thx...

**martindwilson** · 07-27-2009, 10:31 AM

try this array
=MAX(FREQUENCY(IF($L$7:$CE$7="a",COLUMN($L$7:$CE$7)),IF($L$7:$CE$7<>"a",COLUMN($L$7:$CE$7))))>=4
returns true if 4 or more together change to = 4 if you only want to find if there are only 4 consecutive "a"'s