Count Until?

[mcgumby]

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.

 

[mac_see]

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.

 

[Domenic]

Here's a guess...

Assumptions:

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

B5 contains your target amount/result

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!

 

[mcgumby]

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

 

[Domenic]

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...

=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...

=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]

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...

=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...

=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!

 

[mcgumby]

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

 

[Domenic]

...Thanks very much.

Your very welcome!

...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?

 

[mcgumby]

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

 

[Domenic]

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:

=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")

Click Add

Name: Array2

Refers to:

=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))

Click Add

Name: Array3

Refers to:

=IF(Array1,Sheet1!$B4,Array2)<Sheet1!$B4

Click Ok

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

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

Hope this helps!