VBA count consecutive values under target

[Malakite]

I was wondering if anyone could assist with this.

I currently have a table where the column headers are months and the rows show a reference value e.g 12345

The values within the table are percentages. what I need to do is count for each value the number of previous cells including the current one that are below 90%

so for example

Mar-11 Apr-11 May-11 Jun-11 Jul-11
12345 91% 89% 92% 78% 64%

This would show as

Mar-11 Apr-11 May-11 Jun-11 Jul-11
12345 0 1 0 1 2

So it should start counting previous values once a cell is below 90% and stop should a previous cell be under 90%

I am working in excel but could be done in access if easier.


Any ideas?

Thanks

 

[AshenShugar1873]

Assuming your first month is in column B paste this formula into B below the first percentage.

=IF(COUNTIF(B2,"<90")=0,0,A3+1)

and copy along the row to the end.

If the current month is over 90 it puts in a 0, if not it increments the previous count of values under 90. A3 should obviously be blank or seeded with a 0 unless you want to change the first formula in column B to explicitly be

=IF(COUNTIF(B2,"<90")=0,0,1)