How to count specific values between other values
Asked Answered
H

5

5

I'm having a hard time figuring this one out.

I have a short list of numbers in column C (C4 to C19).
Those numbers can range from 0 to anything. Never empty.

In column D is a date, in column E is an offset, but this is not relevant to the problem.

I would like in column F, the count of the value zero, before that line, until we find a non-zero value above that.

So, let's say F18. I want to know the count of zeroes in C before C18. The answer is 3. For F17, the answer is 2 (C16 and C15). And so on.

Image

I did try to figure it out with some COUNTIFS and INDEX.... but I'm lost.

Hibbs answered 16/4 at 14:29 Comment(5)
Show what formula you have tried and the expected output how you want to show the count, btw have you tried using SCAN() here?Glori
Hello, I didn't post the formulas because to be honest, I'm not sure where I was trying to go with it. I did not try the SCAN formula. One thing worth noting, is that my excel is in French, so I also have to translate formulas, making it even harder for me.Hibbs
Translation site I used this one, has most functions (I've used the dutch version, hope it helps).Dunkin
It appears that @Lajos Arpad gave the only correct answer because it doesn't include the current row when counting zeroes.Rebeccarebecka
@TomSharpe you are absolutely right. But I realized afterwards that for my application I prefer using the current row because it is some sort of projection for the current day. I didn't think about that at first I guess.Hibbs
D
3

This one does it the other way around (top to bottom):

=IFERROR(SCAN(0,C4:C19,LAMBDA(a,v,IF(v=0,a+1,0))),"")

I hope I'm not breaking any rules, but here is it in French:

=SIERREUR(SCAN(0;C4:C19;LAMBDA(a;v;SI(v=0;a+1;0)));"")
Dunkin answered 16/4 at 14:41 Comment(3)
WOW, thanks a lot. I reversed my table, it doesn't matter which way it is, and this is working just fine!! Only had to translate it.Hibbs
@JessySaint added translation, but not sure if it's accurate though. Luckily it's only 3 functions.Dunkin
My French and working version : =SIERREUR(SCAN(0;C4:C19;LAMBDA(a;v;SI(v=0;a+1;0)));"")Hibbs
G
7

Here is one way doing it using SCAN() as mentioned in comments:

enter image description here


=SCAN(0,C4:C19=0,LAMBDA(x,y,IF(y,x+1,0)))

Revised Formula: Edit -- 4/17/2024 --> Credit to Tom Sharpe Sir and Lajos Arpad Sir, for highlighting the actual logic.

=DROP(VSTACK(0,SCAN(0,C4:C19=0,LAMBDA(x,y,y*(x+1)))),-1)

Glori answered 16/4 at 14:42 Comment(0)
R
6

Here is @Lajos Arpad's solution as a lambda (with apologies):

=LET(data,C4:C19,SCAN(0,SEQUENCE(ROWS(data)),
LAMBDA(a,c,IF(c=1,0,LET(prev,INDEX(data,c-1),a*(prev=0)+(prev=0))))))

enter image description here

Or slightly shorter

=LET(data,C4:C19,SCAN(0,SEQUENCE(ROWS(data)),
LAMBDA(a,c,IF(c=1,0,LET(prev,INDEX(data,c-1),(a+1)*(prev=0))))))

Bonus: can you do it without a lambda? Yes, e.g. find the difference between the number of the previous row and the number of the last row before the current one that has a non-zero in it:

=LET(data,C4:C19,s,SEQUENCE(ROWS(data)),fs,FILTER(s,data<>0),s-XLOOKUP(s-1,fs,fs,0,-1,-1)-1)
Rebeccarebecka answered 16/4 at 21:13 Comment(2)
Tom Sir, appreciate your findings. It has always helped to learn. May I ask ? Will this not work as per the logic stated --> =DROP(VSTACK(0,SCAN(0,C4:C19=0,LAMBDA(x,y,y*(x+1)))),-1)Glori
Yes that's perfect and much shorter.Rebeccarebecka
G
5

My understanding is that in the F column's i'th cell we need the length of 0-segments in C up to, but not including the i'th column, up to the first cell in C which is either not 0 or out of range. If my understanding is correct, then this is the expected result:

enter image description here

and you can achieve this via

=($C4=0) * $F4 + ($C4=0)

in $F5.

Explanation:

  • $C4=0 evaluates to boolean and is converted to numeric when multiplied with something, that is, after the conversion, it will be 0 if it was false and 1 if it was true
  • $F4 is the sum so far, we multiply it with 1 if we had a zero in the previous row and 0 if we had a 1
  • we add 1 if the row above has a 0 and 0 if the row above has a 1
Gerstein answered 16/4 at 14:57 Comment(0)
D
3

This one does it the other way around (top to bottom):

=IFERROR(SCAN(0,C4:C19,LAMBDA(a,v,IF(v=0,a+1,0))),"")

I hope I'm not breaking any rules, but here is it in French:

=SIERREUR(SCAN(0;C4:C19;LAMBDA(a;v;SI(v=0;a+1;0)));"")
Dunkin answered 16/4 at 14:41 Comment(3)
WOW, thanks a lot. I reversed my table, it doesn't matter which way it is, and this is working just fine!! Only had to translate it.Hibbs
@JessySaint added translation, but not sure if it's accurate though. Luckily it's only 3 functions.Dunkin
My French and working version : =SIERREUR(SCAN(0;C4:C19;LAMBDA(a;v;SI(v=0;a+1;0)));"")Hibbs
A
0

Just XMATCHing for fun :)

=LAMBDA(_r,
LET(
_next_largest, 0.1,
_comment, CONCAT("Count of values below '", _next_largest, "' in the rows above"),
_above, LAMBDA(_row_index,  INDEX(_r,1,1):INDEX(_r,_row_index-1,1)),
_indices, SEQUENCE(ROWS(_r)),
_offset_to_matched, LAMBDA(_row_index,_matched, _row_index - 1 - _matched),

MAP(_indices, LAMBDA(i,
IF(i = 1, _comment,
IFNA(_offset_to_matched(i, XMATCH(_next_largest, _above(i),1,-1)), "")
)))))(_r)

Formula and result


Or with SCAN,OFFSET and XMATCH:

=LAMBDA(arr, LET(
_next_largest, 0.1,
_comment, CONCAT("Count of values below '", _next_largest, "' in the rows above"),
SCAN(1,SEQUENCE(ROWS(_r)), LAMBDA(a,_row_index,
IF(_row_index = 1, _comment,
IFNA(_row_index - 1 - XMATCH(0.1,OFFSET(_r,0,0,_row_index-1),1,-1), "")
)))))(_r)
Aoristic answered 26/5 at 13:50 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.