EXCEL cryptogram problem?? please help! :)

nooraali

New Member
Joined
Jan 26, 2014
Messages
5
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!! :)

hello_zps4e4746fb.jpg
[/URL][/IMG]



 
Last edited:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
clip_image001.gif

<tbody>
</tbody>
34 = brown = Y
6= 104 = E
20 = orange = s
2= yellow = tlooks a bit like YESTERDAY
2yellow100AREDR
3105101BORANGES
4116102CYELLOWT
5gray103DGREENU
6104104EBLUEV
7114105FINDIGOW
8silver106GVIOLETX
9102107HBROWNY
10green108IBLACKZ
11113109JWHITE.
12maroon110KPINK!
13109111LGOLD?
14pink112MGRAY@
15115113NSILVER#
16gold114OMAROON,
17106115PIVORY
18red116QMUSTARD&
19103
20orangeTHE TABLE BELOW IS CALLED MYTABLE
21indigo
22black
23111100Ataking the first number in the message = 34
24100101B
25101102C=vlookup(34,firsttable,2) gives BROWN
26ivory103D
27blue104E=vlookup(BROWN, mytable,2) gives Y
28107105F
29108106G
30mustard107H
31white108I
32110109J
33112110K
34brown111L
112M
113N
114O
the table above is called firsttable115P
116Q
BLACKZ
BLUEV
BROWNY
GOLD?
GRAY@
GREENU
INDIGOW
IVORY
MAROON,
MUSTARD&
ORANGES
PINK!
REDR
SILVER#
VIOLETX
WHITE.
YELLOWT

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

nooraali

New Member
Joined
Jan 26, 2014
Messages
5
Thank you! but does that mean that I have to rewrite the formula for every cell?
 

nooraali

New Member
Joined
Jan 26, 2014
Messages
5
hello i have just tried to apply this to excel and it is not working why do you think this is?
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 26, 2014
Messages
5
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

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,912
Messages
5,598,828
Members
414,260
Latest member
joishe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top