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
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."
[@[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).