Counting Consecutive Improvements

S

SteveC

Hi, to count consecutive improvements, I convert the following:

ColA ColB ColC ColD ColE ColF ColG ColH ColI
10.2% 9.8% 9.4% 9.2% 9.0% 8.7% 8.6% 7.8% 7.9%

So it becomes
ColS ColT ColU ColV ColW ColX ColY ColZ ColAA
Yes Yes Yes Yes Yes Yes Yes No BlankCell

Then I apply in Col AA
=IF(ISERROR(MATCH("No",S2:Z2,0)-1),8,MATCH("No",S2:Z2,0)-1)

I then get a value of 8

It works great because if there is a "No" in ColS, I get a value of 0, if
Yes Yes No, I get 2, etc.

MY QUESTION:
Is there a way to do this without "helper cells" that will work directly
with the percent changes instead?

I tried a nested if statement, but it doesn't work...

A thread concerning the nested if approach is here, but the formula didn't
work out correctly (apply it to the above % changes, and you will get 8
instead of 7):

http://www.microsoft.com/office/com...&p=1&mid=e9a64d1f-f484-45e3-aa18-2d90ed708e25



A thread that provided me insight using the match formula is here:

http://groups.google.com/group/micr...de8363d5e13/f4fc9e17ac40e014#f4fc9e17ac40e014

Thanks for any suggestions...
 
Top