How to calculate current streak of conditional number and excluding blanks

0 votes

Please be patient with me if I'm not too familiar with things around here because this is my first post on StackOverflow. Here's the problem:

Number of connections
 1777
 2500
 4227
  355
11332
 8402
 1972
 2285
 1828
 2249
 1138
 4082
 4121
 1969
 4289
 2728
12000
13000
15000
13000
13250

I write this piece every single day. When there are more than 10,000 connections, I want to be able to retrieve the current streak, and I want this number to be updated each time a new connection is entered.
As an illustration, the streak would be 5 today (12000, 13000, 15000, 13000, and 13250), however if I entered 3200 the following day, I would like the number to return to 0.

I experimented with several fixes for this problem. A resource column I created is titled "CONNECTIONS TO HIDE with."

=IFS(
   [@[Number of connections]]=""; "";
   [@[Number of connections]]>10000; "Win";
   [@[Number of connections]]<10000; "Loss"
)

And then in the streak cell, the formula:

=COUNTA(Table8[CONNECTIONS TO HIDE]) -
 MATCH(2;INDEX(1/(Table8[CONNECTIONS TO HIDE]="Loss");0))

It did not work.

I tried a simpler approach with:

=COUNTA(Table8[Number of connections]) -
 MATCH(2;INDEX(1/(Table8[Number of connections]<10000);0))

but still without result.

The main problem here seems to be that I can't make the formula ignore empty cells that will be filled in later. In fact, it works just fine when I merely drag my selection to the values I've previously entered rather than the entire column (including blanks).

Feb 26, 2023 in Others by narikkadan
• 63,640 points
381 views

1 answer to this question.

0 votes

To solve this  use xmatch if it's available in your version of Excel to do a reverse search:

=ROWS(Table1)-XMATCH(TRUE,Table1[Number of connections]<10000,0,-1)

enter image description here

enter image description here

To ignore blanks:

=LET(data,Table1[Number of connections],filter,FILTER(data,data<>""),COUNT(filter)-XMATCH(TRUE,filter<10000,0,-1))
answered Mar 17, 2023 by Kithuzzz
• 38,000 points

Related Questions In Others

0 votes
1 answer

How to take a screenshot of a current Activity and then share it?

For me, I captured and then shared ...READ MORE

answered Feb 8, 2022 in Others by Soham
• 9,710 points
1,067 views
0 votes
2 answers

How to get the URL of the current tab in Google Chrome?

Its so simple.... If you want to ...READ MORE

answered Aug 12, 2020 in Others by Steve
• 200 points
3,559 views
0 votes
1 answer

How to get current time and date in Android

In order to get the current date ...READ MORE

answered Feb 23, 2022 in Others by Aditya
• 7,680 points
2,125 views
0 votes
1 answer

How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,640 points
1,122 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
3,899 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,970 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
961 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,622 views
0 votes
1 answer

How to calculate median of unsorted values

In vba code: Dim r As Range Dim result 'Set ...READ MORE

answered Feb 23, 2023 in Others by Kithuzzz
• 38,000 points
560 views
0 votes
1 answer

VBA how to calculate depth of items in excel, simliar to a BOM system

Add on the sheet an ActiveX Microsoft ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,000 points
590 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP