As the sigma drop-down context menu functions operate on an implied or defined range of contiguous cells it could easily be deemed logical to include [Product] as an additional choice. Is it a feasible function to calculate the quotient of multiple contiguous numbers?
Created attachment 168873 [details] Screen dump
We could add to the widget all important functions but I don't think it's a good idea -1 from me
Why is Product so important to be added to this list?
(In reply to Heiko Tietze from comment #3) > Why is Product so important to be added to this list? It's probably just as relevant as both Min & Max - it returns a result based upon the calculated value of the whole array. Min & Max just "Cherry Pick" - not really what one could define as "Array Functions". The Subtotal()function provides infinitely better control over these Sigma functions. Paste special caters for "simple" mathematical operations on the selected cells and the analysis of selected cells on the status line can be defined to display, in real-time, the same results as the Sigma functions. What else is missing from all these pre-defined "array" functions?
Let me put my question into another context. What is the whole menu good for? Just typing "=SUM()" is pretty simple and we have a huge number of function that can't be listed anyway. So it's rather a clue for new users how to interact with the functions bar. Not really useful, with or without more functions. As an enhancement idea, how about showing the last used functions in that menu?
(In reply to Heiko Tietze from comment #5) > > As an enhancement idea, how about showing the last used functions in that > menu? +1 from me
(In reply to Colin from comment #6) > (In reply to Heiko Tietze from comment #5) > > > > As an enhancement idea, how about showing the last used functions in that > > menu? > > +1 from me It would make sense if the last used functions were pertinent to that sheet - not just LOCalc
Eike, what do you think?
(In reply to Heiko Tietze from comment #5) > Let me put my question into another context. What is the whole menu good > for? Just typing "=SUM()" is pretty simple and we have a huge number of > function that can't be listed anyway. So it's rather a clue for new users > how to interact with the functions bar. Not really useful, with or without > more functions. You are missing a work flow there. The function is applied to the last cells not containing a formula with the same function, or if the last cell has such function then to all cells containing the function, in a contiguous area. For example, in sequence enter A1: 1 A2: 2 A3: hit sigma Sum => =SUM(A1:A2) => 3 A4: 4 A5: 8 A6: hit sigma Sum => =SUM(A4:A5) => 12 A7: hit sigma Sum => =SUM(A6:A6;A3:A3) => 15 With SUM() it works like subtotals and total. (for which in the last case the formula probably should be =SUM(A6;A3) instead, but that's cosmetic detail). > As an enhancement idea, how about showing the last used functions in that > menu? We already have that in the Name Box, if a formula is started it switches to the most recently used functions list. (In reply to Heiko Tietze from comment #8) > Eike, what do you think? I'm unbiased, if people think having PRODUCT there would be useful then why not. Every entry of sc/uiconfig/scalc/ui/autosum.ui needs handling in sc/source/ui/app/inputwin.cxx ScInputWindow::MenuHdl() but that should be easy enough to make this an EasyHack. Doing so.
(In reply to Eike Rathke from comment #9) > You are missing a work flow there. This is a quite hidden gem. No idea how to make it transparent to understand. > I'm unbiased, if people think having PRODUCT there would be useful then why > not. > > Every entry of sc/uiconfig/scalc/ui/autosum.ui needs handling in > sc/source/ui/app/inputwin.cxx ScInputWindow::MenuHdl() but that should be > easy enough to make this an EasyHack. Doing so. Thanks for the code pointer, adding PRODUCT wont hurt.
(In reply to Eike Rathke from comment #9) > (In reply to Heiko Tietze from comment #5) > > > As an enhancement idea, how about showing the last used functions in that > > menu? > We already have that in the Name Box, if a formula is started it switches to > the most recently used functions list. > That's an interesting feature I'd never noticed - I'm no touch typist so I look at the keyboard then the cell contents and/or bar. Now I'll add the name box to the focal points. Just looked at the Name box functionality and a thought occurs to me; If the user has already started typing a few characters then perhaps the name box could also produce "smart" suggestions in the same manner as the function wizard. What would be awesome for us relative newbies would be the option/ability to then open one of those selections in the function wizard and not fail the syntax test 60% of the time :)) It has been noted the function wizard presents all the partial matches for those who only have "half an idea" of what they're looking for (doing;)). Let me know if it's worth filing an enhancement request along these lines.
(In reply to Heiko Tietze from comment #10) > This is a quite hidden gem. No idea how to make it transparent to understand. https://help.libreoffice.org/latest/en-GB/text/scalc/02/06030000.html?DbPAR=CALC maybe could explain better. > > Every entry of sc/uiconfig/scalc/ui/autosum.ui needs handling in > > sc/source/ui/app/inputwin.cxx ScInputWindow::MenuHdl() but that should be > > easy enough to make this an EasyHack. Doing so. > > Thanks for the code pointer, adding PRODUCT wont hurt. One more to comply with: in a filtered area the SUBTOTAL() function with a function index is inserted instead of SUM(), ... to correctly calculate filtered rows. In sc/source/ui/view/viewfun2.cxx GetSubTotal() needs to be adapted as well. The function index for PRODUCT is 6. While at it, maybe all remaining known SUBTOTAL() functions could be added, on the other hand I'm not sure that's actually needed or wouldn't overcrowd the list. See https://help.libreoffice.org/latest/en-GB/text/scalc/01/04060106.html?DbPAR=CALC#bm_id3165633
More places to adapt.. sc/source/ui/view/viewfun2.cxx the enum ScAutoSum, and lcl_IsAutoSumData() the switch, and one comparison in ScViewFunc::GetAutoSumArea() (best introduce a proper end value in the enum for that instead of using the last of the "real" values.. so that never needs to be adapted again). For reference see the commit that introduced the dropdown: https://git.libreoffice.org/core/+/0ec98930888ee9f29032d12185baefc71da8489f%5E! (changing difficulty of the EasyHack to medium).
And the (now hopefully) correct URL because DumbZilla munges the trailing ! exclamation mark from the URL effectively leading to the parent commit.. https://git.libreoffice.org/core/+/0ec98930888ee9f29032d12185baefc71da8489f%5E%21/
(In reply to Eike Rathke from comment #12) > While at it, maybe all remaining known SUBTOTAL() functions could be added, > on the other hand I'm not sure that's actually needed or wouldn't overcrowd > the list. Eleven items should be manageable.
Dhiraj Holden committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/75705bc48e1ad5cc4a31e810f77578ce462291fa tdf#139602 Added all subtotal functions to autosum It will be available in 7.4.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.