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. > > 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? -- Andy Pepperdine On this mailing list help is provided by volunteers. Please subscribe to the mailing list to see all the replies to a query, and reply only to the mailing list at [EMAIL PROTECTED] For FAQ, userguide, see: http://documentation.openoffice.org/ --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
