Ask Your Question

Array formula won't update correctly (not even with F9) [closed]

asked 2013-11-13 11:30:05 +0200

jamjamdave gravatar image

updated 2015-09-07 20:53:08 +0200

Alex Kemp gravatar image

image description

columns: A B C D E
row1 Item Value Lookup Count Value

row2 C 23 C 3 23

row3 X 34 - - 56

row4 C 56 - - 67

row5 C 67

row6 X 78

Defined Name ranges:

A2:A6 = Item

B:B = Value

Formulas: D2: =COUNTIF(item,C2)

Array Formulae

E2: {=IF(ROWS(E$2:E2)>D$2,"",INDEX(value,SMALL(IF(item=C$2,ROW(item)),ROWS(E$2:E2))))}

E3: {=IF(ROWS(E$2:E3)>D$2,"",INDEX(value,SMALL(IF(item=C$2,ROW(item)),ROWS(E$2:E3))))}

E4: {=IF(ROWS(E$2:E4)>D$2,"",INDEX(value,SMALL(IF(item=C$2,ROW(item)),ROWS(E$2:E4)))) }

Problem: Sheet as above is calculating correctly. Change C2 from "C" to "X" - the correct values will be displayed in E2 and E3 (34, 78). Change C2 back to "C" - now only E2 and E3 are populated correctly. E4 is blank, instead of having "67". See new screenshot below:

image description

If you push F9 - nothing happens. The only way to recalculate correctly, is to recopy formula E2 down E2:E4. then the correct three values are displayed (23, 45, 56)

LibreOffice version:

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-21 12:26:14.427534

4 answers

Sort by » oldest newest most voted

answered 2014-04-26 23:51:57 +0200

mw gravatar image

This behaviour appears to have been fixed in LibO V4.2.3.3 (released April 2014). It was present in LibO V4.2.1.1.

I can't see which source code change dealt with it.

edit flag offensive delete link more

answered 2013-12-13 13:24:16 +0200

m.a.riosv gravatar image

I think arrays needs a hard recalc [Shift+Ctrl+F9].

edit flag offensive delete link more

answered 2013-12-13 08:07:17 +0200

jheath gravatar image

I have a similar problem - a simple formula doesn't recalculate after a macro operation. I have posted an .ods file that demonstrates the bug at:

The failure to recalculate bug happens in LO, but works fine in LO

edit flag offensive delete link more


I'm glad that I hold myself from upgrading to LO version 4.1.x. Still happy with version 4.0.6. I will wait until version is 4.1.5 or higher.

Paijo gravatar imagePaijo ( 2013-12-13 09:01:42 +0200 )edit

answered 2013-11-13 13:53:25 +0200

jamjamdave gravatar image

OK, it seems to be a bug. From : "I tested your sheet with three versions of LibO V3.6.5.2, V4.0.2.2, V4.1.3.2

The first 2 of these dont’ show the behaviour you describe. The results are recalculated reversibly. The third one shows the bug."

edit flag offensive delete link more

Question Tools



Asked: 2013-11-13 11:30:05 +0200

Seen: 2,538 times

Last updated: Apr 26 '14