IF function and the 12 tones of the piano

[Schroeder70]

Hello everyone

I am new to this forum and fairly new to Excel. I have figured out a few things myself but still have a good way to go.

I have a question about the IF-function.

In my function, the numbers from 1 to 12 are equated with the twelve keys on the piano.
C=1
C#=2
D=3
E-flat=4
Etc.

The function looks like this (written in A2):

=IF(A1=1,"C",IF(A1=2,"C#",IF(A1=3,"D",IF(A1=4,"Eb",IF(A1=5,"E",IF(A1=6,"F",IF(A1=7,"F#",IF(A1=8,"G",IF(A1=9,"G#",IF(A1=10,"A",IF(A1=11,"Bb",IF(A1=12,"B"))))))))))))

So far so good. But now I need each tone to equate to multiples of 12 including negative values.

C=1, 13, 25, 37, … but also -11, -23, -35, ….
C#= 2, 14, 26, … but also -10, -22, -34, …
Etc.

I have managed to do this for the first tone “C” and stopped just below 100.

=IF(OR(A1=1,A1=13,A1=25,A1=37,A1=49,A1=61,A1=73,A1=85,A1=97,A1=-11,A1=-23,A1=-35,A1=-47,A1=-59,A1=-71,A1=-83,A1=-95,),"C")


I am not able to write a function that does this for the remaining eleven tones, so that whatever number I write into A1 it would produce a note-name in A2.


Can anyone help?
Thank you.

[Kevin UK]

Hi Schroeder70

May be look at the VLOOKUP function.

VLOOKUP

[M E Good @ XL4M]

Hello Schroeder70 (didn't miss the Peanuts association)

A1 enter number
A2 =INDEX(F1:F12,MATCH(IF(MOD(A1,12)=0,12,MOD(A1,12)),G1:G12,0))
Col F enter tone name in cells 1 to 12
Col G enter 1 to 12 in cells 1 to 12

Hope this does what you require.

[protonLeah]

If the range is from 1 to 12 use:

Please Login or Register  to view this content.

For negative values, try:

Please Login or Register  to view this content.

[Schroeder70]

I have tried it out and it does work.
Many thanks for your help.

[Marcol]

Try this for all numbers, assuming zero, isn't a valid entry.

Please Login or Register  to view this content.

[shg]

Or for positive and negative values,

=TRIM(MID("B BbA G#G F#F E EbD C#C", 23 - 2*MOD(A2-1, 12), 2))

[Schroeder70]

Thanks to all of you for your input.
Marcol, I have tried it out and it works fine; unfortunately I need to include zero. Thanks anyhow.
Thank you to M E Good and shg; I think I will use your formulas as I am essentially trying to build a transposition table (+/- semitones) with numbers that are then converted into pitch-names.

[shg]

You're welcome.

[Marcol]

Why do you need zero?
If "middle C", (Key 40) is 1 ,then -1 (Key 39) is "B(3)"

See this workbook

Sheet1 has 3 workable formulae, enter your value in A1

Note
shgs' formula returns a different result from mine for negative values.
This is not an error the formulae use different parameters.

Sheet2 shows the keyboard and the logic I've used, select from the drop-down to see the result comparisms.

[Schroeder70]

Hi Marcol
Thanks for the files.
I need the formula for a transposition table. I have a sequence of pitches which are then transposed up- or downwards.
If I left out the zero, I would omit a semitone.

Thanks again for your help.
S

[Marcol]

I'm lost now! What semi-tone should zero return?

Seems to me they can all be accounted for without referring to zero, see Sheet2 Columns K & G.

However if you have a solution that suits your needs all is well ...