Den 2006-05-10 15:44:54 skrev Andy Pepperdine
<[EMAIL PROTECTED]>:
Commented inserted inline.
On Wednesday 10 May 2006 13:55, Johnny Andersson wrote:
Hi!
I tried to, just for fun, make a simple spreadsheet to solve a very
simple
"puzzle", in this case the very famous SEVEN+SEVEN+SIX=TWENTY.
I did this the following way:
Column B and C, rows 5-13:
E 8
I 5
N 2
S 6
T 1
V 7
W 3
X 0
Y 4
Range E5:J8:
S E V E N
S E V E N
+ S I X
----------------
T W E N T Y
Range L5:Q8:
Formulas converting letters in E5:J8 to their corresponding numbers in
B5:C13.
Example: E9 (corresponding to the "W" in "TWENTY") looks like this:
=LETARAD(E9;$B$5:$C$13;2;1)
This is Swedish. In English I guess it would look like this:
=VLOOKUP(E9;$B$5:$C$13;2;1)
S5:S8:
Converts the single character numbers of a row to a multi character
number.
Example: S5 looks like this:
=Q5+10*P5+100*O5+1000*N5+10000*M5
S9 sums S5:S7.
S10:
=OM(S8=S9;"Jippie!";"Attans också!")
Translated to English:
=IF(S8=S9;"Jippie!";"Shit, man!")
So if S8=S9, then the problem is solved.
Now to the question or problem:
It seems like VLOOKUP looks at V and W as the same character! As you can
see, in the table above, V=7, W=3, but E9 still displays 7, not 3 (E9
looks for the W in B5:C13 but it displays the result of the V row
instead)!
I thought that in Swedish V and W compare equal for the purposes of
sorting
names. So it appears the lookup is done using the locale's collating
rules.
So if I switch to another language, this problem does not occur?
Is this a bug or is it supposed to work this way?
In this case I just replaced the formula with "=C11", but sometimes it
might not be possible to find a workaround to the problem.
I realize that in some cases I might want to consider V and W to be the
same character, but sometimes I'd rather not. How about making this
possible to set by the user by adding another parameter to the VLOOKUP
function? Or is there another function that would better suit my needs
in
this particular case?
--
Johnny
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
