Creating a Complex What if in Excel: Beyond Nested Conditionals

[outstretchedarm]

I'm trying to create a complex set of variables in an excel
spreadsheet. Since I am not a strong programmer, the best way to do
this is to basically use a very complex if/then argument. However,
excel only supports 7 nested conditionals.

Basically, what i want to do is "teach" excel the notes of a music
keyboard in terms of number.

Ex 60 = C, 61 = Db, 62 = D etc etc...and so on for all 127 notes of a
midi keyboard.

In other words, like an array or list that I can do calculations with.

Any advice?

 

[bobbo]

You can make a User defined function that returns the note for a
numeric value.
In a standard module paste this

Function Nt(Score As Variant) As String

Select Case Score
Case Is = 60
Nt = "C"
End Select

End Function

This only works for a value of 60. Yo will have to write the other 126
cases and the corresponding values. Example

Function Nt(Score As Variant) As String

Select Case Score
Case Is = 60
Nt = "C"
Case is = 62
Nt = "D"
End Select

End Function

All of the cases must be between the Select Case Statement and the End
Select.

HTH

 

[Peter T]

There are many ways with either VBA or Excel formulas, in both look at the
Mod function.

Perhaps a named formula -

Select cell B1
Ctrl-F3 Define names
Name: Note
Refersto (enter following as one line)
=INDEX({"C","Db","D","EB","E","F","Gb","G","Ab","A","Bb","B"},MOD(A1,12)+1)

in the cell offset one to right of your number enter =Note

One way with a UDF

Function fnNote(number)
Static bGotArray As Boolean
Static arrKeys
On Error GoTo errh
If Not bGotArray Then
arrKeys = getKeys
bGotArray = True
End If
x = arrKeys(number Mod 12)
fnNote = arrKeys(number Mod 12)
Exit Function
errh:
fnNote = CVErr(xlValue)
End Function

Function getKeys()
getKeys = Array("C", "Db", "D", "EB", "E", "F", _
"Gb", "G", "Ab", "A", "Bb", "B")
End Function

=fnNote(A1)

Regards,
Peter T

 

[Omar]

thanks...I need to sit down with this and figure it out. thanks for
the running start.

so would I just paste this code into a cell where I would open up a VBA
module?

 

[Peter T]

so would I just paste this code into a cell where I would open up a VBA

module?

In Excel Alt-F11 to open the VB editor
Select the name of your workbook in the left panel to highlight it
Right-click > insert module
Paste the code in the large right pane
After testing close the module window with the smaller x top right
Collapse the tree against your workbook name
Close the VBE

To use the UDF (user defined function) in a cell type =fnNote(A1) to return
the name of the note in cell A1, which should contain a number between
0-127. Alternatively
=fnNote(60)

Did you try the named formula method

Regards,
Peter T

 

[Omar]

Okay I'm doing that, but the vba code is coming up with a bug, and it
point to the line that says:

Function getKeys()

any ideas?

 

[Peter T]

I just tried the code as posted and it worked fine for me.

None of the code should appear with red text.
The last line of the code should read "End Function"

In the VBE, on the main menu, Debug > Compile Project

When code breaks a message box should appear with a description of the
error - what does it say.

Regards,
Peter T

 

[Omar]

okay, i can't get the VB one to work for now. i keep getting #NAME all
over the place.

However, i was able to get the named function to work.

where I plug in "=Note" to the right of a cell with the named function
pasted into it, it gives me the corresponding note name of the midi
number. thanks.

a few more questions:

1) how can i set it up so that I can use it to do transpositions? Liek
how can I make it such that C + 4 = E (meaning midi note 60 + 4 = midi
note 64, which is E)?

2) how can I change the xy orientation of this "=Note" named function?
Liek how can I make such that the number above (instead of the right
of) will give me the note name?


again, thanks

-O

 

[Peter T]

I suspect you are giving up too easily with the UDF, if it's not calculating
many times on the sheet this would be the simplest in terms of overall
flexibility. I can only assume you did not paste the code as posted or you
have two functions with the same name.

2) how can I change the xy orientation of this "=Note" named function?
Liek how can I make such that the number above (instead of the right
of) will give me the note name?

