Counting Consecutive Numbers

[RO24]

Good Morning,

How can I create a lookup to count consecutive numbers > 100%.

I have a sheet with columns of percentages for sales reps and would like to count consecutive quarters that they are over 100%.

Sales 1 109.4% 99.2% 96.3% 101.4% 90.2%
Sales 2 82.3% 78.0% 78.4% 83.3% 84.0%
Sales 3 98.2% 89.0% 98.8% 87.7% 83.0%
Sales 4 96.5% 91.5% 102.1% 96.8% 107.2%
Sales 5 108.7% 109.0% 97.6% 112.1% 102.3%
Sales 6 109.2% 116.1% 119.6% 114.8% 100.0%
Sales 7 100.4% 114.7% 107.9% 107.7% 93.1%

Assuming all information is in one sheet and there are 5 columns of figures. Thanks for the help.

[FlameRetired]

This is in I2:M8 of the attached.

Please Login or Register  to view this content.

Then in H2:H8

Please Login or Register  to view this content.

Does this do what you want?

[newdoverman]

With your % values in b2:f8 enter this formula in H2 and fill across to L2 and fill down. This formula will enter a 1 for each value in the row B2:F2 and if the previous value is 1 will add 1 but if the previous value was 0 then the value will be 1 if the corresponding value in B2:F2 is greater than 0.

Please Login or Register  to view this content.

Enter this in M2 and fill down to give the number of consecutive values >100%

Please Login or Register  to view this content.