Excel Array in Calc?

[jasonsgroovemachine]

I’m considering swapping over from Office to Libre.

I’ve got a spreadsheet that I grabbed from an excel template that I use pretty much every day, but there’s an array that I guess doesn’t want to work with Calc.

Untitled 1.ods (17.0 KB)

What is supposed to happen is I set the year manually, and it fills in the days of the week off of that.

Opening the original excel file gives me a message saying “Automatic update of external links has been disabled.” and an option to allow updating. I tried with and without that and it didn’t make a difference.

Right now I, sometimes, get #N/A for certain days of the week or ALL the days of the week the first time I opened the thing up.

Also Saturday is called Shabbat in the Calc version but not the Excel version. Not a big deal there, but a weird change all the same.

Anyone have any idea what’s up or how I can go about fixing this? Or should I just track down a similar template that already exists for Calc?

Thanks in advance.

[EarnestAl]

The ods fails in my LO and in MS Office 2019.
There is an extension Perpetual Calendar on the extensions site, otherwise search on calendar there.
You could also search for calendar on OpenOffice templates, https://templates.openoffice.org/

[jasonsgroovemachine]

I grabbed that template and it’s gonna work just fine. Thank you!

[erAck]

That can’t work because in the ndx named expression

1/FREQUENCY(DATE(YearToDisplay;MonthToDisplayNumber;1);calendar)

returns an unsorted array consisting of #DIV/0! errors and one 1 value, but in

MATCH(2;1/FREQUENCY(DATE(YearToDisplay;MonthToDisplayNumber;1);calendar))

MATCH() with the 3rd argument omitted expects a sorted (ordered ascending) array for the range lookup which it isn’t and thus yields #N/A error. It might be that Excel automatically sorts the internal array or does other things ignoring the #DIV/0! errors and hence it works there, but IMHO that’s unspecified behaviour. It might be similar to the LOOKUP(2;1/...) behaviour, that is also unspecified but is implemented in Calc as a workaround. For MATCH() and HLOOKUP() and VLOOKUP() it is not.

If some automatic sorting or error values ignoring behaviour in MATCH() and the [HV]LOOKUP() functions actually is specified somewhere by Microsoft I’d be glad for pointers…