# Count Until?

#### mcgumby

##### New Member
I have a row of figures for the last 12 weeks, they are results. I need to count backwards from the most recent week, checking if the result reached target.

This is so I can show how many consecutive weeks a target has been reached.

I've toyed with the idea of a macro to do this, however as we may need to add more weeks I'd rather do it with a formulae?

I know there is countif, however this wont do it consecutively?

Has anyone any pointers?

Thanks.

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Can you put up an example worksheet? You can use Colo's HTML Maker utility for displaying your Excel Worksheet on the board. Scroll down for the link.

Here's a guess...

Assumptions:

B2:M2 contain the results for 12 weeks, from oldest to most current

Formula:

=IF(COUNTIF(B2:M2,"<"&B5),MATCH(TRUE,SUBTOTAL(9,OFFSET(B2:M2,,LARGE(IF(B2:M2<>"",COLUMN(B2:M2)-COLUMN(B2)),ROW(INDIRECT("1:"&COLUMNS(B2:M2)))),,1))<B5,0)-1,COUNT(B2:M2))

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

Hope this helps!

Hi, unfortunatley I cant post html from this puter as there is no Excel on it and my work PC is acting up.

Basically cells A4 = Target 100%, and B4 = Threshold 95. Cells D4 to 04 contain the values, in %, 91,94,97,97,99,98,96,94,96,99,98,97. These represent the latest weeks results, with Col O containing last weeks. Cell P4 contains the above modified formulae as in...

=IF(COUNTIF(D4:04,"<"&B4),MATCH(TRUE,SUBTOTAL(9,OFFSET(D4:O4,,LARGE(IF(D4:O4<>"",COLUMN(D4:O4)-COLUMN(D4)),ROW(INDIRECT("1:"&COLUMNS(D4:O4)))),,1))))

However the above Formulae returns "#N/A" and I've never used Offset and Indirect before so cant pin down where its falling over.

Basically if last weeks result was above or equal to threshold then that is one week in succession, and if the week before also meets this scanario then its 2 weeks and so on.

Sorry I cant post HTML I'll try again later.

McG

A couple of things...

1) I just realized that the formula I posted was cut-off and not displayed completely.

2) When you confirm the formula, instead of pressing just ENTER, press CONTROL+SHIFT+ENTER.

Now here's the formula...

Code:
``=IF(COUNTIF(D4:O4,"<"&B4),MATCH(TRUE,SUBTOTAL(9,OFFSET(D4:O4,,LARGE(IF(D4:O4<>"",COLUMN(D4:O4)-COLUMN(D4)),ROW(INDIRECT("1:"&COLUMNS(D4:O4)))),,1))<B4,0)-1,COUNT(D4:O4))``

However, if you want the evaluation to always start from O4 and work backwards, as apposed to start from the first non-empty cell as this formula does, you could use the following formula instead...

Code:
``````=IF(COUNTIF(D4:O4,"<"&B4),MATCH(TRUE,SUBTOTAL(9,OFFSET(D4:O4,,COLUMNS(D4:O4)-(COLUMN(D4:O4)-COLUMN(D4)+1),,1))<B4,0)-1,COUNT(D4:O4))

...which aslo needs to be confirmed with CONTROL+SHIFT+ENTER.``````

Hope this helps!

Domenic said:
A couple of things...

1) I just realized that the formula I posted was cut-off and did not display completely.

2) When you confirm the formula, instead of pressing just ENTER, press CONTROL+SHIFT+ENTER.

Now here's the formula...

Code:
``=IF(COUNTIF(D4:O4,"<"&B4),MATCH(TRUE,SUBTOTAL(9,OFFSET(D4:O4,,LARGE(IF(D4:O4<>"",COLUMN(D4:O4)-COLUMN(D4)),ROW(INDIRECT("1:"&COLUMNS(D4:O4)))),,1))<B4,0)-1,COUNT(D4:O4))``

However, if you don't have empty cells, you could use the following formula instead...

Code:
``````=IF(COUNTIF(D4:O4,"<"&B4),MATCH(TRUE,SUBTOTAL(9,OFFSET(D4:O4,,COLUMNS(D4:O4)-(COLUMN(D4:O4)-COLUMN(D4)+1),,1))<B4,0)-1,COUNT(D4:O4))

...which aslo needs to be confirmed with CONTROL+SHIFT+ENTER.``````

Hope this helps!

That works a treat. I've been able to reverse it as well to find if a measure has been low for a number of weeks, ie sub 50%. Thanks very much.

I'll sit and work it all out, ie how it works tomorrow. I now need to add in an error capture as occasionally some weeks will have no data so will show result "n/a". I need to include this in the check as a positive, ie a target/threshold. Expect more questions

McG

mcgumby said:
...Thanks very much.

...I now need to add in an error capture as occasionally some weeks will have no data so will show result "n/a".

Do you mean that D4:O4 will all show "N/A"? And, if so, do you mean the text value "N/A" or the logical value #N/A?

I need to include this in the check as a positive, ie a target/threshold.

Not sure what you mean here. Can you elaborate? An example would be helpful. Also, which formula are you using?

D4:O4 will occasionally show "n/a" representng no data, meaning we havent sampled the site. So if they are having a run of results meeting target then I want to include this in the run as it isnt their fault we havent sampled them.

McG

Okay, that's going to make things a lot more interesting. In that case, try the following...

1) Define the following references:

Select P4

Insert > Name > Define

Name: Array1

Refers to:

Code:
``=COUNTIF(OFFSET(Sheet1!\$D4:\$O4,,LARGE(IF(Sheet1!\$D4:\$O4<>"",COLUMN(Sheet1!\$D4:\$O4)-COLUMN(Sheet1!\$D4)),ROW(INDIRECT("1:"&COLUMNS(Sheet1!\$D4:\$O4)))),,1),"N/A")``

Name: Array2

Refers to:

Code:
``=SUBTOTAL(9,OFFSET(Sheet1!\$D4:\$O4,,LARGE(IF(Sheet1!\$D4:\$O4<>"",COLUMN(Sheet1!\$D4:\$O4)-COLUMN(Sheet1!\$D4)),ROW(INDIRECT("1:"&COLUMNS(Sheet1!\$D4:\$O4)))),,1))``

Name: Array3

Refers to:

Code:
``=IF(Array1,Sheet1!\$B4,Array2)<Sheet1!\$B4``

Click Ok

2) Enter the following formula in P4, and copy down:

Code:
``=IF(COUNTIF(D4:O4,"<"&B4),MATCH(TRUE,Array3,0)-1,COUNTIF(D4:O4,"N/A")+COUNT(D4:O4))``

Hope this helps!

Replies
2
Views
138
Replies
9
Views
360
Replies
1
Views
156
Replies
3
Views
244
Replies
6
Views
280

1,196,501
Messages
6,015,568
Members
441,902
Latest member
alhaste

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back