Counting the Most Recent Consecutive Values

[goliathau]

G'day Everyone,

I hope you can help me with a counting issue. I have a spreadsheet that tracks project status changes that's updated weekly. Every week project managers show their overall project status using a RAG status. The colours correspond to numbers as below.

1. Green
2. Amber
3. Red

I want to track how long each project has been in it's current status. The statuses are entered in a new column for each week. The count should start from the current, most recent week (rightmost column) and count backwards until the status changes.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Project Name[/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[TD]Week 4[/TD]
[TD]Week 5[/TD]
[TD]Week 6[/TD]
[TD]Week 7[/TD]
[TD]Week 8[/TD]
[/TR]
[TR]
[TD]Harmony[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]Minerva[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]Imperial[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]










Using the table above as an example, the count for Weeks in the Current Status should be:

• Harmony: 3 (3 weeks in Green/1)
• Minerva: 8 (8 weeks in Amber/2)
• Imperial: 6 (6 weeks in Red/3)
• Sydney: 1 (1 week in Amber/2)

Ideally, the count would consider that new columns are added every week. It should also consider that new projects are added (like Sydney in the table above which was added in Week 3) and will not have any numbers in their first weeks.

Can anyone help me? Thanks in advance!

Cheers,

Scott

 

[Ron Coderre]

With your sample data in B1:J5

Try this ARRAY-FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)

A2: =1/LOOKUP(2,1/FREQUENCY(
IF(C2:Z2=LOOKUP(10^10,C2:Z2),COLUMN(C2:Z2)),
IF(C2:Z2<> LOOKUP(10^10,C2:Z2),COLUMN(C2:Z2))
))))

Copy that formula down through A5.

Is that something you can work with?

 

[goliathau]

With your sample data in B1:J5

Try this ARRAY-FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)

A2: =1/LOOKUP(2,1/FREQUENCY(
IF(C2:Z2=LOOKUP(10^10,C2:Z2),COLUMN(C2:Z2)),
IF(C2:Z2<> LOOKUP(10^10,C2:Z2),COLUMN(C2:Z2))
))))

Copy that formula down through A5.

Is that something you can work with?

Hi Ron,

This is excellent and I can get it to work in the model I provided but not when I try it on my actual data. I replicated the issue when I added more data. When the columns went beyond column Z (AA etc) I am getting an error. I dropped a file on my Google Share here: https://drive.google.com/file/d/1W2TmPBqA6Air2INqTbHOlnBWkmdiPFUU/view?usp=sharing

The first tab has your model with the error when I expanded one row. The second tab shows my actual data. You can see that I am getting some odd numbers that don't appear to be correct.

I really appreciate your help. Any suggestions are welcome!

Ta,

Scott

 

[Ron Coderre]

Not knowing how much data you have, my formula accommodated through Col_Z.
If you believe your data may eventually go through a further column, use that column reference instead.

 

[goliathau]

Not knowing how much data you have, my formula accommodated through Col_Z.
If you believe your data may eventually go through a further column, use that column reference instead.

Thanks Ron. I got it to work. It was my lack of experience with Arrays that was the issue. This is a very elegant solution. I really appreciate your help.