# Array formula won't update correctly (not even with F9) [closed]  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: 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: 4.1.3.2

edit retag 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

Sort by » oldest newest most voted

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.

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

more

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:

https://bugs.freedesktop.org/show_bug.cgi?id=71598

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

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.

OK, it seems to be a bug. From Libreofficeforum.org : "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."

more