Letter Puzzle

Hi All,

I'm new to this forum, I'm from Ireland and need some help with an Excel problem. Hopefully the attached will explain clearly what I'm trying to achieve.

Regards to everyone

LETTER PUZZLE.png

"I need to change the values in C4 to C8 which will represent the letters in A4 to A8"

There are no letters in A4 to A8, cells A4 to A8 are blank in the attached spreadsheet
Amd anyway, it looks like you mean rows 5 to 9 not 4 to 8.

Are you looking for a formula solution or VBA?

So, what are you trying to do? Enter a bunch of letters in F6 and H6, calculate the values that they represent and do a division?

Will it always be EFAF/JEH... or can you enter any of the letters in any combination?

If so, what is the maximum number of letters in any one cell?

Please show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

I'd be surprised if there were a reasonable formula-based solution. I think you just need to ratchet through the permut(10, 5) permutations to find one that works.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Here's a way to do it with a little bit of code (a three-line sub, a couple of user-defined functions, and a named formula):

	A	B	C	D	E	F	G	H	I	J	K	L
1	0.836	0.462	0.378	0.850	0.139	0.538	0.868	0.669	0.506	0.226		A1: =RAND()
2
3	A	E	F	H	J
4	2	6	7	1	9							A4: =RANK(A1, $A$1:$J$1) - 1
5
6	EFAF	/	JEH	=	F
7
8	6727	/	961	=	7							A8: =SUMPRODUCT(LOOKUP(MID(A6, RowVec(1, LEN(A6)), 1), $A$3:$E$3, $A$4:$E$4), 10 ^ RowVec(LEN(A6) - 1, 0, -1))
9											6727 / 961 = 7	K9: =Cat(A8:E8, " ")
10											TRUE	K10: =Eval

The numbers are assigned randomly and the worksheet calculated until the expression is true. Takes a few seconds.

The letters in row 3 need to be in alphabetical order.