I have no access to any Excel, and don’t know anybody around who has.
For some time now I read now and then about the wonderful “dynamic arrays” supported by some Excel versions. As usual, I can’t make heads or tails of the jumble of pages claiming to help with Excel.
Therefore my question here:
How will Excel’s dynamic array formulas avoid both, spilling and overwriting previous content, if the size of the output goes up too much?
See also:
disaskLuppQuestionDynamicArray_Excel.ods (18.8 KB)
How would a “dynamic array” in Excel handle this situation?
Possibly like the dynamic database ranges in Calc, by insertion and deletion of cells.
Demo for anyone who doesn’t know about dynamic database ranges:
- Data source window (Ctrl+Shift+F4)
- From the left pane of that window, drag the icon of table “biblio” in database “Bibliography” to cell A1.
- Apply some visible formatting to the selected database range, for instance a back color.
- Call Data>Define… select “Import1” and fix the wrong defaults under “Options”:
#tdf163192
4.1. Insert or delete cells = ON
4.2. Keep formatting = ON - Enter
=COUNTA(A2:A21)directly below the “Identifier” column. That is cell A22 with the current demo database as shipped with LibreOffice. - Navigate to the column behind the last (should be AG), enter a “Count” as label in AG1 and drag down the formula
=COUNTA(A2:AF2). Most of the rows have 9 values. - The right pane of the data source window shows the database view on the record set we have imported into Calc. There is a little navigation bar at the bottom with a little + icon which takes you to the “very last record” after the last one where you can enter a new record. You may also scroll down to that row.
- Enter some string into the first “Identifier” column and save it by navigating to another row.
- Click any cell in the import range on the sheet and call Data>Refresh
Result:
- The formatted cell range expands by one row.
- The formula in column A moves down by one row and shows the new count of values.
- The formulas in column AG expand by one row. The last one counts one string.
- Right-click the gray column header of the row you have added in step 8 and choose “Delete Rows”. Confirm the deletion.
- Click a sheet cell and Data>Refresh again.
Result:
- The formatted cell range shrinks by one row.
- The formula in column A moves up by one row and shows the new count of values.
- The formulas in column AG shrinks by one row.
We can do all the XLOOKUP, FILTER, SORT stuff (and a lot more) in plain, simple SQL and get the dynamic results in spreadsheets.
Thanks to @Villeroy.
Insertion of a PivotTable into an existing sheet would overwrite previous content, but only if a respective prompt is answered “Yes”.
This is the experience I can guesses base on.
And my question worded a bit simpler should have been “Will Excel prompt for allowance?”
I would expect it behaving like a dynamic database range, inserting/deleting cells silently. But what do I know? My last version of Excel was version 8 of 1997.
1 Like
Hello, colleagues!
Details about dynamic arrays in modern versions of Excel are written here.
See “Formula overlap…” from the link above.
Tanks to @sokol92!
The page you linked to and the mentioned section therein I had actually “read” a few days ago, but obviouly not thoroughly enough.
Now I think to have understood.
Summary: The explicit alert is only shown “on input”, and on the casual “alert” by dotted border the user must act interactively.
Did you also find a clear explanation of how to reference the dynamic output range as a whole by formulas elsewhere?
Conclusion so far: Lupp’s BSM is better. It lacks, however, some features hardly implementable based on user code. And, of course, it comes with all the disadvantages of documents needing user code to work.
Have a look at this German guy explaining a new Excel function with new range operators.
The function has a German name KÜRZUNGSBEREICH (“SHORTENEDRANGE”?) KÜRZUNGSBEREICH(A5:A9999;2;0;B5:B9999) returns the cells in B5:B9999 shortened by the range of trailing empty cells in A5:A9999.
The short version is: A5:.A9999 with a point after the colon shortening trailing empty cells. A5.:B9999 shortens leading empty cells. A5.:.A9999 shortens in both directions.
The old evolutionary race. If you try to make it idiot proof, nature will come up with better idiots.
Thanks!
But sorry, I won’t start another time watching youtube.
Yours.
I have a function on a topic close to this one.
' ------------------------------------------------------------------------------
' lang:en
' Assigns an array formula to a cell (as with interactive input).
' The specified formula is entered into the cells of the range, the size of which corresponds to
' the size of the array returned by the formula.
' Parameters:
' oRange cell that specifies the upper left corner of the target range.
' arrayFormula array formula. May optionally contain {} and an equal sign.
'
' The function returns the resulting range of cells or Nothing.
Function ArrayFormulaSet(ByVal oRange As Object, ByVal arrayFormula As String) As Object
Dim oDoc as Object, oController As Object, oCur as Object, oDisp as Object
Dim props(0) as new com.sun.star.beans.PropertyValue
Dim viewData_o, arr, oCell As Object
oDoc=oRange.Spreadsheet.DrawPage.Forms.Parent
oController=oDoc.CurrentController
viewData_o=oController.ViewData
oCell=oRange.getCellByPosition(0,0)
oDisp=createUnoService("com.sun.star.frame.DispatchHelper")
props(0).Name = "ToPoint"
arr=Split(oCell.AbsoluteName, ".")
props(0).Value = arr(Ubound(arr))
oDisp.executeDispatch oController.Frame, ".uno:GoToCell", "", 0, props
props(0).Name = "Formula"
props(0).Value = arrayFormula
oDisp.executeDispatch oController.Frame, ".uno:InsertMatrix", "", 0, props
oCur=oCell.SpreadSheet.CreateCursorByRange(oCell)
oCur.CollapseToCurrentArray()
ArrayFormulaSet=oCur
On Error Resume Next
oController.Select selection_old
End Function
Sub TestArrayFormulaSet()
Dim oDoc as Object, oRange As Object, s As String, formula As String
s="Hello World!"
formula="Mid(""" & s & """; Row(A1:A" & Len(s) & ");1)"
oDoc = StarDesktop.LoadComponentFromUrl("private:factory/scalc", "_blank", 0, Array())
oRange=ArrayFormulaSet(oDoc.Sheets(0).GetCellByPosition(0,0), formula)
Msgbox "Array Formula Range: " & oRange.AbsoluteName
End Sub
That’s why I tried to explain it in a nutshell. We will struggle with this next level of incompatibility from next year on.
I’m 80 and won’t struggle many more years.
Actually this is another time that I have a solution and am looking for a problem in which many users may be interested, and with which I can demonstrate clearly the value of what I created under many aspects.
In short what I have:
- A concept how to avoid overflow and underflow for the results of array formulas in Calc by making them accessible via single cells or simply via given names (what also can be a bridge to imperative programming).
- A little package of routines (around 100 lines of LibO Basic) implementing that concept.
- A template containing that package and a few additional routines which may be useful in cooperation with that package.
- A few examples, some realistic, showing how the package can be applied.
But:
The concept came to my mind when I was interested in the usage of Calc’s matrix functions for calculations with complex-valued matrices. And that is very mathematical and “abstract”. Nothing that would blow the minds of many everyday users.
I only found one US engineer who gave a talk about a similar approach in 2005.
What I lack:
- The mind-blowing use-cases.
- Somebody being interested in the concept itself, and in discussing and criticizing it. My former colleagues are out of reach.
I will now try to catch your eyes attaching the two mentioned documents.
bsm_RC2_withCache.ots (31.5 KB)
bsm_CplMinverse.WithReducedExampleCached.ods (71.4 KB)
Sorry if I’m just annoying you.
Thanks to everybody!
But I still haven’t found a definite answer to the question in what way the result of a “dynamic array formula” can be referenced as a whole in Excel if it shall be used as an intermediary result in different places for additional calculations.
Who has actually access to a recent version of Excel and can tell me the facts from own experience?
If a reference to the current result has to give its cell range as an address, the whole “dynamic” thing can be ignored, imo.
Excel has the concept of “structured reference”. That is partly available in LibreOffice: CalcTableRef - The Document Foundation Wiki
If you want to reference the resulting area of a function that results a dynamic area (e.g. SORT function), then you reference the top-left cell of that area and append the hash operator. Example: If the cell E3 contains the formula =SORT(mySource[#Data];1), then E3# references the complete output range of the SORT function.
ReferenceDynamicArea.xlsx (11.8 KB)
Thanks a lot!
Unfortunately I still don’t understand the ways Excel developers think.
The main reason for my interest in these things is a “concept” I invented myself about 9 years ago to create variables by formulas in spreadsheets. This is mainly useful for arrays of course, and it was designed to allow the creation of intermediary named arrays by Calc formulas without any output - and to decide later if an output or a reference somewhere else or whatever should be made.
Output without range locking is also supported.
See also the attached example:
disask114154ExampleForRegina.ods (36.2 KB)
You might get an understanding looking at the file markup. A function, that returns an dynamic array has the attributes t="array" and ref="E3:F19", for example.The ref attribute contains the range that the function occupies at time of saving. This information is surely hold internally too, when the file is opened.
If then the output range of the function changes, because one of its parameters changes its value, the needed new output range is newly calculated. If the new output range is larger than the previous one, two things might happen. (A) All additional cells are empty: Excel updates the current used range. (B) At least one additional cell is not empty: Excel produces a #SPILL! error.
LibreOffice has not implemented dynamic arrays, but keeps the output range that was calculated on first creation of an array formula. So when the output range changes because some input changes, some cell values are not included or some cell values get an #NV entry. The requests for dynamic array are in tdf#161642, tdf#161399, tdf#158620 and tdf#127808.
I think, that providing something similar to “dynamic array” by macros is difficult.
This depends on the understanding of “something similar”.
To reproduce the prompt for permission to overwrite neighbouring content is difficult. To produce the kind of borders hinting a spill also is.
But what good for if I can create and use the array without needing a CellRange for it - and without a risk of underflow (#N/A) or overflow (spilling)?
The first working implementation of my concept concerning a way to provide access to a calculated array by reference to a single cell or by using a “name” was written within half an hour, and needed about 50 lines of Basic code. That’s not too dificult, is it?
Of course, the referenced array could later be output anywhere to a then locked range, but my main intention was to be able to use it as an intermediary result for subsequent calculations.
For this purpose the reference to the array as a whole without knowing a range is substantial.
The example I attached to post #14 can explain this to more detail.