Calc: formula: why are tildes auto-replacing commas?

English
, , , , , , ,
#1

I want to have the spreadsheet randomly pick a cell from cells defined by certain rows and columns, in this case columns A–Y and rows 5–17. I think this is the formula that’ll do it, predicated on cell BA3 randomly generating a number between 1 and 25:

=CONCATENATE(IF(BA3=1),“A”,(IF(BA3=2),“B”,(IF(BA3=3),“C”,(IF(BA3=4),“D”,(IF(BA3=5),“E”,(IF(BA3=6),“F”,(IF(BA3=7),“G”,(IF(BA3=8),“H”,(IF(BA3=9),“I”,(IF(BA3=10),“J”,(IF(BA3=11),“K”,(IF(BA3=12),“L”,(IF(BA3=13),“M”,(IF(BA3=14),“N”,(IF(BA3=15),“O”,(IF(BA3=16),“P”,(IF(BA3=17),“Q”,(IF(BA3=18),“R”,(IF(BA3=19),“S”,(IF(BA3=20),“T”,(IF(BA3=21),“U”,(IF(BA3=22),“V”,(IF(BA3=23),“W”,(IF(BA3=24),“X”,“Y”))))))))))))))))))))))),RANDBETWEEN(5,17))

But commas are auto-replaced by tildes by LibreOffice Calc, thus:

=CONCATENATE(IF(BA3=1),“A”,(IF(BA3=2)~“B”~(IF(BA3=3)~“C”~(IF(BA3=4)~“D”~(IF(BA3=5)~“E”~(IF(BA3=6)~“F”~(IF(BA3=7)~“G”~(IF(BA3=8)~“H”~(IF(BA3=9)~“I”~(IF(BA3=10)~“J”~(IF(BA3=11)~“K”~(IF(BA3=12)~“L”~(IF(BA3=13)~“M”~(IF(BA3=14)~“N”~(IF(BA3=15)~“O”~(IF(BA3=16)~“P”~(IF(BA3=17)~“Q”~(IF(BA3=18)~“R”~(IF(BA3=19)~“S”~(IF(BA3=20)~“T”~(IF(BA3=21)~“U”~(IF(BA3=22)~“V”~(IF(BA3=23)~“W”~(IF(BA3=24)~“X”~“Y”))))))))))))))))))))))),RANDBETWEEN(5,17))

I’m running LibreOffice 4.2.8.2 (build 4.2.8.2-6.fc20), English, which version I’m told is at its end-of-life, but which is still auto-updated by Fedora 20 Linux, English, itself being auto-updated.

The resulting formula does not do the desired job. What should I do?

Thanks.

Calc: Why is a <~> impossible to delete?
#10

  1. The OFFSET formula given by @karolus is correct, but slightly unsystematic insofar as it handles the row-offset and the column-offset differently without urgent need.

  2. The result aimed on can also be produced by a formula using the INDEX function. This solution may be slightly easier to understand for a beginner.

  3. The originally given formula is trying to calculate the column part of the source cell address by nested IF function calls. First of all this is a bad approach. Secondly it won’t work without comnbining it with the INDIRECT function. Thirdly it is a mess with respect to the usage of IF. Closing the parentheses in every case directly behind the condition will

a) make vanish the syntactical positions for the TRUE case expression and the FALSE case expression as well and

