The following array formula does not work anymore after update from 4.x to 5.0.6

[Baalka]

Hi,

I previously used the following array formula from my main sheet, which worked just fine.

=INDEX(SunEarthTools5min.B$2:VE$367,E806,MATCH(MIN(IF(ISNUMBER(SunEarthTools5min.B31:VE31),ABS(180-(SunEarthTools5min.$B31:$VE31)),"NA")),ABS(180-SunEarthTools5min.$B31:$VE31),0))

The point of the formula was to find the hour of the zenith (azimuth 180°) each day in a sheet with the day as row and time by increments of 5 mins as columns. My main sheet has in column E the days from 1 to 365, which is used as the row of the index.

After updating yesterday to 5.0.6, this formula dos not work anymore, returning #VALUE, and I can’t find any workaround nor any error in the formula (there shouldn’t be any, as it worked just fine in the previous versions).

The ISNUMBER allows to filter out sunless hours ; removing it from the formula didn’t change anything.

I tried this :

=INDEX(SunEarthTools5min.B$2:VE$367,E779,MATCH(180-MIN(IF(ISNUMBER(SunEarthTools5min.B4:VE4),ABS(180-(SunEarthTools5min.$B4:$VE4)),"")),SunEarthTools5min.$B4:$VE4,0))

Which works, but provides an error (#N/A) for a number of rows for no reason that I can figure out. That’s however not as good a solution, I know it, that’s why I only tried it as a last resort.

So, if anybody can spot an error in my formulas or help, or if it is definitely a bug I should file a report for, I’d be very glad.

Thank you.

[Lupp]

5.0.6 was not the most matured version.

[Baalka]

I first tried the fresh version, I had that problem, thought it was maybe an oversight and reversed to 5.0.6. Tried 5.2.0 dev, wouldn’t launch after installation.

I found a 4.4.1 installer and will try to reverse back to it, to see if it works again so I could resume my calculations without loosing too much time on it. Maybe I simply really missed something and did a poor work, but I checked times and times again and can’t see that.

[Lupp]

For experimental purposes you may prefer to use a portable LibO from Index of /libreoffice/old .

[erAck]

Make sure your data is actually numeric numbers and not just text strings that look like numbers. Also, the “NA” text return value in IF(ISNUMBER(SunEarthTools5min.B31:VE31),ABS(180-(SunEarthTools5min.$B31:$VE31)),"NA") may result in #VALUE! error if used in further arithmetic operations. Somewhere along the lines of 4.3/4.4/ 5.0 stricter error propagation in array/matrix operations was introduced and calculating with text strings doesn’t necessarily silently assume 0 anymore. Also check the settings under Tools - Options - Calc - Formula “Detailed Calculation Settings” Custom Details for available Contents to Numbers conversions since 4.3. See LibreOffice 4.3: Release Notes - The Document Foundation Wiki

[Baalka]

The data is only numerical, and properly formatted as number - general. You are right about the “NA”. tbh, I’m pretty surprised it did work.

I’ve deleted all non-numerical data, and checked the options you linked to, so I can go with :

=INDEX(SunEarthTools5min.B$2:VE$367,E777,MATCH(MIN(ABS(180-(SunEarthTools5min.B2:VE2))),ABS(180-SunEarthTools5min.B2:VE2),0))

Which still gives the same error. I did not find any other option that helped with it.

Thank you for your help.

[erAck]

Entering that formula on a blank sheet named SunEarthTools5min as array formula gives me a column with 366 result cells all empty, no error. So the formula itself works, but not with your data. My crystal ball is fogged and I can’t see anything without the sheet in question. Maybe it helps if you select the array formula range and invoke the Function Wizard (Ctrl+F2) and have a look at the Structure tab to see where the error gets injected.

[Baalka]

Thank you, you solved my issue !

I copied the content exactly as-is in a new document, and it worked …

I suppose somehow the file didn’t live properly through the update ? I have really no idea, as I did strictly no transformation after copying it to a now document.

thanks anyway, I’m happy I didn’t have to do it all over.