Counting Consecutive Periods In Rows Using Numbers

[Yaaseen23]

Hi All

If anyone can help, it would be much appreciated, I have attached a picture.


I look at large datasets to pick up trends on when devices are not working & will indicate how long it takes to get resolved.
i need a formula to pick up if a device was below 90% uptime, consecutively,2 days in a row. the same formula will be used for 2,3,4& 5 days consecutively.
i highlighted the cells with conditional formatting to visually indicate, although i need the figures.

Regards

[gebobs]

I was able to do something with conditional formatting although it does not highlight the last cell. If there are two in a row, it highlights just the first cell. If there are three in a row, it highlights the first two.

Anyhoo, select the entire range of data. In conditional formatting, go to New Rule, and Use Formula...

Use this formula... =AND(A$2<90,B$2<90)

Set the format you want.

[xor]

If you can accept a helper row.

[xor]

Please disregard from #3. It doesn't work.

[xor]

Maybe this one is better.

[Yaaseen23]

Hi

Thanks for the feedback, although it wont work.
iv added an example, attached
From the pivot table. I need 1 formula in the last column. Hope this helps.

Regards

[xor]

No attachments!

[Yaaseen23]

oh snap, file size too big, will cut out data quick

[Yaaseen23]

here we go, i had to remove alot of data

[xor]

I don't think you are especially helpful.

You posted a picture and I gave a proposal to which your reaction was:

Thanks for the feedback, although it wont work.

without telling why. Now you post a sheet with a lot of percentage numbers (different from your original posting) and a lot of colors without telling what you actually want.

Try to put yourself in someone's place who tries to help you without knowing your problem.

[Yaaseen23]

Hey, I see your frustration and thanks for letting me know. I'm new to this and i didn't see it from your view . Thank you for helping.

Firstly why it wouldn't work that well..I have about 500 000 rows of data, with a helper row, it would be 1 000 000 rows.

The green colour is a day when a device had uptime above 96 %
The yellow colour is a day when a device had a uptime between 90% and 96%
The red colour is a device with an uptime of below 90℅
The first column is device id's
I used conditional formatting to indicate visually, when a device is below 90 ℅ , consecutively, basically only focusing on the red days. I need a figure to indicate how much times a device is down consecutively after the grand total column.
I will use the figure to look at, in which region we have an issue with regards to service level agreements.

So...A formula to drag down will be easy and efficient if that is possible, I can't say.

Would you still help? What will it cost me?
Is that much better info?

[Yaaseen23]

Oh forgot to mention, the benchmark is 96%

[xor]

Yes that is much better info.

Please tell me the results you expect in some cells, for example N2:N11

[Yaaseen23]

Expected info...hope this helps, 3 arguments.

Columns :
Column N - 2 Consecutive days below 90 %
Column O - 3 Consecutive days below 90 %
Column P - 4 Consecutive days below 90 %

Row : 1 to 11 below

3 2 1
3 1 1
2 1 1
3 2 1
3 1 0
3 2 1
2 0 0
2 1 1
2 1 1
2 1 1

[Yaaseen23]

rows actually:

N2 to N11
O2 to O11
P2 to P11