Forum Discussion
creating a list showing differences in row numbers for each integer in a database
Hi Nick, I have completed the first part of your task. Now I'm sure you can do your graphic on your own. I didn't understand how it should look.
The formula that generates your data could look like this:
=IFERROR(LET(data,$A$1:$A$99,integer,H$2,diffs,FILTER(ROW(data),data=integer),INDEX(diffs,SEQUENCE(COUNT(diffs)-1,,2))-INDEX(diffs,SEQUENCE(COUNT(diffs)-1,,1))),"")See also the enclosed example file.
dscheikey Thanks, you really know Excel formulas!! I will try it out on some of my database files and get back to you when I am sure it works, but I see with your example, which is very useful, how it works and the logic behind it. This is a great help for me, I will certainly give you credit if and when I publish work involving these databases. For your information, I am studying birdsong, and the "integers" represent specific phrase types sung by a vireo, which can be repeated at different intervals during a song bout. These song bouts can last 30 min or more, with about 1 phrase sung per second. I want to be able to represent the pattern of the occurence of distinct phrase types during such a song bout.
Assuming data=Sheet11:$A:$A entire spill could be generated as
=LET(
ints, UNIQUE(TOROW(data, 3), 1),
diff, LAMBDA(range,v,
LET(
pos, TOCOL(ROW(range) / (range = v), 3),
DROP(pos, 1) - DROP(pos, -1)
)
),
VSTACK(
ints,
IFNA(
DROP(
REDUCE("", ints, LAMBDA(a,v, HSTACK(a, diff(data, v)))),
,
1
),
""
)
)
)- Thanks Sergei, interesting that two such different formulas can be applied, Excel language is a mystery to me. I am currently using dscheikey's formula on my data and it is working. Making graphs of the results is something I was able to do with my few Excel tools!