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

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.

edit retag close merge delete

5.0.6 was not the most matured version.

( 2016-05-25 20:21:41 +0200 )edit

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.

( 2016-05-25 23:53:32 +0200 )edit

For experimental purposes you may prefer to use a portable LibO from http://downloadarchive.documentfounda... .

( 2016-05-26 06:58:54 +0200 )edit

Sort by » oldest newest most voted

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 https://wiki.documentfoundation.org/R...

more

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.

( 2016-05-25 20:24:30 +0200 )edit

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.

( 2016-05-26 12:51:35 +0200 )edit

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.

( 2016-05-26 15:58:38 +0200 )edit