EXCEL cryptogram problem?? please help! :)

[nooraali]

I have to solve this cryptogram by providing formulas in each position of the secret message (with the formulas being able to carry over in each row of the message).

In the secret message, the numbers 2 to 34 have replaced letters and punctuation marks. The key to the cryptogram is in column A, where either a numeric value or color label is listed for each of the numbers in the secret message. The decode tables to the right are for finding which letter or punctuation mark is being represented in the secret message.

Sorry I'm still very new to excel spreadsheet formulas and have never solved a cryptogram before! I know that I have to use the ISNUMBER function but dont know where to start! Thank you very much for the help!!

[/URL][/IMG]

 

[oldbrewer]

<tbody> </tbody>
			34 = brown = Y
			6= 104 = E
			20 = orange = s
			2= yellow = t		looks a bit like YESTERDAY
	2	yellow				100	A		RED	R
	3	105				101	B		ORANGE	S
	4	116				102	C		YELLOW	T
	5	gray				103	D		GREEN	U
	6	104				104	E		BLUE	V
	7	114				105	F		INDIGO	W
	8	silver				106	G		VIOLET	X
	9	102				107	H		BROWN	Y
	10	green				108	I		BLACK	Z
	11	113				109	J		WHITE	.
	12	maroon				110	K		PINK	!
	13	109				111	L		GOLD	?
	14	pink				112	M		GRAY	@
	15	115				113	N		SILVER	#
	16	gold				114	O		MAROON	,
	17	106				115	P		IVORY
	18	red				116	Q		MUSTARD	&
	19	103
	20	orange				THE TABLE BELOW IS CALLED MYTABLE
	21	indigo
	22	black
	23	111				100	A					taking the first number in the message = 34
	24	100				101	B
	25	101				102	C					=vlookup(34,firsttable,2) gives BROWN
	26	ivory				103	D
	27	blue				104	E					=vlookup(BROWN, mytable,2) gives Y
	28	107				105	F
	29	108				106	G
	30	mustard				107	H
	31	white				108	I
	32	110				109	J
	33	112				110	K
	34	brown				111	L
						112	M
						113	N
						114	O
the table above is called firsttable			115	P
						116	Q
						BLACK	Z
						BLUE	V
						BROWN	Y
						GOLD	?
						GRAY	@
						GREEN	U
						INDIGO	W
						IVORY
						MAROON	,
						MUSTARD	&
						ORANGE	S
						PINK	!
						RED	R
						SILVER	#
						VIOLET	X
						WHITE	.
						YELLOW	T

<colgroup><col span="16"></colgroup><tbody>
</tbody>

 

[nooraali]

Thank you! but does that mean that I have to rewrite the formula for every cell?

 

[nooraali]

hello i have just tried to apply this to excel and it is not working why do you think this is?

 

[mole999]

If you take 28 it equals 107

107 equals the letter H

so 28 = H

you need to solve for all parts to establish the quote by 13th Century Persian Rumi

 

[nooraali]

I understand that i can just do this manually for each cell, but how can I come up with formulas that will carry over in each row of the message? thanks!

 

[mole999]

surely this is a test of your knowledge and excel skills as part of course work. If a forum provided a perfect answer do the helpers all get credits

 

[mikerickson]

Imagine a column B, where B2 has the formula

=IFERROR(VLOOKUP(A2, $X2:$Y$18,2,FALSE),VLOOKUP(A2, $AA2:$AB$18, 2, FALSE))

Then consider putting =INDEX($A:$A, D5, 1) in C5

 

[oldbrewer]

B+ , Mike