b) create unpaired parentheses by the dozens. The tilde is (as @pierre-ives samyn states correctly) the union operator for ranges in syntactically correct expressions. It is, however, also a kind of error marker in cases where parameter delimiters cannot be interpreted correctly because of rotten syntax.

  1. To calculate one of two random numbers used for equivalent purposes outside and the other one inside of the final expression is an unneeded breach of formal consistency.

  2. A few correct solutions are:

    =OFFSET(A5;RANDBETWEEN(0;12);RANDBETWEEN(0;24)
    =INDEX($A$5:$Y$17;RANDBETWEEN(0;12);RANDBETWEEN(0;24)
    and, if you insist on precalculating the random numbers outside the final expression, and on calculating the address of the source cell first instead of directly accessing it:

    =INDIRECT(CHAR(BA3)&TEXT(BA4;“0”))
    where in BA3 and BA4 respectively you have:
    =RANDBETWEEN(65;89)
    =RANDBETWEEN(5;17)
    Even less recommendable, but also functional would be:
    =CHOOSE(RANDBETWEEN(1;25);“A”;“B”; … ;“X”;“Y”)
    in BA3 and then in your target cell:
    =INDIRECT(BA3&BA4)

Please note: I am using the semicolon as parameter delimiter. This will work in every locale while the comma is only applicable in locales where the comma is not needed as decimal delimiter.

@NickLevinson : You needn’t post a comment as a fake answer. It can be added as a comment in the proper place.

#11

@Lupp: +1 for =INDEX(..) but -1 for unneccessary INDIRECT-ions,
lets start a sophistication-contest:

.....MID("ABCDEFGHIJKLMNOPQRSTUVWXY";RANDBETWEEN(1;25);1) &

;-)

#12

@karolus : Hübsche Idee! But I haven’t a contest in mind. The “solution” with INDIRECT was not recommended but only added to demonstrate in what way the original approach was basically wrong. Your suggestions were perfect (except the 88). As an old teacher I simply felt an urge to point out the main errors and how to avoid them in the future.

[[What about the Python thread? :wink: ]]

#13

@Lupp: which python thread?

#14

@karolus Detailed tutorial regarding LibreOffice to Python macro writing, especially for Calc

#15

Most fora in which I participate prefer chronological posting to avoid interruptions, so I didn’t catch that this one prefers subthreads (in which comments may have to be divided). Thanks for letting me know.

#16

As far as I can tell, the actual question hasn’t been answered here. I too have LibreOffice Calc (now v6, English) replacing a comma, in a formula with a tilde ~.
My formula: =IF($B30=1,C29,IF(B30=2,C29+((D29-C29)*B8,C29+((D29-C29)*B8*2))))
The last comma is being consistently converted to a ~ and a resulting Err:502 Invalid argument
Actually I found the answer: a missing ) immediately before the comma.
But more useful behaviour would be to leave the comma and throw error:508 Pair missing.

#17

(I had to edit the comment above to get the correct formula.)
Entering your formula having replaced the commas with semicolons no error occurred.
Quoting @spicedreams: “(now v6, English)”
What release or prerelease of V6? What is set to English? UI?, locale?, document language?, cell locale?, function names only?
BTW: Please read this thread.

#2

Hallo

=OFFSET(A4;RANDBETWEEN(1;13);RANDBETWEEN(0;24))

you ask:

I want to have the spreadsheet randomly pick a cell from cells defined by certain rows and columns, in this case columns A–Y and rows 5–17.

my Formula above does exactly that, it shows randomly one cellcontent from Range A5:Y17
So far i can see your Formulamess shows only some CellAddressName, if it would work it does like below:

=UNICHAR(RANDBETWEEN(65;88)) & RANDBETWEEN(5;17)
#3

The cell address as a single string is what I wanted, not cell content, so I’d present the randomly-picked cell address to the user. It looks like, after only slight editing, =UNICHAR(RANDBETWEEN(65,89)) & RANDBETWEEN(5,17) will work. (Calc auto-replaced the semicolons with commas (likely by localization not affecting functioning), I replaced “88” with “89”, and I don’t think I’ll need to refer to a two-letter column later, although if I do I’d have to think up a solution for that.)

#4

AFAIK the comma as Argument delimiter is used only in US-English Localisation, most other Localisations use semicolons.

the Original-formulas in your initial-post give no hint about the used Localisation.

#5

That (in the answer beginning with “Hallo”) doesn’t work at all. That gives me a single-digit answer. If my formula was grossly wrong, I’m trying to create one that will give a random result in the form of A7 today, when refreshed G13, when again refreshed B6, and so on. But, apart from that, does anyone know why the tildes show up and how to prevent their replacing commas?

#6

Hi - Tilde (~) is the Union operator. Suppose that you want to count the number of elements> 5 within these ranges:
E1:E4, F1:F2 and G1:G4. You would type your formula like this:

=COUNTIF((E1:E4;F1:F2;G1:G4);">5")

The “interior” parentheses are required to differentiate the second argument of the formula (criteria). The spreadsheet will automatically adjust the formula using the ranges union operator.

=COUNTIF(E1:E4~F1:F2~G1:G4;">5")

Of course we can directly type the formula using the operator.

I do not understand your function. You could “lighten” using the concatenation operator & rather than CONCATENATE. Anyway, this function combines several text strings into one string. So I do not see how this would give the expected result.

I do not understand your IF (BAx = x). The parentheses define a condition without Then_value & Otherwise_value.

#7

this Monsterformula does not combine several … at the End its only one Char from A to Y with some number between 5 and 17

#8

@karolus Sorry you read wrong… I said …CONCATENATE. Anyway, this function combines and not Your formula combines…

Regards

#9

It’s not “IF (BAx = x)” but more like IF (BA3 = x) and it appears my error was in closing the parentheses too soon; I should have caught that. Thank you for noticing.

The ampersand is likely just as good as a concatenator. I just prefer easier-to-read programming to make revising later or by someone else easier, but an ampersand is fine.

#18

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.