Define a similar name, eg "NoteA", but this time first select A2 instead of
B1, and another similar name, NoteB, with A65536 selected.
=NoteA will return the cell Above and NoteB the cell below.

Alternatively, not as short and simple but more flexible, define a name like
this -

Name: KeyArray
Refersto: ={"C","Db","D","Eb","E","F","Gb","G","Ab","A","Bb","B"}

or put the keys in 12 cells (vertically or horizontally) and Name the 12
cells

and use this formula anywhere you want
=INDEX(KeyArray,MOD(A1,12)+1)

1) how can i set it up so that I can use it to do transpositions? Liek
how can I make it such that C + 4 = E (meaning midi note 60 + 4 = midi
note 64, which is E)?

Define KeyArray as above

=MATCH("C",KeyArray) - 1
for "C" should return value 0

=INDEX(KeyArray,MOD((MATCH(A2,KeyArray,0)-1)+A3,12)+1)
with "C" in A2 and 4 in A3 should return "E"

Regards,
Peter T

 

[Omar]

thanks dude.. I'll go to work on this in the morning. right now its
bedtime!

 

[Omar]

and would i do C + 4 = 64 ?

much thanks

 

[Peter T]

Referring to my previous post -

=MATCH("C",KeyArray) - 1 + 60 + 4

Assuming you want relative to the octave where C = 60

Regards,
Peter T

 

[Omar]

okay, I've basically got this working now, man. I really can't thank
you enough. I've been trying to get this for the longest.

 

[Omar]

hmmm...this line

=MATCH("C",KeyArray) - 1 + 60 + 4

is giving me some inaccurate results

(I changed the "C" to a cell {d6} I want it to translate in to numbers.

however...it brings up 69 in the case of 60

so I changed the formula to:

=MATCH("d6",KeyArray) + 54

so in the case of "C" it gives 60

but in the case of "G" it gives 65, which is "F"

any way to repair this. I know the solution eithewr has to do with
using the MOD function judiciously, or created some CASE conditions to
simply things.

I appreciate all your help.

- Omar

 

[Peter T]

=MATCH("d6",KeyArray) + 54

"d6" (ie as a string not a cell-ref) does not exist in KeyArray !!!

The above returns an incorrect value because the third optional Match
argument defaults to 1. Perhaps it would be better to include the third
argument in the Match function 0, the above would then correctly return N/A.
See Match in help.

The Match should find position of a key that exists in keyarray between
1-12. To make it all work the value then needs to be reduced by -1

Guessing, if C6 = 60 and D6 = 62, perhaps you want to do something like this
=MATCH("D",KeyArray) -1 + (6-1) * 12

Regards,
Peter T

 

[Omar]

no,my bad...I'm afraid we're misunderstanding each other. I take the
blame.

When I made reference to D6, I was referring to a certain cell, with a
note name in it. I want another cell to look at that cell, and based
on the string that it is D6, to properly decide what midi note number
it refers to.

so if D6 is the first cell, and it says "E"

I want D11, a second cell. to properly display "64"

if D6 says "Gb"

I want D11 to display "66"

so on and so forth.

how can we accomplish this?

also, I am flirting with during a parallel application in VisualBasic
5. How can I make a similar formula/function that will run in VB?

thanks
-Omar

 

[Omar]

okay...thanks for the heads up on the different kind of match
functions...

I got it to work like this:

=MATCH(D6,KeyArray,0) + 54


thanks!

any advise on doing something similar in VB. Like where do you put
something like cell references in VB if there are no cell's to speak of?

 

[Peter T]

Quoting from both your previous & this post

In your previous post you said -

if D6 says "Gb"
I want D11 to display "66"

and below -

I got it to work like this:
=MATCH(D6,KeyArray,0) + 54

This doesn't make sense, if D6 = "Gb", for me that returns 61 and not 66

Consider the formula as two parts

first part: MATCH(D6,KeyArray,0) - 1
second part: + a multiple of 12 depending on the relative octave

so to return "Gb" between "C"=60 & "B"=71, ie "Gb"= 66

=MATCH("Gb",KeyArray,0)-1 + (5 * 12)

also, I am flirting with during a parallel application in VisualBasic
5. How can I make a similar formula/function that will run in VB?

Look again at the UDF example I gave in my first post which returns the key
from a given value.

Regards,
Peter T



how can we accomplish this?