Ask Your Question
0

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

asked 2016-05-25 19:10:07 +0200

Baalka gravatar image

updated 2020-08-05 11:47:10 +0200

Alex Kemp gravatar image

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 flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-05 11:48:25.183503

Comments

5.0.6 was not the most matured version.

Lupp gravatar imageLupp ( 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.

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

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

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

1 Answer

Sort by » oldest newest most voted
0

answered 2016-05-25 19:44:10 +0200

erAck gravatar image

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

edit flag offensive delete link more

Comments

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.

Baalka gravatar imageBaalka ( 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.

erAck gravatar imageerAck ( 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.

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

Question Tools

1 follower

Stats

Asked: 2016-05-25 19:10:07 +0200

Seen: 182 times

Last updated: May 25 '16