Forum Discussion
Occurrence in an excel serie
Looking at the replies thus far, for all their ingenuity I can't help but think that sometimes the new dynamic array formulas entice us so much as to make us lose sight of simpler set-ups. What's more, iterating functions such as SCAN, BYROW and REDUCE can be expensive, and text-splitting and text-combining operations seem largely inappropriate for a column of numeric ones and zeroes.
Given that the OP is in any case seeking a fixed number of columns in the output (one for ones and one for zeroes), I see no great need for a single spill formula which fills that static number of columns. I would prefer simply:
=LET(
ζ, A1:A15,
ξ, FREQUENCY(IF(ζ = 0, ROW(ζ)), IF(ζ <> 0, ROW(ζ))),
FILTER(ξ, ξ)
)
which can then be duplicated in the next column, changing the 0 to a 1.
Regards
As someone that has moved 100% to the new Excel, I can still see some sense in what you say. I tend to look at Excel as two computing environments that share a common function library. I am eager to dispense with everything that characterises the legacy spreadsheet because I never liked it anyway. The question of interest is then one of whether the new methods have matured sufficiently to offer a complete replacement for tried-and-tested wisdom of the ages.
My first approach was to use SCAN as a simple accumulator
= LET( offset, DROP(VSTACK(-1,data),-1), start?, data<>offset, end?, DROP(VSTACK(start?, TRUE),1), accumulation, SCAN(0, start?, LAMBDA(a,b, IF(b=TRUE, 1, a+1))), frequencies, FILTER(accumulation, end?), frequencies )The next exploits the FREQUENCY function, though not in exactly the same manner as you.
= LET( index, SEQUENCE(15), offset, DROP(VSTACK(data,-1),1), end?, data<>offset, bins, FILTER(index, end?), freq, FREQUENCY(index, bins), DROP(freq, -1) )I will have to consider your use of ξ, η, ζ as variable names, especially since ξ1, η1, ζ1 are valid names that haven't been squandered to support the ludicrous A1 direct referencing notation!
Ah, so you've decided to combine the two required outputs into a single column, in which the rows represent alternating frequency counts for ones and zeroes corresponding to the order in which those values occur in data?
I like it, especially since you could then, if desired, wrap that in WRAPROWS to achieve the desired two-column output.
Regards
Agreed; I should have included WRAPROWS in the final statement
WRAPROWS(DROP(freq,-1), 2, "")
