Array formula not working

English
#1

We have a card playing club with 6 tournaments per year. The rank in each tournament is entered into the attached table. (How to attach it? Searching “attach image” provides no answer). The two worst of the six results are dropped and only four are counted for the final Points score in column C. The two grey shaded results for each player are omitted.

The formula in Excel to do this used to be {=SUM(SMALL(D2:I2,{1,2,3,4}))} entered with Ctrl-Shift-Enter.
Have copied this formula into Libre Office Calc, but it’s not working. Have tried many variations after studying the Array formula guidelines, but none worked. What is the correct formula to calculate the best (lowest) four results out of six for each player?
Once it is entered in the first row, can it be pulled down to the end of the table?
Are there basic differences between Excel and Libre formulas?
Thank you.

Lookup Part of Text: Randomly Placed Text in Cell
#2

I cannot confirm the statement. Assuming your settings is accepting the comma as parameter delimiter and as either the column delimiter or the row delimiter for inline arrays, the formula should work. It does for me in LibO Calc V5.2.2. in my ‘German (Germany)’ locale with the semicolon in bot tze mentioned roles.

#3

Is there a way to verify whether my settings accept the comma? Have also tried with semi-colons instead of commas, but not working either.

#4

Have now copied the first 6 records from the main table and pasted them into a new sheet, and there the formula works. But the same formula doesn’t work in the original sheet. When entering Ctrl-Shift-Enter, the curly brackets don’t appear. How to change the settings?

#5

As a parameter delimiter the semicolon should be accepted in any locale. The settings for the two delimiters set for use with inline constant arrays you find under > ‘Tools’ > ‘Options’ > ‘LibreOffice Calc’ > ’ ‘Formula’ > ‘Separators’.
You also may try {=SUM(SMALL(D2:I2;ROW(INDIRECT("A1:A4"))))} entered for array-evaluation again, where the four consecutive ranks are generated by the ROW function applied to a range spanning from row 1 to row 4.

(Editing with respct to the comment below:)
Following your report about the settings both the variants (with comma or with semicolon used for the inline array) should work. The suggestion above should work anyway. I’m stumped.
Please try this attached example which was made with LibO Calc V5.2.2. Both the suggested solutions worked there for me (in columns H, I).

#6

Have entered your table in a test sheet and both formulas work. Something seems to be the matter with my original sheet. But what?

#7

Have deleted all formulas on the original table and then entered the same old formula that used to work in Excel. It’s now working properly. Thanks for your help, appreciate it very much. Also keeping your second formula in reserve. But the question remains what could have caused the non-acceptance of the Ctrl-Shift-Enter that makes it work as an array?

#8

I won’t be able to investigate that. However, many documents based on a former solution in Excel were actually imported from one of the variants of xls or xlsx. This process is not (and cannot be) completely reliable. And: A softwre unreasonably developed into a direction of ever growing complexity may be afflicted by a potentially infinite set of possible malfunctions in its generl background. Buffer overflow e.g. cannot only cause vulnerability by malign software, but any kind of malfunction.

#9

Let’s hope Calc will at least never shoot down an airplane with 290 people. I don’t feel sure about Excel. And concerning military action we were more than once at the edge of final destruction caused by the unjustifiable trust into software based systems. Sleep well.

#10

Thanks very much for your help. The problem seems to be solved and the array works now, although I don’t understand why.Will just have to live with it. Have a nice day.

#11

Sorry, maybe I entered my answers in the wrong place.
Under Separators the Function and Array column show a comma, and the array row a semi-colon. The same settings for both the original and the test table, where one works and the other not.

The formula {=SUM(SMALL(D2:I2;ROW(INDIRECT(“A1:A4”))))} is not working. When entering with Ctrl-Shift-Emter, the curly brackets don’t show.

The same is true for the original formula. In the test table the formula works. But when copying that formula and pasting it back to the original table, the curly brackets don’t appear after enter with Ctrl-Shift-Emter. What can prevent those brackets to show?

#12

“When entering with Ctrl-Shift-Emter, the curly brackets don’t show.”
This is a completely new aspect indicating a problem with the UI or something on your system if you actually used the correct key-combinatrion Ctrl+Shift+Enter.

#13

Yes, I used always the same Ctrl-Shift-Enter. In the test table it goes in, in the original not.

#14

Did you completely shut down and restart your computer (and LibO) at least once since the problem showed up?

#15

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