We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

It works in Google Sheets but not in Libre Calc ---

asked 2020-11-30 16:31:48 +0200

TomH2020 gravatar image

updated 2020-11-30 18:16:48 +0200

=arrayformula(vlookup(J3-0.01,sort({A4:A,sumif(A4:A,"<="&A4:A,B4:B)}),2,1))

This works in Google Sheets but not in Libre ... Err:539 is converted to ... {=VLOOKUP(J3-0.01,sort({A4:A1109,SUMIF(A4:A1109,"<="&A4:A1109,B4:B1109)}))}

i am trying to find the Balance BEFORE the Next Deposit.

J3 is the Date of Next Deposit ... Col A is the Date of all Transactions ... Col B is the Value for each transaction ...

This is the one page of the spreadsheet where the error occurs.

C:\fakepath\2020 Credit Tracker 2.ods

And here is the Original Google Sheets page as well

https://docs.google.com/spreadsheets/...

edit retag flag offensive close merge delete

Comments

3

There are standards concerning office software, and google don't like them. Commercial competitors will always need to be incompatible with open standards. How should google force sane users otherwise into their realm?
Good legislature would put a stop to their game.

Lupp gravatar imageLupp ( 2020-11-30 18:13:14 +0200 )edit
1

@TomH2020, It seem to me not a good idea that some values (column B) are references to previous values. If you sort by date, it will create a mess. You can see when a cell contain numbers or references/formulas with Ctrl+F8 (the same that menu View - Values Hightlighting).

LeroyG gravatar imageLeroyG ( 2020-11-30 22:32:42 +0200 )edit

This questions is a sequel of question/273531/.

LeroyG gravatar imageLeroyG ( 2020-12-11 19:07:36 +0200 )edit

1 Answer

Sort by » oldest newest most voted
2

answered 2020-11-30 18:26:46 +0200

ajlittoz gravatar image

updated 2020-11-30 21:00:37 +0200

Err:539 is unsupported inline array content. This should direct you to your SUMIF function usage.

Second argument is Criteria. In your formula, you try to express a "combined" criterion as a concatenation (& operator) between scalar string "<=" and range A4:A1109. This does not make sense.

The second error is: there is no SORT built-in function in Calc.

Finally, conversion may have messed up the argument separators. It depends on the locale, so I can't assert if , is the right separator for you.

EDIT 1

As you seem too lazy to learn the basics of Calc (this is quite easy when you already know other spreadsheet application; just download the Calc Guide and read it), here is the correct formula:

=SUMIF(A4:A1109;"<="&$J$3;B4:B1109)

There are yet other strange function calls in J3, L3 and M3. I let you adapt them as an exercise because I dont know and I have no time to guess what these cells should display.

Moreover the formulas in J3:M3 play nasty tricks with the locale. E.g. date format is hard-coded to US style which upset me when I tried to understand why the result was not what I read in the H-column. My locale being different, I lost a lot of time thinking I didn't use SUMIF correctly. A useful spreadsheet should not create confusion, using two formattings for the same type of data: either date are locale or they are forced to some conventions but should never change across cells.

(EDIT 2 only removed misspellings and typos)

To show the community your question has been answered, click the ✓ next to the correct answer, and "upvote" by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

In case you need clarification, edit your question (not an answer which is reserved for solutions) or comment the relevant answer.

edit flag offensive delete link more

Comments

I can and will acknowledge that there are issues, as pointed out in the above 'answer'. But what is the solution? I did not write the original, it was contributed in Google OnLine help and it worded in Google Sheets. What is the solution in Libre Calc? I am looking for the Balance before the next deposit, just before the deposit. If you could fix the cell (K3) on the spead-sheet or just respond with what should be in the cell, it would be appreciated.

TomH2020 gravatar imageTomH2020 ( 2020-11-30 19:56:57 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-11-30 16:31:48 +0200

Seen: 92 times

Last updated: Nov 30 '20