ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1
12(improv)
2
A0.resolutionNnTracker

This column provides notes that are part of an acceptable ascending scale within the context of the current harmony AND the current tonic. It relies heavily on its helper columns, of course.
3
to be pasted into the initialization row (into 8)
EXPLANATIONS of each step of the formula on this side
4
5
=IF(IF
6
Index( currMidiNn, row(), 1 ) <> "-"
currMidiNn is not a blank
7
,THEN
8
If(IF
9
ISERROR( find( ";" & text( index( currMidiNn, row(), 1 ), "000" ), index( A0.resolutionNnTracker, row() - 1, 1 ) ) )
we are not able to find currMidiNn in the previous entry in this column
10
,THEN
11
index( A0.resolutionNnTracker, row() - 1, 1 )
Just copy the previous entry
12
,
OTHERWISE (we have encountered a note which is a resolution which we are waiting for)
13
MID(
do a complicated formula that uses MID (string extractor)
14
index( A0.resolutionNnTracker, row() - 1, 1 )
removing this note from the tracker string
15
,
16
1
17
,
18
find(
19
";" & text( index( currMidiNn, row(), 1 ), "000" ),
20
index( A0.resolutionNnTracker, row() - 1, 1 )
21
) - 1
22
) & MID(
23
index( A0.resolutionNnTracker, row() - 1, 1 )
24
,
25
find(
26
";" & text( index( currMidiNn, row(), 1 ), "000" ),
27
index( A0.resolutionNnTracker, row() - 1, 1 )
28
) + 4
29
,
30
9999
31
)
32
) & IF(
But here we also have to decided whether this new note needs to be added to the tracker or not
33
OR(SO IF
34
index( degAboveHarmRoot, row(), 1 ) = 1,
it's degree 1 3 or 5
35
index( degAboveHarmRoot, row(), 1 ) = 3,
36
index( degAboveHarmRoot, row(), 1 ) = 5
37
)
38
,THEN
39
""
it is not added
40
,
OTHERWISE
41
IF(
if it's not already in the tracker, add melNoteG1Nn output to the tracker
42
ISERROR(
43
find(
44
";" & text( index( melNoteG1Nn, row(), 1 ), "000" ),
45
index( A0.resolutionNnTracker, row() - 1, 1 )
46
)
47
)
48
,
49
";" & TEXT( index( melNoteG1Nn, row(), 1 ), "000" )
50
,
51
""
52
)
53
54
)
55
,
OTHERWISE (currMidiNn is a blank)
56
index( A0.resolutionNnTracker, row() - 1, 1 )
Return the previous entry in this column
57
58
59
)
60
61
62