Array formula not working

calc

asked 2016-11-11 19:44:12 +0100

peri1224
1 ●1 ●1 ●2

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.

edit retag flag offensive close merge delete

Comments

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.

Lupp ( 2016-11-11 20:10:58 +0100 )edit

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.

peri1224 ( 2016-11-11 20:47:23 +0100 )edit

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?

peri1224 ( 2016-11-11 21:12:11 +0100 )edit

add a comment

2 Answers

Sort by » oldest newest most voted

answered 2016-11-11 21:00:11 +0100

Lupp
12135 ●33 ●91 ●173

updated 2016-11-11 21:45:19 +0100

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).

edit flag offensive delete link

Comments

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

peri1224 ( 2016-11-11 22:10:21 +0100 )edit

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?

peri1224 ( 2016-11-12 07:37:01 +0100 )edit

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.

Lupp ( 2016-11-12 14:21:08 +0100 )edit

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.

Lupp ( 2016-11-12 14:30:42 +0100 )edit

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.

peri1224 ( 2016-11-14 16:44:28 +0100 )edit

add a comment

answered 2016-11-11 21:28:09 +0100

peri1224
1 ●1 ●1 ●2

updated 2016-11-11 21:47:15 +0100

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?

edit flag offensive delete link

Comments

"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.

Lupp ( 2016-11-11 21:51:26 +0100 )edit

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

peri1224 ( 2016-11-11 22:12:36 +0100 )edit

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

Lupp ( 2016-11-11 23:10:30 +0100 )edit

add a comment

Array formula not working

Comments

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

Array formula not working edit

Comments

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

Array formula not working