How can I merge these two formulas?

Question

I'm trying to merge these two formulas so that it only works out the streak if the cell has a number (each cell has a formula in it but is blank)

Formula 1

=MAX(SCAN(0,B1:B,LAMBDA(a,b,(a+(b<>0))*(b<>0))))

Formula 2

=IF(ISNUMBER(B1:B).....Do a thing.....)

Some Test Data:

Daily session count
100
200
400
100
200
=IF(C9<>"",SUM(C9-C8),"")
200
300
=IF(C9<>"",SUM(C9-C8),"")
=IF(C9<>"",SUM(C9-C8),"")
=IF(C9<>"",SUM(C9-C8),"")
=IF(C9<>"",SUM(C9-C8),"")

The issue is related to this question: Excel - How to count streaks of non-zero numbers on one column

So I just need Formula 1 to only function if the cell is a number — KingLouie, Mar 16 '22 at 21:29
yes but you are trying to combine a test on an array with a formula that outputs a single result. So it does not make a lot of sense. a picture of the data and expected output would help us help you. — Scott Craner, Mar 16 '22 at 21:30
just change the `0` to `""`: `=MAX(SCAN(0,B1:B,LAMBDA(a,b,(a+(b<>""))*(b<>""))))` — Scott Craner, Mar 16 '22 at 21:32
And if you want to do both `""` and `0` then: `=MAX(SCAN(0,B1:B10,LAMBDA(a,b,(a+((b<>"")*(b<>0)))*(b<>"")*(b<>0))))` — Scott Craner, Mar 16 '22 at 21:37

score 0 · Answer 1 · answered Mar 16 '22 at 21:37

0

Thanks to Scott Craner for the answer here. The final solution was this:

=MAX(SCAN(0,B1:B1000,LAMBDA(a,b,(a+(b<>""))*(b<>0))))

This allowed me to measure the longest streak of numbers in a column full of blank cells (with formulas)

answered Mar 16 '22 at 21:37

KingLouie

399
1
5
13

How can I merge these two formulas?

1 Answers1