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.