'How can I merge these two formulas?
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
Solution 1:[1]
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)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | KingLouie |
