METHODS AND SYSTEMS FOR BUCKETING VALUES IN SPREADSHEET FUNCTIONS

The disclosed technology creates new families of (predefined formula) spreadsheet functions which allow users to bucket values, supports use of those bucketing functions in other functions, and adds value bucketing capabilities as an option in existing spreadsheet functions. The technology disclosed can use as inputs either cell ranges or Non-Spreadsheet Cell (NSC) data formulas. The capability allows users to bucket numerical, text and time/date values.

RELATED APPLICATIONS

This application is related to and incorporates by reference the following applications:

Contemporaneously filed U.S. Application Ser. No. ______ titled “Methods and Systems for Spreadsheet Function and Flex Copy Paste Control of Formatting and Use of Selection List Panels,” filed 2 May 2023 (Atty. Docket No. ADAP 1013-2), which claims the benefit of U.S. Application No. 63/337,576 filed 2 May 2022 (Atty. Docket No. ADAP 1013-1), and

U.S. application Ser. No. 16/031,759 titled “Methods and Systems for Connecting A Spreadsheet to External Data Sources with Temporal Replication of Cell Blocks,” filed 10 Jul. 2018, now U.S. Pat. No. 11,017,165, issued 25 May 2021 (Atty. Docket No. ADAP 1002-2), which claims the benefit of U.S. Provisional Patent Application No. 62/530,794, filed on Jul. 10, 2017 (Atty. Docket No. ADAP 1002-1), and

U.S. application Ser. No. 16/191,402 titled “Methods and Systems for Connecting A Spreadsheet to External Data Sources with Ordered Formulaic Use of Data Retrieved,” filed 14 Nov. 2018, now U.S. Pat. No. 11,036,929, issued 15 Jun. 2021 (Atty. Docket No. ADAP 1003-2), which claims the benefit of U.S. Provisional Patent Application No. 62/586,719, filed on 15 Nov. 2017 (Atty. Docket No. ADAP 1003-1), and

BACKGROUND

Today's spreadsheets have a very broad range of logical, math, engineering, statistical, etc. built-in functions (predefined formulas) designed to simplify analytics for users. However, none of those existing functions bucket or group data that can then be used by regular cell range or array function formulas, e.g., SUM, COUNT, MIN, and STDEV. The one place in the traditional spreadsheet where bucketing or grouping capabilities exist is in the Pivot Table. Which is not a function and as described by the Microsoft Excel 2019 Bible, published by Wiley, the “A PivotTable is essentially a dynamic summary report generated from a database”. Which has a limited user set because, as the Microsoft Excel 2019 Bible published by Wiley states, “Unfortunately many users avoid this feature because they think it's too complicated”.

Accordingly, an opportunity arises to allow all spreadsheet users to use functions to create bucketed or grouped data calculations using normal cell functional formulas rather than having to learn a specialized PivotTable feature or the specialized functions, e.g., GETPIVOTDATA or CUBE functions, that convert its output for separate cell use. There is an opportunity to dramatically broaden the functions that can be used in these bucketed or grouped calculations and to create much more complicated formulas combining different functions and algebraic operators. There is an opportunity to do bucketed or grouped calculations in regular cell formulas using regular cell functional formulas. There is also an opportunity to broaden the types of grouping done, simplify the grouping setup, and eliminate presentation of unapplicable options. Thus, broadening the capabilities for the PivotTable knowledgeable users and giving the many non-PivotTable users a regular spreadsheet cell functional capability to do bucketed or grouped analyses.

SUMMARY

The disclosed technology creates a family of spreadsheet functions which allows users to create bucketed/grouped data values which can be used by other cell function calculations and in some embodiments be used within other functions to add bucketing/grouping to their calculations. One embodiment of our disclosed technology creates single value bucket/group generating function which can be used to populate a cell or be used within another function to populate one or more cells. Another embodiment of our disclosed technology are functions which populate more than one cell with bucket/group values. Another embodiment of our disclosed technology is the addition of a bucketing/grouping capability to our other spreadsheet functions, allowing users the option of employing buckets/groups.

Each of these embodiments has numerous versions with different capabilities including automatic filling of missing buckets/groups, bucket/group labels that can be altered by users, and data constraining/filtering capabilities. Our embodiments support bucket/group usage with a broad spectrum of spreadsheet functions and functional formulas involving multiple different spreadsheet functions and algebraic operators. Our new functions support usage with a broad range or array functions, e.g., SUM, MIN, and STDEV, as well as broad usage of non-range or non-array functions, e.g., COS, SIN and LOG. Our bucketing/grouping capabilities have automated capabilities such as starting numeric value ranges with round numbers rather than the first value in the data being evaluated. Supporting date buckets in weeks with an auto set or user set week start day. Our technology supports text bucketing such as alphabetical and/or alphanumeric bucketing/grouping. Our technology delivers buckets/groups which can be used to formulaically access the data in the group or bucket for regular function calculations in other cells or within a larger formula.

Particular aspects of the technology disclosed are described in the claims, specification, and drawings.

DETAILED DESCRIPTION

When spreadsheet applications were first created, they electronically emulated tabular paper spreadsheets. More recently, Microsoft Excel, Google Sheets, Apple Numbers, and others have dramatically increased the breadth of capabilities and usefulness of spreadsheets. Spreadsheet applications now access data across a wide variety of sources including relational, structured and semi-structured, open data protocol (OData), Web and Hadoop among others; and these applications manipulate data—such as in PivotTables and via Microsoft PowerPivot. Additionally, spreadsheets have extensive functionality for creating charts with SmartArt and for building forms, and they even have programming languages embedded within them, such as Visual Basic (VBA in Excel), Apps Script (in Google Sheets) and Apple Script (in Numbers).

With their added capabilities, spreadsheet applications have become substantially more complicated. The data manipulation and embedded programming language capabilities can be very powerful but are complicated to learn and therefore they are used by a very small fraction of the spreadsheet application user base. There are other advanced capabilities including Pivot Tables, Power Pivot and Power Query that allow users to manipulate data in spreadsheet overlays and processes from which formulas and cells can be extracted by further capabilities such as Cube Functions (e.g., for PivotTables). These capabilities require users to learn very different interfaces, and operations that operate very separately from their regular cell activities. As such only a fraction of users knows these capabilities which require learning and remembering very different feature operations. All this complexity has led to over a hundred books and thousands of online videos that have been published to help users understand the capabilities of Excel alone.

Spreadsheet providers like Microsoft Excel and Google Sheets cater to the specialized needs of users through many capabilities including vast numbers of spreadsheet functions (e.g., built in predefined formulas including SUM, COUNT and MIN). Microsoft Excel includes more than four hundred and fifty built-in functions and Google Sheets over four hundred. These built-in functions make operations desired by users dramatically simpler and are used by virtually every user.

The formulaically defined Non-Spreadsheet Cell (NSC) data variables and related technologies disclosed in “Methods and Systems for Connecting a Spreadsheet to External Data Sources with Formulaic Specification of Data Retrieval” filed previously, allow users to work with all types of numeric and text external data sets much larger and more complex than can currently fit in traditional spreadsheets. This external data connection creates the foundation for users to automate spreadsheet work without the use of embedded programming languages or special prebuilt data feeds, taking spreadsheets from a tool users employ to conduct one off or routine analytics to a real-time competitor of systems that automate repetitive activities.

The disclosed technology allows users to use one formula using one of our new functions to create bucketed data for data summarization and for data analytics (i.e., going beyond summarizing the data with functional and/or algebraic analytics). The disclosed technology goes beyond existing Pivot Table bucketing/grouping summarization of data used with eleven range or array functions (i.e., Sum, Count, Average, Max, Min, Product, Count numbers, StdDev, StdDevp, Var, and Varp shown inFIG.1AandFIG.1Bfor Microsoft Excel) to use a much broader set of range or array functions and a large fraction of the hundreds of additional non-range or non-array functions available. Our technology allows users to create their own combination of functions and algebraic operators in a single calculation not limited to the set list of operations as shown for Excel inFIG.1C. Our technology is not a specialized feature only usable by itself (e.g., PivotTable), but spreadsheet functions that can be used in any cell and directly or via its cell output in other functional calculations using its formulaic data values (e.g., not losing the data underpinning the bucket/group). Our grouping/bucketing technology is usable in several ways, to populate an individual cell, to populate a value within another function, to write/populate many different groups/buckets in multiple cells, or as a feature within other functions adding the grouping/bucketing capability to those functions. Our technology brings bucketing/grouping capabilities to spreadsheet functions and spreadsheet function analytics, something lacking in today's spreadsheets.

Because Microsoft Excel has the broadest capabilities of the available spreadsheets, we will primarily example user activities with it. Google Sheets and many of the other available spreadsheets have subsets of the Functions and Pivot Table capabilities available within Microsoft Excel and while there are differences, generally operate in similar manner.

We will example a charity worker summarizing donations for three months of data. It is a very simple data set to example how the current PivotTable features work and their limitations.FIG.2throughFIG.5examples that charity user using Microsoft Excel to summarize the donations by size of donation buckets/groups by country, in this example Canada and the US.FIG.6throughFIG.9examples the charity user summarizing the donations by date groups/buckets andFIG.10shows that Microsoft Excel has no alphabetical bucketing within its PivotTable.

InFIG.2the charity user has created a PivotTable255using the data in252setting it up via the controls268. It is doing a daily ‘Sum of donation’234which as shown in the formula bar is designated by what looks like text223matching what is in the PivotTable control279.FIG.3examples the charity user right clicking one of the donation values334to get the right click menu365where the user clicks ‘Group . . . ’375to get the ‘Grouping’ popup474inFIG.4A. The suggested grouping settings start with the lowest value of ‘150’465with a suggested group increment of ‘1000’485and an ‘Ending at: 9875’475which is the largest ‘donation’ not the endpoint of the last suggested group. Because in most situation users want rounded starting points, e.g., 0 rather than 150, the user opts inFIG.4Bto alter the ‘Starting at’ to ‘0’429and then alters the ‘Ending at:’ to ‘10000’439while leaving the ‘By’ at ‘1000’449. When the user clicks ‘OK’459they get the grouped/bucketed PivotTable554inFIG.5. It is worth noting that the cell ‘F3’ which contains the ‘0-1000’544simply shows that content in the formula bar523, like the ‘F3’ ‘150’334inFIG.3just showed ‘150’ in the formula bar323. Because as we will later example the user must do additional work to access the formula creating those values and make those values formulaically represent the data that generated them versus simply a number (i.e., ‘150’) or text (i.e., ‘0-1000’).

FIG.6examples the same charity user creating date-based groupings of the ‘Sum of donation’ using the same data652and the same controls668to generate the PivotTable655. They then click into cell ‘F3’ which shows the first date ‘2/7/22’644and in the formula bar shows the exact same content ‘2/7/22’623rather than the formula that put the value there. We skip showing the right click menu with the grouping selection (as shown inFIG.3) and go straight toFIG.7showing the ‘Grouping’ popup776. Like in the numerical popup the ‘Starting at: 2/7/2022’745is automatically populated with the first date ‘2/7/22’ and the ‘Ending at: 4/5/2022’755is automatically populated with the last date ‘4/5/2022’ in the data. The difference here is rather than a single suggested increment (‘By 1000’485inFIG.4A) the popup776lists seven options. Those options are clearly generic to a data type because as is shown in the ‘date’ data752‘Seconds’, ‘Minutes’ and Hours'765are not applicable to the data. Clicking ‘Days’774does nothing to change the output and therefore also does not feel like it, as configured here, is a helpful grouping suggestion in this situation. Likewise, ‘Years’ is also not very helpful as the data all resides within one year and so it is simply a ‘Grand Total’ line. The grouping option list has not been tested against relevant applicability of generating more than one group or changing the output by creating at least one group (bucket). When the user clicks ‘Months’775and then clicks ‘OK’788they get the PivotTable845inFIG.8. Here again the ‘Feb’ in ‘F3’844shows the same ‘Feb’ in the formula bar formula823, nota formula for the grouped data.

FIG.9examples what happens if the user instead of selecting ‘Months’ in the Grouping popup776inFIG.7selects ‘Hours’ in765. They get a single line with ‘12 AM’944, which as previously described is not helpful as there were no hours in the data.

FIG.10shows that Microsoft Excel PivotTable cannot group/bucket alphabetical content. In this example the charity user had donation data by donor first and last name. They would like to group the data by the first letter of the donors' last name. To do so they create a PivotTable and then try to group the ‘Row Labels’1064by right clicking into cell ‘F4’1044and then clicking ‘Group’ in the menu as shown inFIG.3375, however instead of getting a ‘Grouping’ popup the user gets the ‘Alert’1047telling the user ‘Cannot group that selection’.

FIG.11shows that the Google Sheets PivotTable capability cannot group/bucket alphabetical content. When the charity user replicates the same PivotTable asFIG.10and tries to group the last names (1144through1147) the right click option1145contains no ‘Group’ option. Stopping the user from even attempting to do it. Otherwise, Google Sheets PivotTable setup works in a relatively similar manner (to the Microsoft Excel PivotTable) employing a different UI specifying the same inputs.

FIG.12throughFIG.15examples the grouping setup parallelingFIG.2throughFIG.5in Microsoft Excel. InFIG.12the charity user has created a PivotTable1255using the data in1263setting it up via the controls1268. Like in the Microsoft Excel example it is doing a daily ‘Sum of donation’. The result of ‘150’ shown in cell ‘F3’1234, like Microsoft Excel, is displayed in the formula bar as the value ‘150’1223not a formula of how that data is there.FIG.13examples the charity user right clicking one of the donation values1334to get the right click menu1346where the user clicks ‘Create pivot group rule’1356to get the ‘Grouping’ popup inFIG.14A. The decisions are the same as in Microsoft Excel but there are no suggested grouping settings (the1442,1444and1453are not situational specific suggestions but just generic numeric examples). InFIG.14Bthe charity user sets the ‘Minimum value’ to ‘0’1446, sets the ‘Maximum value’ at ‘10000’1448and sets the ‘Interval size’ to ‘1000’1457. When the user clicks ‘OK’1479they get the grouped/bucketed vales1568in the PivotTable1545inFIG.15. Like in Microsoft Excel, the Google Sheets formula bar does not show a formula for the grouped data but simply the text ‘0-1000’1523like what is in the corresponding cell ‘F3’1534.

FIG.16examples the same charity user creating date-based groupings of the sum of donations using the same data and the similar controls to generate the PivotTable as exampled for Microsoft Excel. When the user right clicks in cell ‘F3’1624to get the menu1635it now has a selector arrow (not there for numerical values) which the user clicks1645to open a date grouping option menu1667with fifteen options—even more options than Microsoft Excel. However, like Microsoft Excel a number of those options are not applicable to the data set, i.e., Second, Minute, Hour, Hour—Minute (24 hour) and Hour—Minute (12 hour)1657, or would give only one outcome, i.e., Year 1687. Numerous of the others result in the same number of groups/buckets with different date or time conventions. None of them fill in the missing groups/buckets. When the charity user clicks the “Month’1677selection they are delivered three different month groups/buckets1735with results matching those in Microsoft Excel (shown inFIG.8845). Also, like Microsoft Excel the group buckets have formula bar formula1722which only the shows a value, in this case ‘Feb’, rather than the formula determining the value. So, to attempt to use the PivotTable feature values formulaically requires employing other spreadsheet functions. The GETPIVOTDATA functional transformation has very limited capabilities but is implemented in more than just Microsoft Excel (e.g., Google Sheets and LibreOffice Calc), while the CUBE functions are a Microsoft Excel specialized capability requiring an advanced capability called PowerPivot which is an add-in for many users and not available to most Mac users. Neither of these capabilities allow a user to employ a PivotTable created value for calculations which have not been done by the PivotTable and therefore cannot support other data summarizations using the PivotTable data. We will now describe how those PivotTable transformation functions work and example their limitations.

PivotTable Cell References

PivotTable cell referenced values can be used in normal cell formulas but only as the value and not representing the associated data (e.g.,1863is the associated data for the ‘150-1150’ bucket1835) underlying the value.FIG.18examples this for a PivotTable grouped set of donation values1845created by the equivalent of clicking ‘OK’ in the ‘Grouping’ popup474inFIG.4A. In column ‘H’ the user attempts many ways to use the groups/buckets in a cell formula but finds that the data is text1847/1855and there is no way to analytically use the associated data underlying the group/bucket in typical cell functional formulas. The cell reference also does not shift if the content of the PivotTable changes and therefore conventional spreadsheets developed the ‘GETPIVOTTABLE’ function which overcomes this shifting limitation.

The GETPIVOTDATA function transformation of the PivotTable calculated values solves the cell shifting problem with changes in the PivotTable but is only applicable to the visible summarized values in the body of the PivotTable and not applicable to the Row or Column labels.FIG.19AandFIG.19Bexample the shifting workings of a ‘GETPIVOTDATA’ function and a typical cell for PivotTable changes. In this example the user alters the ‘Grouping’ ‘Starting at:’ from ‘0’ (shown by the result in cell ‘F3’1931) to ‘3000’1926clicking the ‘OK’1936in the ‘Grouping’ popup1925(changing the first row's label from ‘0-1000’1931inFIG.19Ato ‘<3000’1961inFIG.19B). InFIG.19Athe charity user has setup a normal reference cell formula ‘=H7’ in cell ‘L7’1945and a ‘GETPIVOTDATA’ functional reference in cell ‘L8’1957which refers to the value in cell ‘H8’1953. When the PivotTable changes as shown inFIG.19Bcell ‘L7’1985still refers to the same ‘H7’ cell, but it has a different value (‘16525’1983vs, ‘10500’1943) because the PivotTable values shifted but the cell reference did not. However, the shift does not change the value in the ‘GETPIVOTDATA’ cell formula in ‘L8’1997because while the ‘18950’ value has shifted from cell ‘H8’1953to cell ‘H6’1973the ‘GETPIVOTDATA’ function automatically corrects for the shift.

However, this shifting capability only works if the value is still visible after the PivotTable change.FIG.20AandFIG.20Bexample what happens if the PivotTable change eliminates the value. In this example the charity user again alters the ‘Grouping’ ‘Starting at:’ from ‘0’ (shown by the result in cell ‘F3’2031) to ‘3000’2026clicking the ‘OK’2036in the ‘Grouping’ popup2025(giving the ‘<3000’ row label2061inFIG.20B). InFIG.20Athe charity user has setup a ‘GETPIVOTDATA’ functional reference in cell ‘L8’2057which refers to the value in cell ‘H4’2043. However, that value disappears after the grouping/bucketing change so that the before value of ‘3250’2057is replaced by a ‘#REF!’ error message because the value no longer exists in the PivotTable2083.

FIG.21examples another limitation of the ‘GETPIVOTDATA’ function, which is its lack of incrementing in copy paste. In this example the charity user wants to replicate the ‘Canada’ column of values2153and2173. They do so by creating the ‘GETPIVOTDATA’ formula in cell ‘K3’2156by typing the ‘=’ and then clicking on cell ‘G3’2153to get the formula in the formula bar2147and the value in cell ‘K3’2156. They then copy paste cell ‘K3’2156to cells ‘K4’ to ‘K11’2176expecting to get the values in cells ‘G4’ to ‘G11’2173but instead get eight copies of the value and formula in ‘K3’2156. Not the desired outcome, requiring the user to manually edit the formulas to get the desired incrementing value copy paste.

The limited capabilities of ‘GETPIVOTDATA’ does not allow users formulaic access to the data underlying the PivotTable and any grouping/bucketing it has performed is the extent of the capabilities for the other traditional spreadsheets. Microsoft Excel went one step farther solving the disappearing value problem (with CUBE functions discussed next) but not solving the normal cell regular function use of the PivotTable accessed data and not expanding the very limited functional calculations supported, i.e., the limitation to the eleven data summarization functions inFIG.1AandFIG.1Band no support of the much broader set of spreadsheet analytical functions.

Microsoft Excel CUBE Functions

Microsoft Excel's CUBE functions solves the disappearing value problem but does not give the user free access to use the PivotTable data in regular cell function (meaning non-CUBE function) calculations and those CUBE function calculations are limited to what the PivotTable they are accessing has previously done. So, users are limited to the eleven range/array functions previously previously (inFIG.1AandFIG.1B) sited and no use of the hundreds of other functions. No ability exists to use the grouped or bucketed data in a non-PivotTable previously done calculations. And the user must have the Power Pivot capability (an add-in for many Microsoft Excel versions and not available in all versions) and separately setup the PivotTable using Power Pivot, as a regular Pivot table setup does not generate the CUBE functions conversion.

FIG.22AandFIG.22Bexamples the necessary step of adding the data2262to the Power Pivot2219data model ‘Add to Data Model’2223completed by clicking the ‘OK’2256in the Table popup2247. This creates a ‘Power Pivot’2313table2344shown inFIG.23from which the charity user clicks the ‘PivotTable’ button2323to start the PivotTable creation process as shown inFIG.24. In this example the charity user opts to create the PivotTable in a ‘New Worksheet’2444by clicking ‘OK’2476in the ‘Create PivotTable’ popup2464. InFIG.25they then do what looks like a normal PivotTable setup2586except it is from a Power Pivot enabled table ‘Table3’2556. The result is a PivotTable2573that is like one previously exampled inFIG.6except this one can do CUBE conversions and that one cannot. The grouping/bucketing done inFIG.26AandFIG.26Bworks the same way with the right click opening a popup where the user clicks ‘Group’2656to get the ‘Grouping’ popup2658inFIG.26B. The screens look slightly different only because the user had to move from their Mac, which lacks the ability to do Power Pivot, to their PC which after adding in Power Pivot could do CUBE function conversions. This has not changed the fact that Microsoft Excel offers grouping options which are not applicable to this data or will give a single group (otherwise known as a Total). The user selects ‘Month’2668and clicks ‘OK’2678to generate the grouped/bucketed PivotTable2723inFIG.27A.

The user can then start the process to convert the Power Pivot PivotTable to CUBE functions. InFIG.27Bthe charity user examples highlighting the PivotTable2783with the ‘PivotTable Analyze’ ribbon2748and clicking the ‘Calculations’ button2756. That opens a popup where the user clicks ‘OLAP Tools’2777and to open another popup where the user clicks ‘Convert to Formulas’2788selection to convert the PivotTable2783to CUBE function formulas shown inFIG.28AthroughFIG.29A.

FIG.28AandFIG.28Bexample that the Row and Column labels are converted into ‘CUBEMEMBER’ function formulas (e.g., value2822with its formula2817inFIG.28Aand value2863with its formula2857inFIG.28B) while the calculated values are converted in ‘CUBEVALUE’ function formulas (e.g., value2883with its formula2877inFIG.28C).FIG.29Ashows the range/array function operation ‘SUM of donation’ done by the PivotTable2932has also been converted into a ‘CUBEMEMBER’ functional formula2917. The user is now able to separately move each cell wherever they want without disrupting its value. The PivotTable is gone so changes to it do not disrupt these values. However, the user cannot change the values to something that has not been evaluated by a PivotTable working from this Power Pivot table.FIG.29Bexamples this by the charity user changing the ‘Sum of donation’ inFIG.29A2918to ‘Count of donation’ inFIG.29B2968which turns the “CUBEVALUE’ results from the calculated value2944to ‘#NA’ errors2974and the ‘Sum of donation’2932‘CUBEMEMBER’ value to a ‘#NA’ error2962. This is confirmed by user accessible error popup2971telling the user ‘Value Not Available Error’. However, as exampled inFIG.30throughFIG.32Bhad the PivotTable ‘Table3’ previously done the ‘Count of donation’ evaluation then that exact same formula would work. Thus, demonstrating that the CUBE functions do not support user calculations which have not been done by the PivotTables using the Power Pivot source data. They can display data summaries already done, however do not have a stand-alone functional computing capability.

FIG.30examples the charity user having set up a ‘Count of donation’ PivotTable3043using the same ‘Table3’3056Power Pivot table using the typical setup3086. InFIG.31they then convert the PivotTable to CUBE functions highlighting the PivotTable3153clicking the ‘Calculations’ button3126, then in the popup they open (clicking) the ‘OLAP Tools’3187selection and then in the popup it opens clicking the ‘Convert to Formulas’3158selection. This then converts the PivotTable to CUBE function formulas shown inFIG.32A. For comparison the previous non-working ‘Count of donation’FIG.29Bis repeated inFIG.32B. The ‘CUBEMEMBER’ formulas3217and3267are identical, but the one created from a ‘Table3’ that has now done the calculations works while the other did not (e.g.,3232vs.3262and3244vs.3274).

FIG.33AthroughFIG.33Cexample that CUBE functions also lack the ability to do an incremental copy paste, but instead do exact replica copy paste.FIG.33Aexamples what the charity user would like to see when they do a copy paste for both the values3322and the formulas3327(seen via the FORMULATEXT function). However, the user has the situation inFIG.33Bmissing the ‘Mar’ and ‘Apr’ grouped/bucketed rows3332. They copy the ‘Feb’ row3352hoping to get the other two months in the target rows3361. Instead, the result is an exact copy of the ‘Feb’ row values3391and formulas3397, not the incremented values displaying ‘Mar’ and ‘Apr’ values3332and formulas3327. So while CUBE functions allow users the ability to both move the cells around and retain values previously calculated by the Power Pivot PivotTables using the Data table, they do not support groups/buckets not already done by the PivotTables, they do not support independent calculations, they do not support a broader set of range/array functions, they do not support non-range/array function calculations, they do not support incremental copy paste and they do not support regular cell functional calculations using their underlying data. We are now going to describe how our technology removes all those limitations and adds capabilities beyond those. Our technology also removes the requirement for user to know how to set up data in PowerPivot, do PowerPivot PivotTables, and do Cube function conversions. Each one a substantial barrier to usage given how different they are from creating a functional formula in a regular spreadsheet cell.

Our disclosed technology creates a family of (predefined formula) spreadsheet functions which allows users to create bucketed/grouped data values which can be used by other cell function calculations and in some embodiments be used within other functions to create bucketed/grouped values. There are three major family branches of the bucketing/grouping technology, one which is single value function, the second is a multi-value generating function and the third is adding this capability as an option to many of our other spreadsheet functions. We will example each of these families of capabilities and how they work with the broad spectrum of our spreadsheet functions.

Single Value Bucketing/Grouping Functions

We will begin exampling two different approaches to creating a bucketing/grouping single value, one where the function specifies the increment and another where the user specifies the grouping/bucketing increment. The idea behind the function specified increment is to present to the user a set of the commonly used increments for super simple setup and the option for the user to opt for a specified increment for custom situations.

FIG.34AandFIG.34Bexample the numeric grouping/bucketing capability, previously exampled in the Microsoft Excel and Google Sheets PivotTables, in a regular spreadsheet cell function usable with its associated data for regular spreadsheet cell calculations. We example embodiments which have our cell and formula bar color outlining (e.g., orange but it could be any color or some other way of visually differentiating the functions) so the user immediately sees they have a bucketing/grouping function. We have also included formula bar button triggered option access (3422and3426) as described contemporaneously in U.S. Application No. 63/337,576, titled “Methods and Systems for Spreadsheet Function and Flex Copy-Paste Control of Formatting and Use of Selection List Panels”. In theFIG.34Aembodiment, the charity user has used a function formula called ‘BUCKET_1000’ with a function specified increment of ‘1000’ for the formulaic data field ‘donation’ as shown in the formula ‘=BUCKET_1000(donation)3423. This simple functional formula automatically executes the steps inFIG.35AthroughFIG.35Eto illustratively example what our function is doing, recognizing it illustrates the concepts realizing our application code can accomplish the same outcome differently.

FIG.35Aexamples the available data which as exampled inFIG.36AthroughFIG.36Ccould be stored elsewhere in the spreadsheet or could be Non-spreadsheet cell (NSC) external data accessed as described in our previous filings. Throughout all our following examples the formulaic data used could be from either source (in-cell or external) or from a combination of the two different sources.FIG.35Bshows that for this formula only the ‘donation’ data values are retrieved.FIG.35Cexamples the sortation done to set up the creation of the groups/buckets inFIG.35Fafter the automatic determination of the starting point and ending point. In this embodiment our technology determines the closest number equal to or below the first data point that when divided by the bucket increment delivers an integer value. The lowest value ‘150’3526does not equal an integer when divided by the bucket increment of 1000 and as shown inFIG.35D. The first value below ‘150’ that fulfils that requirement is ‘0’ which becomes the bucket starting point in this example. The auto determined ending point in this embodiment is the last increment that captures the highest value, in this example ‘9000-9999.99 . . . ’ which captures the highest value of ‘9875’3586with the value ‘9999.99 . . . ’3587shown inFIG.35F. In other embodiments the user can override the automatically set starting and ending points as later exampled.

We have set our bucket/group values, so it is obvious to the user what values are in which group/bucket not groups like other spreadsheets where ‘1000-2000’ and ‘2000-3000’ does not leave the user certain where the 2000 values went. Clearly other group labelling modes can be used but ours are unambiguous. Finally, the last step inFIG.35Gdelivers the desired bucket, in this situation the first one, to the cell ‘A4’3442holding the formula3423inFIG.34A.

Our technology supports the use of each bucket value with its supporting data. The supporting data is all the data related to the bucket as exampled for the first bucket ‘0-999.99 . . . ’3527inFIG.35Fhaving the associated data3563inFIG.35A. As we will example later that associated data is usable in functional formulas referencing a cell populated with that bucket or in functional formulas containing the bucket function creating the bucket value. It includes, as exampled in3563inFIG.35A, any column of the data not just the column used to create the bucket.

Our technology also supports the user specifying the bucket starting and ending points.FIG.162AthroughFIG.162Dexample the charity user setting the bucket starting and ending points.FIG.162Aexamples the charity user creating the formula16223in cell ‘A4’16242and in this embodiment all the automatically done settings of the start point, end point, label type and filling checked on as shown inFIG.162B. The user decides they would like to change the starting and ending points of the buckets/groups so in this embodiment they click the ‘CHANGE OPTIONS’ button16222and to get the ‘BUCKET SETTINGS’ popup shown inFIG.162B. The user then clicks into the ‘Start’ setting box16283which holds the automatically set value ‘0’. The user then changes that to ‘100’16287inFIG.162Cwhich automatically triggers a ‘100’ value increase in the ‘End’ value to ‘10099.99’16288. In this example the user is fine with the new ‘End’ value, but had they wanted another value they could have changed it in16288. The charity user is also fine with the other settings, so they click ‘Save’16298to get the change in the bucket/group value ‘100-1099.99 . . . ’ in cell ‘A4’16246inFIG.162D. Which has changed from the value ‘0-999.99 . . . ’ in cell ‘A4’16242inFIG.162A. Because in this embodiment the bucket/group settings are in invisible arguments (see contemporaneously U.S. Application No. 63/337,576, titled “Methods and Systems for Spreadsheet Function and Flex Copy-Paste Control of Formatting and Use of Selection List Panels”) the formulas16223inFIG.162A and16227inFIG.162Dare unchanged despite the user specified change. As later exampled our technology also supports bucket/group setting visual arguments.

InFIG.34Bthe user specifies the bucketing/grouping increment value of ‘2000’3428in the ‘BUCKET_X’ functional formula3427. This functional formula automatically executes the illustrative steps inFIG.37AthroughFIG.37F.FIG.37Aexamples the available data which as previously discussed could be stored in the spreadsheet or could be non-spreadsheet cell (NSC) external data.FIG.37Bshows that for this formula only the ‘donation’ data values are retrieved.FIG.37Cexamples the sortation done to set up the creation of the groups/buckets inFIG.37E. The lowest value ‘150’3727does not equal an integer when divided by the bucket increment of2000and as shown inFIG.37D. The first value below ‘150’ that fulfils that requirement is ‘0’ which becomes the bucket starting point in this example. The auto determined ending point in this embodiment is the last increment that captures the highest value, in this example ‘8000-9999.99 . . . ’ which captures the highest value of ‘9875’3786.FIG.37Fdelivers the desired bucket, in this situation the first one, to the cell ‘A4’3446holding the formula3427inFIG.34B.

This method and other embodiments or implementations of the technology disclosed can include one or more of the following features and/or features described in connection with additional methods disclosed. In the interest of conciseness, the combinations of features disclosed in this application are not individually enumerated and are not repeated with each base set of features. The reader will understand how features exampled across the different bucketing/grouping functions and capabilities can readily be utilized by those where the feature example is not repeated.

Single Value Bucketing/Grouping Functions—Incremental Copy Paste

FIG.38AandFIG.38Bexample how our single value bucket/grouping function with no filling supports what we call “incremental copy paste”.FIG.38Astarts with the formula3823and value3842in cell ‘A4’ as exampled inFIG.34Aand automatic steps shown inFIG.35AthroughFIG.35E. The charity user wants to see all the values, so they highlight cell ‘A4’3842and clicks the ‘Copy’ button3812in the ribbon and then highlights inFIG.38Bthe cells ‘A5’ to ‘A14’3876clicking the regular ‘Paste’ button3816in the ribbon. This populates the values in3876having incremented through all the bucketing/grouping values and then added the three remaining cells3886in the paste area with ‘#NODATA!’ error messages.

FIG.39AthroughFIG.39Eillustratively examples the automatically done steps by our technology supporting the copy paste.FIG.39Aexamples the retrieved ‘donation’ values, whileFIG.39Bsorts those values in preparation of the bucketing/grouping process. This step is conducted as previously described starting the first bucket at ‘0’ and using the function specified increment of ‘1000’ in ‘BUCKET_1000’ to give the ‘0-999.99 . . . ’ first bucket and then all the subsequent buckets with values until a last bucket/group of ‘9000-9999.99 . . . ’ fulfils the largest ‘donation’ value. Since the paste area includes three additional cells,FIG.39Dexamples the addition of the three ‘#NODATA!’ error messages.FIG.39Ethen formats the results for return to the cells ‘A5’ to ‘A14’3886inFIG.38B. Each of these non-erroneous values has a unique cell formula as exampled for cell ‘A5’3856having the formula3827:

Wherein this embodiment the ‘{BT_2}’ shows that it is Bucket Term 2 with the number incrementing with additional values. In this embodiment the ‘donation’ in formula3823:

is the equivalent short version of donation{!BT_1}. Other argument syntaxes could be used to differentiate the bucket values in the formulas.

Single Value Bucketing/Grouping Functions—Flex Copy Paste

Our bucketing/grouping function technology supports more sophisticated versions of copy paste such as our ‘Flex’ copy paste exampled inFIG.40AandFIG.40Band disclosed in U.S. application Ser. No. 16/191,402.FIG.40Aexamples the charity user copying the cell ‘A4’4042then highlighting the paste area4062. As you will see next the size of the paste area can be anything and only the direction matters because our Flex technology will determine the number of cells to be pasted. The user then clicks the ‘Paste’ button dropdown arrow4016as shown inFIG.40Bto get a dropdown list. The user clicks the ‘Flex’ option4037and our technology pastes the complete set of bucketing/grouping values4076. This is automatically executed by executing the steps inFIG.39AthroughFIG.39Cinstead ofFIG.39DandFIG.39EexecutingFIG.39F. This fills only the cells with values ‘A5’ to ‘A11’4056inFIG.40Bautomatically stopping with the last value. In this embodiment these cells are then outlined in orange which identifies them as ‘BUCKET’ function cells instead of the usual blue identifying a flex copy paste area. However, they could have been blue or not color differentiated (as this is helpful identifier for users but not a necessary capability) for the flex copy paste to work.

Our technology has additional capabilities which can be optional specifications or default capabilities. A valuable capability for our charity user is automatically filling empty buckets which allows the user to put different variants of their analyses side-by-side ensuring all the groups line up despite different analyses having different missing groups.FIG.41AthroughFIG.45Bexamples one embodiment of our group/bucket filling capability as an optional specification which can be turned ‘ON’ or ‘OFF’, and how it works for incremental copy paste and FLEX copy paste. In other embodiments filling can be an automatically executed capability within bucketing/grouping.

FIG.41Aexamples a user of our specified interval bucketing/grouping function opting to turn on the filling capability. They are starting with the function formula4123and value4142with the no filling setting exampled inFIG.38AthroughFIG.40B. However, in this example the charity user clicks the ‘CHANGE OPTIONS’ button4122which opens a hint4137(per U.S. Provisional Patent Application No. 63/192,475. ADAP 1009-1 and contemporaneously per U.S. Application No. 63/337,576, titled “Methods and Systems for Spreadsheet Function and Flex Copy-Paste Control of Formatting and Use of Selection List Panels”) displaying the ‘BUCKET_1000’ ‘OPTIONS’. The user clicks on the ‘FILLING’ option4136that in this embodiment opens a selector dropdown4139displaying the current selection of ‘OFF’4149. This selector could be done many different ways but for this ‘ON’ and ‘OFF’ selection this is a simple and easy method. The user then click on the ‘OFF’ status4139opening the selection dropdown where they see the ‘OFF’ option4149currently selected.FIG.42Aexamples the user clicking the ‘ON’ selection4245in the selector dropdown4235for ‘FILLING’4232to get the result shown inFIG.42B. Nothing looks different about the outcome in the value4246or the formula4227versus the starting point inFIG.41Avalue4142and formula4123. This is because the filling arguments are not displayed in the formula in this embodiment (although will be in other embodiments) and the filling does not change the first bucket shown value. However, the difference becomes readily apparent when the cell is copied and pasted as shown inFIG.43AthroughFIG.45B.

The charity user is going to repeat the copy paste process they did inFIG.38AandFIG.38but with different outcomes. The user highlights cell ‘A4’4342and clicks the ‘Copy’ button4312in the ribbon then highlights inFIG.43Bthe cells ‘A5’ to ‘A14’4376clicking the regular ‘Paste’ button4316in the ribbon. This populates the values in4376having incremented through all the bucketing/grouping values and then filled the one remaining cell4396(not three as inFIG.38B3886) with a ‘#NODATA!’4396error message.

FIG.44AthroughFIG.44Eillustratively examples the automatically done steps by our technology supporting the (incremental) copy paste.FIG.44Aexamples the retrieved ‘donation’ values, whileFIG.44Bsorts those values in preparation of the bucketing/grouping process. This step is conducted as previously described starting the first bucket at ‘0’ and using the function specified increment of ‘1000’ in ‘BUCKET_1000’ to give the ‘0-999.99 . . . ’ first bucket and then all the subsequent buckets with values until a last bucket/group of ‘9000-9999.99 . . . ’ fulfils the largest ‘donation’ value. However, the difference occurs in what is done inFIG.44Dwhere any missing buckets/groups, e.g., ‘4000-4999.99 . . . ’4437and ‘7000-7999.99 . . . ’4447are filled to complete the bucket/group progression (not done inFIG.39D). Then the one additional cell is filled with a ‘#NODATA!’ error message.FIG.44Ethen formats the results for return to the cells ‘A5’ to ‘A14’4376inFIG.43B. Like previously, each of these non-erroneous values has a unique cell formula as exampled for cell ‘A5’4356having the formula4327:

Again, the ‘{BT_2}’ shows that it is Bucket Term 2 with the number incrementing with additional values. However, with the ‘FILLING’ optional specification setting of ‘ON’ there are more bucket/group values.

Our filling technology also works for ‘Flex’ copy paste as exampled inFIG.45AandFIG.45B. However, instead of getting seven pasted values as in4076FIG.40B, the charity user gets nine pasted values4576FIG.45Bfrom what otherwise is an identical copy paste process by the user. They select cell ‘A4’4542, click copy4512, select a paste area4562, click the paste dropdown triangle4516, select ‘Flex’4537in the selections list4527to get the nine paste cells populated4576. The automatically done illustrative steps by our application are shown inFIG.44AthroughFIG.44DandFIG.44F. Thus, giving users easy ways to display the full set of buckets/groups.

Single Value Bucketing/Grouping Functions—Label Options

Our technology has additional capabilities supporting flexible usage by users.FIG.46AthroughFIG.47Cexample group/bucket labelling capabilities supported by our technology. We give users options to alter the labelling to meet their needs. Those options include alternative pre-specified options or a custom ability to create their own labels. In this embodiment the user initiates the change via the ‘CHANGE OPTIONS’ button4622although it could be initiated through the function argument hints, buttons, menus, or other methods. In the Options hint4633the user sees the pre-specified specification they want and clicks it4643opening the selector4664where the user clicks ‘ON’4654which then will replace the ‘OFF’ triggering the change in the label for this cell and all its copy paste related cells4676shown inFIG.46B(versus the previous labels4672). It will also automatically change the ‘ON’4634for the previous label selection to ‘OFF’. Our technology supports having more pre-specified options and different configurations of the selection process.

FIG.47AthroughFIG.47Cexample our technology giving user greater freedom to customize the labels to their choice. The user clicks the ‘custom’ optional specification4744. That opens a UI where the user has many different alternative label configuration capabilities including specifying the word between the two label values of their choice allowing the charity user to specify ‘thru’4786as exampled inFIG.47B.FIG.47Cexamples the outcome of another specification option where the user can go with a single ‘[num]’ numeric value preceded or followed by the word(s) or symbol(s) of their choice. Here the user selected to have the lead number followed by ‘to’4788. Thereby our technology allows users many ways to get a bucketing/grouping label of their choice and while these examples have been for numeric buckets/groups our technology supports similar flexibility in date or text buckets/groups.

Single Value Bucketing/Grouping Functions—Usable in Formulas Via Cell Reference

As described before each of our BUCKET function formulas has a usable formula that allows access to the underlying data for formulas. These formulas look and act like regular function formulas using our versions (which support formulaic data) of the same functions' users are familiar with.FIG.48AthroughFIG.50Gexample how regular cell ‘SUM’ and ‘COUNT’ functional formulas referencing cells containing our bucketed/grouped functional values use their associated data for their calculations. ThenFIG.51AthroughFIG.53Gexample how regular cell SUM or COUNT functional formulas can use our bucket/group functional formulas (and their associated data) via cell reference or by direct formula inclusion to achieve the same outcome.

FIG.48AandFIG.48Bexample that each of the different variants of the single value bucketing/grouping function have formulas that allow other functions to access their formulaic data. In these embodiments those formulas are:‘BUCKET_1000(donation{!BT3})’4824for cell ‘A6’4842inFIG.48A‘BUCKET_X(donation{!BT10}|1000)’4827for cell ‘A13’4886inFIG.48B

These formulas provide access to the bucket/group associated data.

FIG.49Aexamples the charity user using our formulaic data and the COUNT function to determine the number of donations in the first bucket ‘0-999.99 . . . ’ in cell ‘A4’4942. To do so they create in cell ‘B4’4943the formula4924:

That formula accesses the bucketed/grouped data in cell ‘A4’4942to arrive at the count of ‘6’4943as exampled inFIG.50AthroughFIG.50D.FIG.50Aexamples the formulaic data available withFIG.50Billustratively exampling the data supplied by argument ‘donation{A4}’ which gets the bucketed/grouped data from our ‘BUCKET’ function formula in cell ‘A4’. Note, this is the field/column of data from which the buckets are created but had the user specified a different field (e.g., ‘district’ in the associated data) the formula would have worked as shown next.FIG.50Cexamples the ‘COUNT’ function counting the number of numeric values to get ‘6’.FIG.50Dthen formats the result and returns it cell ‘B4’4943inFIG.49A.

FIG.49Bexamples the charity user using our formulaic data and the MAX function to determine the highest (max) number of the district in the first bucket ‘0-999.99 . . . ’ in cell ‘A4’4946. To do so they create in cell ‘C4’4948the formula4927:

That formula accesses the bucketed data in cell ‘A4’4942to arrive at the max of ‘54’4948as exampled inFIG.50AandFIG.50EthroughFIG.50G.FIG.50Aexamples the formulaic data available withFIG.50Eillustratively exampling the data supplied by argument ‘district{A4}’ which gets the bucketed/grouped data from our ‘BUCKET’ function formula in cell ‘A4’ for the field district. Note this is not the field that the data was bucketed on but associated data for that first bucket.FIG.50Fexamples the ‘MAX’ function determining the max value to get ‘54’.FIG.50Gthen formats the result and returns it cell ‘C4’4948inFIG.49B. The user has been able to write a normal looking MAX formula using a formulaic data argument to employ the bucketed/grouped values for the ‘0-999.99 . . . ’ bucket in our technology.

Single Value bucketing/Grouping Functions—Usable in Formulas Directly

FIG.51AandFIG.51Bexample how the charity user can use our bucketing/grouping technology directly in a formula to get the same result as referencing it in a cell.FIG.51Ashows the ‘COUNT’ and ‘SUM’ bucketed calculation results using the cell referencing approach previously described with the cell referencing ‘SUM’ formula5124giving the result ‘$19,745.00’5184in cell ‘C13’.FIG.51Bexamples our technology producing the same result of ‘$19,745.00’5188in cell ‘C13’ however with a formula referencing no other cell. Instead, the ‘BUCKET’ function with the desired value is directly inputted into the formula5127:

FIG.52AthroughFIG.54Fexample the automatically done calculations for those SUM and COUNT calculations done with and without cell references. No surprise there is little difference in them, and they yield the same answers.FIG.52AthroughFIG.52Dexample using the cell reference approach to do the ‘COUNT’ calculation for the tenth bucket/group and return the value of ‘2’ toFIG.51Acell ‘B13’5183.FIG.52AandFIG.52EthroughFIG.52Gexample using the cell reference approach to do the ‘SUM’ calculation for the tenth bucket/group and return the value of ‘$19,745.00’ toFIG.51Acell ‘C13’5184.FIG.53AthroughFIG.54Cexample using the direct in cell use of the ‘BUCKET’ function to do the ‘COUNT’ calculation for the tenth bucket/group and return the same value of ‘2’ toFIG.51Bcell ‘B13’5187. The big difference relative to the cell reference version is where the “BUCKET” stepsFIG.53AthroughFIG.53Eare done. In this situation they are done in this cell and therefore included in the steps done by the formula and in the cell reference version they are done in the cell referenced and therefore not shown in the cell calculation. The result is the same and it is just a situation of where the steps are done.FIG.53AthroughFIG.53EandFIG.54DthroughFIG.54Fexample using the direct in cell use of the ‘BUCKET’ function to do the ‘SUM’ calculation for the tenth bucket/group and return the value of ‘$19,745.00’ toFIG.51Bcell ‘C13’5188. Like in the COUNT example the SUM example is also indicative of where the BUCKET function work is done. As exampled, our technology supports cell reference or in formula use of our single value bucketing/grouping functions.

Single Value Bucketing/Grouping Functions—Usable in Formulas Copy Paste

We previously exampled how our single value bucketing/grouping functions worked for different types of copy paste. We will now example how our technology supports copy paste of formulas referring to cells containing our single value bucketing/grouping functions as well as formulas containing our single value bucketing/grouping function within another function formula.

FIG.55AthroughFIG.56Eexamples our technology supporting regular copy paste of a single bucket function cell formula and functional formulas referencing that cell. In this example the charity user starts with the ‘COUNT’ and ‘SUM’ formulas created like those inFIG.51Awith their referenced cell ‘A4’ holding a single value bucketing/grouping function. They then highlight the three cells ‘A4’ through ‘C4’5543and then clicking ‘Copy’5512followed by highlighting the paste rows of cells ‘A5’ to ‘B13’5572. The user then triggers the normal paste by clicking the ‘Paste’ button5516to fill the values in cells ‘A5’ to ‘C13’5577. This also fills the formula in cell ‘A5’ which is visible in the formula bar5527showing the second bucket value of the ‘BUCKET_1000 function. The user has gotten the full set of buckets/groups and the donation counts and sums for each of the buckets/groups.FIG.55AthroughFIG.56Eillustratively examples the steps to execute that copy paste which starts by generating the bucket values as shown inFIG.53AthroughFIG.53Ewhich are then used inFIG.56Ato retrieve the associated data inFIG.56Bneeded for the calculations. The ‘COUNT’ calculations are then done inFIG.56Cfollowed by the ‘SUM’ calculations inFIG.56Dbefore sending inFIG.56Ethe formatted values to cells ‘B5’ to ‘C13’5577inFIG.55B.

Our technology supports regular paste where the single value bucketing/grouping function is used directly within a functional formula.FIG.180AandFIG.180Bexamples our technology supporting copy paste of functional formulas with direct formula inclusion of our single value bucketing/grouping functions. For this example, the charity user starts with the situation previously discussed inFIG.51B, a ‘COUNT’ and a ‘SUM’ function formula each using an in-formula ‘BUCKET_1000’ function. The user then copies18022the cells ‘B13’ and ‘C13’18084upward into the area18068shown inFIG.180B. They then click the ribbon paste button18016to paste the cell ‘B4’ to ‘C12’18078shown inFIG.180B. The formula18027shown in the formula bar for cell ‘B12’18087examples one of the flex copy pasted formulas including the ‘BUCKET_1000(donation[BT-9})’ function formula within the larger formula.FIG.59AthroughFIG.60Dillustratively examples the steps taken by our app to first calculate the ‘BUCKET’ values and then to use them to calculate the overall values for the copy pasted cell formulas.FIG.59AthroughFIG.59Ecalculates all the different “BUCKET_1000’ ‘donation’ values including doing the filling of the missing buckets as exampled by retrieving the data inFIG.59B, sorting the donation values inFIG.59C, creating the buckets inFIG.59Dand filling the buckets inFIG.59E.FIG.60AthroughFIG.60Dthen uses those bucketed values to retrieve the ‘donation values matching the buckets inFIG.60A, then does the ‘COUNT’ calculations inFIG.60B, does the ‘SUM’ calculations inFIG.60Cbefore formatting the values inFIG.60Dand sending the values to cells ‘B4’ to ‘C12’18068inFIG.180B.

Single Value Bucketing/Grouping Functions—Usable in Formulas Flex Copy Paste

FIG.57AandFIG.57Bexamples our technology supporting flex copy paste of a single bucket function cell formula and functional formulas referencing that cell. In this example the charity user again starts with the ‘COUNT’ and ‘SUM’ formulas like those inFIG.51Awith their referenced cell ‘A4’ holding a single value bucketing/grouping function. They then highlight the three cells ‘A4’ through ‘C4’5743clicking ‘Copy’5712followed by highlighting the paste rows of cells ‘A5’ to ‘A8’5752. As mentioned previously the size of this area does not matter as the Flex paste will determine the size. The user then triggers the Flex paste by clicking the ‘Paste’ type selector triangle5716to get the dropdown with the paste specification options5727. The user then clicks the ‘Flex’ option5737to fill the values in cells ‘A5’ to ‘C13’5777. In this embodiment this also changes the outline to blue indicating this is a flex-controlled area connected to the flex copy pasted BUCKET formula in cells ‘A4; to ‘A13’5776. The user has gotten the full set of donation counts and sums for each of the buckets/groups and the cells occupied with the calculations will flex with the number of buckets/groups in column A.FIG.53AthroughFIG.53EandFIG.55AthroughFIG.56Eillustratively examples the steps to execute that flex copy paste because those steps completely fill the flex space. They begin generating the bucket values as shown inFIG.53AthroughFIG.53Ewhich are then used inFIG.56Ato retrieve the associated data inFIG.56Bneeded for the calculations. The ‘COUNT’ calculations are then done inFIG.56Cfollowed by the ‘SUM’ calculations inFIG.56Dbefore sending inFIG.56Ethe formatted values to cells ‘A5’ to ‘C13’5777inFIG.57B. In this embodiment the blue outlining of the entire flex copy paste area is shown as a helpful indicator to the user that this area is flex copy paste connected. In a different embodiment that outlining could have been the orange color used in many examples for the bucket/group functions, thereby indicating that all the cells are connected to bucket/group functions.

Our technology supports regular and flex copy paste where the single value bucketing/grouping function is used directly within a functional formula.FIG.58AthroughFIG.60Dexamples our technology supporting Flex copy paste of functional formulas with direct formula inclusion of our single value bucketing/grouping functions. For this example, the charity user starts with the situation previously discussed inFIG.51B, a ‘COUNT’ and a ‘SUM’ function formula each using an in-formula ‘BUCKET_1000’ function. The user then copies the cells ‘B13’ and ‘C13’5884upward with the area5883recognizing the size of the area does not matter. They then click the ribbon paste button dropdown arrow5811selecting ‘Flex’5832to trigger the flex paste to the cells ‘B4’ to ‘C12’5878shown inFIG.58B. The formula5827shown in the formula bar for cell ‘B12’5887examples one of the flex copy pasted formulas including the ‘BUCKET_1000(donation[BT-9})’ function formula within the larger formula.FIG.59AthroughFIG.60Dillustratively examples the steps taken by our app to first calculate the ‘BUCKET’ values and then to use them to calculate the overall values for the copy pasted cell formulas.FIG.59AthroughFIG.59Ecalculates all the different “BUCKET_1000’ ‘donation’ values including doing the filling of the missing buckets as exampled by retrieving the data inFIG.59B, sorting the donation values inFIG.59C, creating the buckets inFIG.59Dand filling the buckets inFIG.59E.FIG.60AthroughFIG.60Dthen uses those bucketed values to retrieve the ‘donation values matching the buckets inFIG.60A, then does the ‘COUNT’ calculations inFIG.60B, does the ‘SUM’ calculations inFIG.60Cbefore formatting the values inFIG.60Dand sending the values to cells ‘B4’ to ‘C12’5868inFIG.58B. The user could have used a regular paste in which case the outlined paste space would matter and be filled with as many values as possible and as previously exampled filled with ‘ #NODATA!’ once all the calculated values were exhausted.

FIG.61AthroughFIG.62Iexample our single value bucketing/grouping function technology supporting constraints/filters in generating values and generating values through copy paste.FIG.61Aexamples the charity users looking to analyze donation performance by geographic district buckets within the ‘US’. To do so they created the bucket formula6123:

Where the second argument group ‘country{“US”}’ is the constraint/filter.

FIG.62AthroughFIG.62Fillustratively examples the automatically executed steps by our technology to deliver the value of ‘0-9’6142inFIG.61AThe steps start with the data retrieval inFIG.62B, then the values constrained to only the ‘US’ inFIG.62C(removing Canada data), followed by sorting of the district values inFIG.62D, auto determination of the starting point inFIG.62E, auto determination of the ending point inFIG.62F, creation of the buckets/groups inFIG.62Gand the formatting of the value inFIG.62Hfor return to cell ‘A4’6142inFIG.61A. Note in this example the user has no filling which becomes apparent in the copy paste done inFIG.61B. Here the user clicks the copy button6112and then highlights cells ‘A5’ and ‘A6’6156inFIG.61B. They then click the paste button6116(or could have instead hit control v) to get the two values6156with the formula6127for cell ‘A5’ visible in the formula bar. The automatically executed steps for this copy paste parallel the previous steps inFIG.62AthroughFIG.62Hexcept stepFIG.62His replaced by the step inFIG.62Ireturning the two values to cells ‘A5’ and ‘A6’6156inFIG.61B. Our technology supports additional constraints/filters, e.g., a date range, but the user here only wanted to limit the buckets and the fields that use them through a single constraint/filter of the ‘US’.

Single Value Bucketing/Grouping Functions—Auto Rounded Labels

FIG.61AthroughFIG.62Iexampled an additional capability of our technology, auto rounded numeric labels. More specifically our labels automatically communicate to a user the correct range of values. As previously mentioned, our labels are not confusing like “1000-2000” followed by “2000-3000” which does not clearly identify where value 2000 went. So, we do something that makes it clearer like “1000-1999.99 . . . ’ followed by “2000-2999.99 . . . ’. However, our technology also recognizes when all the values are integers, as inFIG.61AthroughFIG.62G, and therefore automatically does ‘0-9’6142instead of “0-9.99 . . . ” which also lets the user know all the values are integers rather than reals. As described before our technology auto sets the starting and ending point. In this embodiment our technology determines the closest number equal to or below the first data point that when divided by the bucket increment delivers an integer value. The lowest value ‘1’ inFIG.62Dwhen divided by the bucket increment of 10 does not equal an integer (0.1) and as shown inFIG.62E‘ 1/10=0.1’. The first value below ‘1’ that fulfils that requirement is ‘0’ which becomes the bucket starting point in this example. The auto determined ending point in this embodiment is the last increment that captures the highest value, in this example ‘40-49’ which captures the highest value of ‘47’ inFIG.62Dwith the value ‘49’ shown inFIG.62F.

FIG.63AthroughFIG.64Eexample another capability supported by our technology, an intelligent presentation of bucketing/grouping options to users of only relevant options. In this embodiment the definition of relevant is such that it would result in two or more buckets and generates buckets different than the values themselves eliminating all specifications that would result in one or no relevant buckets/groups or buckets/groups no different than the values themselves.FIG.63Aexamples the charity user creating a ‘BUCKET_X’ functional formula where the cursor6343is in the second argument group that is for specifying the bucket increment6352. This exposes the Hint6373which contains intelligently selected options. Specifically, only those optional specifications which will give two or more buckets/groups or buckets/groups that are different than the un-bucketed/ungrouped data (i.e., have at least one bucket/group that groups more than one unique value).FIG.64Cexamples the specifications presented and the next specifications both directions rejected (smaller and larger values). The ‘0.1’ option is rejected because it all the values are integers and it is not an integer increment as well as it would not generate any real buckets, meaning combinations of values. The ‘1’ option is not presented because it does not create any buckets/groups combining unique values. The ‘5’, ‘10’ and ‘25’ are presented because they generate two or more buckets/groups (combining values) while the ‘50’ and any higher option are not presented because they would result in only one bucket/group. When the user clicks ‘10’6363it delivers the result ‘0-9’6346in cell ‘A4’ populating the ‘10’ in the formula6327inFIG.63B. All the steps delivering these changes are illustratively exampled inFIG.64AthroughFIG.64E.

As we will example later this capability works for different the bucketing/grouping of different data types and scales up (larger increments) or down (smaller increments) depending upon the values of data.

FIG.65AthroughFIG.66Eexample bucketing/grouping working for date fields with filling and our intelligent options.FIG.65Aexamples the charity user creating a ‘BUCKET_X’ functional formula for a date field called ‘date’ where the cursor6543is in the second argument group that is for specifying the bucket increment6552. This exposes the Hint6573which contains intelligently selected options. Specifically, only those specifications which will give two or more buckets/groups with at least one real bucket/group.FIG.66Cexamples the specifications presented and rejected realizing that in different situations those options could alter down (e.g., second) or up (e.g., Century) in length of time and options therefore considered and offered. Here the ‘Minute’ or ‘Hour’ options are rejected because they do not exist in the data or because they will generate only one bucket (which they would do in Microsoft Excel as previously exampled). The ‘Day’ option is rejected for presentation to the user because it does not result in any combination of unique values (i.e., does not create any buckets/groups of more than one unique value). The ‘Year’ and anything beyond ‘Year’, in this case ‘Decade’ fail because they will only generate one bucket—which makes it the equivalent of running a total which does not have multiple buckets/groups. Calculation-wise the steps retrieving the dataFIG.66B, bucketing and fillingFIG.66Dand returning the value ‘2/7'22-2/13/22’ inFIG.66Eto cell ‘A4’6546inFIG.65Bparallel steps in the numeric preceding examples. The formula in the formula bar6527gets populated with the ‘Week’ increment with a week starting on Monday and ending on Sunday (an option that can be set by the user for when the week starts, or different options can be presented to the user).

Single Value Bucketing/Grouping Functions—Date Buckets/Groups has all Capabilities

FIG.67AthroughFIG.69Bexamples some previously described capabilities for numeric buckets/groups for dates. Since variants of all the numeric capabilities exampled previously are supported for dates, we will do a more abbreviated set of representative examples.FIG.67Aexamples regular copy paste of a ‘BUCKET_X’ for a date field ‘date’ with ‘Weekly’ bucketing/group increments shown in the formula6724. The charity user highlighted cell ‘A4’6742hit the shortcut ‘Control c’, highlighted the target paste area ‘A5’ to ‘A12’6762and then clicked the ‘Paste’ button6711returning the values fromFIG.66Fto the cells ‘A5’ to ‘A12’6762. Our date bucketing/grouping capabilities also support flex copy paste.

FIG.67Bexamples a ‘COUNT’ formula6727in cell ‘B7’6767employs ‘donation’ values constrained to ‘date{A7}’ where the date is referencing the bucket/group values in cell ‘A7’6766. This is one of the values generated by the copy paste inFIG.67A.FIG.68AthroughFIG.68Eillustratively examples the steps automatically done by our application to calculate the value ‘6’ in cell ‘B7’6767. Just as with the numeric examples the ‘date’ reference to the bucketed/grouped values supplies those values and their related data to the calculation as shown inFIG.68BthroughFIG.68Dand then returns the value ‘6’ as shown inFIG.68E.

FIG.69AandFIG.69Bexample how our family of single value bucket/group functions applied to a date field are directly usable in a formula.FIG.69Aexamples how cell ‘B7’6962generates the value ‘6’ from the formula in the formula bar6924which directly includes the ‘BUCKET_X(date{!BT_4}|Week)’ formula in the ‘COUNT’ formula:

WhereFIG.69Bexamples the same cell ‘B7’6967generating the same value ‘6’ from the formula in the formula bar6924which indirectly includes the ‘BUCKET_X(date{!BT_4}|Week)’ formula from the cell ‘A7’6966in its formula6927:

Both calculations have arrived at the same value using the same formulas although accessed different ways, directly inFIG.69Aand indirectly through cell ‘A7’6966inFIG.69B.

Rather than repeating additional date variant examples that were exampled for numeric buckets/groups (e.g., selecting different label variants or creating a custom one FIG.46A throughFIG.47C), we will move to a capability not seen in existing spreadsheet function Pivot tables and certainly not seen in existing spreadsheet functions—text buckets/groups.

Single Value Bucketing/Grouping Functions—Text Buckets/Groups has All Capabilities

FIG.70AthroughFIG.71Fexamples some of previously described bucketing/grouping capabilities for text (e.g., alphabetical) buckets/groups. There are many different potential text, alphabetical, alphanumeric or character bucketing/grouping increments. These initial examples are bucketed/grouped by the first letter of the specified field values. People relatively frequently bucket people's last names by their first letter and that is what the charity user wants to do with their data. Our technology supports creating standardized or custom buckets such as ‘A to D’, ‘E to H’ and so on, however we will keep the buckets/groups simple for example purposes.

For brevity purposes we have combined multiple user actions into each figure.FIG.70Aexamples the charity user creating the first alphabetical bucket and then copy paste to incrementally replicate that functional formula. The charity user creates a bucketing/grouping functional formula7023in cell ‘A4’7042which contains a ‘BUCKET_X’ function working for the field ‘last’ and bucketing by ‘Letter’ with a constraint/filter of ‘date’{2/1/22’ . . . ‘3/31/22’}’. If you opened ‘Options’ you would find that there is no filling.FIG.71AthroughFIG.71Eillustratively examples the steps automatically done by the formula including the data constraining/filtering inFIG.71C, the sorting and bucketing done inFIG.71Dand the formatting and return inFIG.71Eto cell ‘A4’7042inFIG.70A.

The charity user then hits Control c to start a flex copy paste of cell ‘A4’7042. The user then highlights a paste direction7052clicking the ‘Paste’ dropdown arrow7011and then selecting (clicking) the ‘Flex’ paste specification7031in the paste specifications list7021. This then populates the values in cells ‘A5’ through ‘A14’7082which for brevity are not highlighted the way the app would do it but otherwise accurately example the results. The flex copy paste automatically executed steps by our technology are illustratively exampled inFIG.71AthroughFIG.71DandFIG.71F.

FIG.70Bthen examples the charity user using the value and associated data of the bucket/group function in cell ‘A4’7046(created inFIG.70A7042) in two functional formulas created in cells ‘B4’ and ‘C4’7048. Followed then by a flex copy pastes of those two cells to create the full set of values7088. To do this the charity user first creates the formulas in cells ‘B4’ and ‘C4’7048automatically triggering the illustratively exampled steps7233inFIG.72A,7235inFIG.72B and7238inFIG.72C. They then copy cells ‘B4’ and ‘C4’7048inFIG.70Bfollowed by highlighting a paste direction7058. The user then clicks the ‘Paste’ dropdown arrow7016selecting (clicking) the ‘Flex’ paste option7037in the options list7027. This then populates the values in cells ‘B5’ through ‘C14’7088which for brevity are not highlighted the way the app would do it but otherwise accurately portray the results. The automatically executed steps by our technology for the flex copy paste are illustratively exampled inFIG.72AthroughFIG.72C.

Rather than re-exampling all previous capabilities for different text data type bucketing/grouping (e.g., buckets combining letters and numbers like T1, T2, T3 and C1, C2, C3 for scientific or medical experiments differentiated by Test and Control), we will example the very broad analytical capabilities supported by our technology.

Single Value Bucketing/Grouping Functions—Analytics Support

Another dimension that differentiates our technology from the Pivot table and its related capabilities (GETPIVOTDATA and CUBE functions) is that our technology goes well beyond summarizing data to supporting broad based analytics. Going well beyond the Microsoft Excel 2019 Bible published by Wiley description “A PivotTable is essentially a dynamic summary report generated from a database”. All the eleven functions traditional spreadsheet PivotTables support (FIG.1AandFIG.1B) summarize data for ranges/arrays while our technology supports a much broader spectrum of range/array functions but importantly supports non-range/array functions that do analytics not only summarization. Thus, our technology supports usage of the large number of analytical functions that make up the over four hundred functions supported in a typical spreadsheet and supports very complicated formulas using those functions and algebraic operators. These analytical capabilities apply to any of our different bucket/group types and while the numeric buckets are employed in the next example, it could have easily been done for a date, alphabetical or alphanumeric text one. Our single value bucketing/grouping functions support analytics which otherwise are not supported by any existing spreadsheet functions or features other than users writing code with the programming languages supported by the respective spreadsheets. This is a major advancement. And as described before, our functions work for any supported calculation and are not dependent on any other feature (e.g., Power Pivot PivotTable) having already done that specific calculation.

FIG.73AthroughFIG.75Eexamples our single value bucketing/grouping function with numeric groups/buckets supporting a complex functional and algebraic cell formula. That formula utilizes range/array and non-range/array functions with many algebraic operators referencing a bucketed cell value and using its associated data.FIG.73Aexamples the single value bucket/group used in the complex analytical formula7372inFIG.73B. It is created by a scientist who wants to calculate experimental test results for buckets of experiments. They want to look at buckets of ten experiment numbers recognizing that they have some holes in their data with experiments that have not yet concluded. For that reason, the scientist wants to fill any empty bucket now as they will have results later. They create the ‘BUCKET_X(exp|10) formula7324in cell ‘A5’7351which evaluates experiments ‘0-9’. The steps automatically done by that process, including the bucket options presented to the user, are illustratively exampled inFIG.74AthroughFIG.74Eparalleling steps previously described.

Then inFIG.73Bthe scientist creates the analytical formula7327in cell ‘B5’7356to evaluate the experiments in the ‘exp’ bucket ‘0-9’7355. That analytical formula is a combination of range/array functions (i.e., SUM, COUNT and DEVSQ), non-range/array functions (i.e., SQRT, COS and LOG10), a constant (i.e., 1.3) and algebraic operations (i.e., plus, minus and divide) which goes well beyond summarizing data and into analyzing it. The formula7372in cell ‘B5’7356repeatedly uses the bucket/group values in ‘A5’7355with the automatically done steps illustratively exampling the calculations done inFIG.75AthroughFIG.75E.FIG.75Aevaluates for each experiment (‘exp’) in the ‘0-9’ the first part of the formula:

FIG.75Bevaluates for each experiment (‘exp’) in the ‘0-9’ the second part of the formula:

WhileFIG.75Cevaluates for each experiment (‘exp’) in the ‘0-9’ the third part of the formula:

FIG.75Dthen evaluates the three parts of the formula for the final step inFIG.75Eto return to cell ‘B5’7356inFIG.73B. Thus, the scientist has repetitively utilized a single value bucket/group function in a complicated analytical cell formula. They also could have directly used the ‘BUCKET’ function in the formula if they had replaced each of the four ‘A5’ arguments with ‘BUCKET_X(exp|10)’ thereby delivering the same result.

FIG.76AandFIG.76Bexamples the scientist then copy paste replicating the two cells inFIG.73Bcontaining the bucket/group function formula7324inFIG.73Aand the complex analytical formula7327inFIG.73Busing a regular copy paste (although they could have used a flex copy paste). The scientist user copies the two cells7653created inFIG.73AandFIG.73Bhighlighting the four output rows7662and then clicking ‘Paste’7611. The result are the values in cells ‘A6’ to ‘B9’7667inFIG.76Bwhich are produced automatically by the steps illustratively exampled inFIG.77throughFIG.79C. These steps effectively replicate each of the calculations done inFIG.75AthroughFIG.75Efor each of the four other buckets/groups, recognizing that one of those buckets/groups is currently empty and therefore filled.

The preceding has exampled different embodiments of our single value bucketing/grouping functions used in cells or in functional formulas that result in a single cell value. However, our single value bucketing/grouping functions can be used in multi-cell populating functions to deliver bucketed/grouped results.

Single Value Bucketing/Grouping Functions—Use in Our Multi-Cell Populating Functions

Our single value bucketing/grouping functions can be used to populate multiple cells when used in our Multiple cells populating functions, e.g., our WRITE or WRITE_2D functions which are the subject of our patent applications U.S. application Ser. No. 16/191,402, U.S. application Ser. No. 17/374,901 and U.S. Application No. 63/240,828.FIG.80AthroughFIG.83Dexample two different usages of our single value bucketing/group functions (e.g., ‘BUCKET_10’) within one of our multi-cell populating functions (e.g., ‘WRITE_CALC_V’). Our ‘BUCKET_10’ function transforms a normal formulaic data field into a bucketed/grouped data field within the ‘WRITE’ function. The example is done with a one-dimensional function providing columns of information, but if used in a different variant of WRITE functions it could have been rows of information or in a two-dimension variant rows and columns of information.

FIG.80Aexamples the charity user creating a ‘WRITE’ function formula using our new ‘BUCKET_10’ function. The user wants to ‘COUNT’ and ‘SUM’ the donations, as they have done previously by writing a ‘BUCKET’ function formula, then a ‘COUNT’ function formula in another cell referencing it, then a ‘SUM’ function formula in a third cell referencing the ‘BUCKET’ function formula and finally copying all three cells to produce the full set of buckets. However, rather than separately doing all those steps they would prefer to create one functional formula to do it all as they are doing in cell ‘A4’8042with the formula8024. They have used one of our single value bucketing/grouping functions ‘BUCKET_10(district)’ as the first argument. When they finished the formula and hit ENTER this delivers the buckets/groups of ‘district’8066shown inFIG.80B. The rest of the ‘WRITE_CALC_V’ functional formula8027delivers the additional columns of results8068shown inFIG.80Bwhich were calculated using the bucketed/grouped values.

FIG.81AthroughFIG.81Dexamples the calculations done by the formula8027including the determination of the buckets/groups and their filling inFIG.81Band then the use of those buckets/groups in the ‘COUNT’ and ‘SUM’ calculations inFIG.81C. Those data summarization formulas could have been more complicated analytics formulas each involving more than one function and including non-range/array functions.FIG.81Dexamples the returning of the values to the cells ‘A4’ to ‘C9’8066and8068at which point in this embodiment the populated area is green dash dot outlined and the fx8022icon is replaced with the ‘CHANGE OPTIONS’ green button8026.FIG.82AthroughFIG.83Dexamples the same actions asFIG.80AthroughFIG.82Dexcept without the filling turned on in the ‘BUCKET_10’ function. Therefore, the results8268inFIG.82Bhave four rows instead of the six rows in8068inFIG.80B. Rather than re-exampling all the other single-value bucketing/grouping function capabilities working in multi-value functions, which they do, we will move onto the next major family of bucketing/grouping functions in our technology.

Single Value from Multi-Value Selecting Bucketing/Grouping Functions

The preceding function capabilities populated a single bucket value into a single cell or spreadsheet formula cell, our next embodiment populates multiple selectable bucket/group values from which the user or the application default specifies one bucket/group value for population into the cell with its associated data.FIG.176AthroughFIG.177Fexamples the creation and usage of our new single-value from multi-value selecting bucketing/grouping function technology.FIG.176Aexamples the user having created the formula17633using a new function called in this embodiment ‘BUCKET_DROPDOWN_X’ with the syntax shown inFIG.176D. The first argument group is occupied by the ‘field’17614, the second argument group by the ‘bucket’17614increment and the third optional argument group is for constraints17616. In this example the charity user wants ‘district’17634field values bucketed by increments of ‘10’17635with no constraints as shown inFIG.176Agetting in this embodiment the application default value of the first bucket ‘0-9’17653shown in cell ‘B3’. This value is automatically generated by the function via the steps illustratively exampled inFIG.177AthroughFIG.177F.

The user then decides they would like to select a different bucket, so they click the button17654inFIG.176Ato expose the dropdown list17667inFIG.176B. In this embodiment that opens with ‘0-9’16757highlighted as the current selection, the user then clicks their desired selection of ‘40-49’17677to see the value populated in the cell ‘B3’17687inFIG.176C. This is one embodiment of our technology for populating a bucket value and its associated data into a cell with a multi-bucket selector list allowing easy re-selection of the bucket value. That selected value with its associated data is then usable by other cells in calculations as exampled elsewhere in this filing.

Multi-Value Bucketing/Grouping Functions Populating a Single Cell

We will now example an embodiment that populates multiple bucket values and their associated data into a single cell. default specifies one bucket/group value for population into the cell with its associated data.FIG.178AthroughFIG.179Fexamples the creation and usage of our new multi-value bucketing/grouping in a single cell function technology.FIG.178Aexamples the user having created the formula17824using a new function called in this embodiment ‘BUCKET_DROPDOWN_X’ with the syntax shown inFIG.178D. The first argument group is occupied by the ‘field’17814, the second argument group by the ‘bucket’17814increment and the third optional argument group is for constraints17816. In this example the charity user wants ‘date’17825field values bucketed by increments of ‘Week’17846with one constraint ‘country{“US”}’18732as shown inFIG.178Agetting in this embodiment the application default value of the first bucket ‘!ALL’17843shown in cell ‘B3’. This value is automatically generated by the function via the steps illustratively exampled inFIG.178AthroughFIG.178F.

The user then decides they would like to select a different bucket, so they click the button17844inFIG.176Ato expose the dropdown list17864inFIG.178A. There the user sees that ‘[Select ALL]’17864is on and the user then decides they would like to select four values17877shown in the popup17867inFIG.178B. In this embodiment that populates the value ‘*4*’ in the cell ‘B3’17847which tells the user there are four bucket values and their associated data in that cell. This is one embodiment of our technology for populating multiple bucket values and their associated data into a cell with a multi-bucket selector list allowing easy changing of the selections. Those selected values with their associated data is then usable by other cells in calculations as exampled elsewhere in this filing.

The preceding has exampled different embodiments of what we labelled single value bucket/group functions and multi-bucket/group into a single cell functions. We will now example families of bucketing/grouping functions which deliver multiple values from a single functional formula into multiple different cells. We will break that into two different types of families of functions, the first dedicated functions that create the buckets/groups and the second where the bucketing/grouping capability is an optional specification added to existing or new functions that do more than creating the groups/buckets.

Our grouping/bucketing technology supports a multi-value populating family of functions supporting the different data types, filling/non-filling, external data/n-cell data, user selectable labels, user customizable labels, use by cell functional calculations, used by cell complex analytics functional calculations, regular/flex copy paste of those functional calculations, constraints, and intelligent options. Rather than re-exampling every previously mentioned capability for every data type and situation we will example a subset to give a representative view of our multi-value bucketing/grouping functions.

FIG.84AthroughFIG.85Gexamples a prespecified increment multi-value bucketing/grouping function employing automatic filling and automatic start/end specification. The charity user is redoing a previous analysis of donation counts and sums by donation buckets (with a1000increment).FIG.84Aexamples the charity user creating the ‘WRITE_BUCKET_1000_V’ formula, which could have been named very differently. They have typed the formula8423in cell ‘A4’8442with the cursor8424at the end of the formula. InFIG.84Athe user finishes the formula8428by adding the closing parenthesis ‘)’ and then hitting ‘ENTER/RETURN’ to automatically populate the cells ‘A4’ to ‘A13’8476with ‘donation’ buckets in the increments of 1000 starting at ‘0’. This embodiment also outlines the ‘BUCKET’ cells in orange and employs a ‘CHANGE OPTIONS’ button8426.FIG.85AthroughFIG.85Gillustratively examples the steps automatically executed by the function including the automatic filling of the buckets/groups8538and8558. In this embodiment our technology automatically determines the bucket start value to be the closest number equal to or below the first data point that when divided by the bucket increment delivers an integer value. The lowest value ‘150’ does not equal an integer when divided by the bucket increment of1000and as shown inFIG.85D. The first value below ‘150’ that fulfils that requirement is ‘0’ which becomes the bucket starting point in this example. The auto determined ending point in this embodiment is the last increment that captures the highest value, in this example ‘9000-9999.99 . . . ’ which captures the highest value of ‘9875’ with the value ‘9999.99 . . . ’ shown inFIG.85E. In other embodiments the user can override the automatically set starting and ending points as exampled later. In another embodiment of our technology, we could use a variant of the illustrative algebraic test to create a value that in this example would be ‘0.0 . . . 1-1000’, ‘1000.0 . . . 1-2000’ and so on or display the values as ‘>0-1000’. ‘>1000-2000’ and so on, or some other unambiguous series of buckets.

Our technology also supports the user specifying the bucket starting point.FIG.163AthroughFIG.164Dexamples the user starting with a formula that automatically fills missing buckets and automatically sets the bucket start/end point. That user then changes those start and end points.FIG.163Ashows the charity user starting with the finished formula16323and output16373for cell ‘A4’16342with its automatically generated bucket/group start and end points as well as the automatically filling of missing buckets. However, the charity user has decided they want the buckets/groups to start at ‘150’ rather than ‘0’. Therefore, in this embodiment the user clicks the ‘CHANGE OPTIONS’ button16322to open the ‘OPTIONS’ hint examples inFIG.164A. The user then clicks the ‘BUCKETS’ optional specification16433to open the ‘BUCKET SETTINGS’ UIFIG.164Bwhere the user clicks into the ‘Start:’ box with the value ‘0’16483. They then change that value to the desired ‘150’16487inFIG.164Cwhich in this embodiment automatically increments the ‘End:’ value to ‘10149.99 . . . ’16488. The user is fine with that value, although they could have specified another value, so they click ‘Save’16498to return to the ‘OPTIONS’ hintFIG.164D. They now see that the ‘STATUS’ for ‘BUCKETS’ has changed from ‘DEFAULT’ before (inFIG.164A16433) to ‘ON’16438. You can also see that in this embodiment that ‘DEFAULT’ setting included ‘FILL’ be checked on16475. The user then clicks the ‘Go back’16447to deliver the revised bucket values16376inFIG.164Bstarting with ‘150’ in cell ‘A4’16346.

FIG.86AthroughFIG.88Eexamples a prespecified increment multi-value bucketing/grouping function employing NO filling and showing another type of OPTION HINT usage.FIG.86Aexamples the charity user starting the process in this embodiment to turn off the bucket/group filling. The user is in cell ‘A4’8642which, as previously shown inFIG.84throughFIG.85E, populates the buckets/groups8672with filling. However, once the user clicks the ‘CHANGE OPTIONS’ button8622it opens the ‘OPTIONS’ HINT8637inFIG.86Bwhere the user clicks the ‘FILLING’ option8636which in this embodiment opens the selector dropdown8639showing the current ‘ON’8649setting.FIG.87Athen examples the user clicking the ‘OFF’8745selection after which the user clicks the ‘CLOSE’8798selection inFIG.87Bto get the unfilled bucket/group result8766inFIG.87C. The filled buckets/groups ‘4000-4999.99 . . . ’8762inFIG.87Aand ‘7000-7999.99 . . . ’8782inFIG.87Ahave been removed as shown in the illustratively recalculated steps inFIG.88AthroughFIG.88E. In this embodiment, because the option arguments are invisible, the formula8723and8727has not visibly changed despite the ‘FILLING’ option changing, as per our contemporaneous U.S. Application No. 63/337,576, titled “Methods and Systems for Spreadsheet Function and Flex Copy-Paste Control of Formatting and Use of Selection List Panels”. However, in another embodiment the option arguments would be visible and therefore the user would see the change such as an option argument changing from ‘FILLING[ON]’ to ‘FILLING[OFF]’.

Multi-Value Bucketing/Grouping Functions—Usable in Formulas Via Cell Reference

FIG.89AthroughFIG.90Gexamples formula usage of our multi-value bucketing/grouping function (‘WRITE_BUCKET_1000 V’) generated values and their associated data in COUNT and SUM formulas.FIG.89Aexamples the charity user creating in cell ‘B4’8943a formula8924:

This formula uses the bucket/group values in cell ‘A4’8942as illustratively exampled inFIG.90AthroughFIG.90Dwhere the ‘COUNT’ calculations inFIG.90Cuse the bucket associated ‘donation’ data retrieved inFIG.90B.FIG.89Bexamples the charity user creating in cell ‘C4’8948a formula8927:

This formula uses the bucket/group values in cell ‘A4’8946as illustratively exampled inFIG.90AandFIG.90EthroughFIG.90Gwhere the ‘SUM’ calculations inFIG.90Fuse the bucket associated ‘donation’ data retrieved inFIG.90E. These examples used simple data summarization formulas, but our multi-value bucket/group function technology also supports complex analytical function formulas like those inFIG.73BthroughFIG.75E.

Multi-Value Bucketing/Grouping Functions—Usable in Formula Copy Paste

FIG.91AthroughFIG.92Eexamples our multi-value bucketing/grouping functions supporting the copy paste of formulas referencing the bucket/group values. The charity user is copying the formulas created inFIG.89AandFIG.89Bin cells ‘B4’ and ‘C4’9144highlighting the rows9173and clicking the ‘Copy’ button9112. When they click ‘Paste’9116inFIG.91Bthey get the results illustratively calculated inFIG.92AthroughFIG.92Epopulating the cells ‘B5’ to ‘C13’9177inFIG.91B. Each of the copy pasted formulas references one of the multi-value bucket/group function generated values (and uses their associated data) as exampled by the cell ‘B5’9157formula9127referencing cell ‘A5’9156. As mentioned before, these examples use simple data summarization functional formulas, but our multi-value bucket/group function technology also supports copy paste of complex analytical function formulas value/formulaic references like those inFIG.76BthroughFIG.79C.

Multi-Value Bucketing/Grouping Functions—Usable in Formula Flex Copy Paste

FIG.93AthroughFIG.93Bexamples our multi-value bucketing/grouping functions supporting flex copy paste of formulas referencing the bucket/group values. The charity user is copying the formulas created inFIG.89AandFIG.89Bin cells ‘B4’ and ‘C4’9344highlighting the rows9353and clicking the ‘Copy’ button9312. When they click ‘Paste’ dropdown triangle9316inFIG.91Bto get the dropdown9327where they click the ‘Flex’ selection9337to get the results illustratively calculated inFIG.92AthroughFIG.92Epopulating the cells ‘B5’ to ‘C13’9377inFIG.93B. These values are created using the associated data for the buckets/groups in cells ‘A5’ to ‘A13’9376and will flex with any changes to the formula populating those cells. As mentioned before, these examples use simple data summarization formulas, but our multi-value bucket/group function technology complex also supports copy paste of complex analytical function formulas value/formulaic references like those inFIG.76BthroughFIG.79C.

FIG.94AthroughFIG.96Bexamples the filling and flexing capabilities of our multi-value bucketing/grouping functions and the flex copy pasted cells referencing them.FIG.94Aexamples the charity user turning off bucket/group filling by first clicking the ‘CHANGE OPTIONS’ button9422to open the HINT9434, clicking on the ‘FILLING’ option9433to open the filling dropdown9435where they click ‘OFF’9445to remove the two filled buckets/groups9462inFIG.94A and9482inFIG.94Ain the buckets/groups9466inFIG.94B. This causes the flex copy paste cells9467to shrink (flex).FIG.95Aexamples how each of the ‘WRITE_BUCKET’ cells has a formula, exampled for cell ‘A11’9582with the formula9524shown in the formula bar. This formula9524:

could instead have been a ‘BUCKET’ freestanding formula populated by the ‘WRITE’ as follows:

FIG.95Bexamples how the flex collapsed cell ‘A12’9586(previously populated by a bucket/group with ‘FILLING’ ‘ON’) has no formula9527. And in this embodiment is also shown to not be part of the bucketed/grouped area because it lacks the orange outlining for both cell ‘A12’9586and the formula bar9527and has the ‘fx’ button9526instead of the ‘orange ‘CHANGE OPTIONS’ button9522shown inFIG.95A. Likewise, the flex copy pasted formula each have a formula9624as exampled for cell ‘C11’9685inFIG.96Awhile the previously populated flex cells have no formula9627as exampled for cell ‘C12’9688inFIG.96B. In this embodiment the flex blue and related orange cell outlining and ‘FLEX OPTIONS’ button9622shown inFIG.96Ais gone with no cell outlining the ‘fx’ button9626instead inFIG.96B.

FIG.97AthroughFIG.99Fexamples our multi-value bucketing/grouping function technology supporting constraints/filters and user selected labels in generating values.FIG.97Aexamples our ‘WRITE_BUCKET_X’ function formula9727for cell ‘A4’9742generating bucket/group values9772with no constraint.FIG.97Bthen examples the same ‘WRITE_BUCKET_X’ function for cell ‘A4’9746generating bucket/group values9766with a constraint of ‘country {“Canada” }’9736in the functional formula9727. The difference is the no constraint formula9724populates eight cells ‘A4’ to ‘A11’9772(as illustratively exampled inFIG.98AthroughFIG.98F) while the constrained formula9727populates only five cells ‘A4’ to ‘A8’9766(as illustratively exampled inFIG.99AthroughFIG.99G). All of this was done where the user had previously selected a manually set label using ‘to’ between values rather than the default automatic usage in this embodiment of ‘-’. However, the user also could have opted to create a custom label which in this example they did not do. The user also selected the increment of ‘1000’ from the set of increment specifications they were offered which includes all the check marked options inFIG.98Dbut none of the ‘X’ marked specifications because those either generate no bucketed/grouped values or generate only one bucket for all the data.

FIG.100AthroughFIG.101Eexamples our multi-value bucketing/grouping function technology supporting date buckets/groups and intelligent bucket/group increment options.FIG.100Aexamples the charity user in the process of creating a ‘WRITE_BUCKET_X’ formula at the stage of selecting/specifying10043the ‘increment’10053value from the HINT10073. The ‘INCREMENTS’ are intelligently determined by our application as exampled inFIG.101C. Here our application works as previously described to determine the set of bucket/group increment specifications fitting the data, namely results with at least two values that are different than the data ungrouped. In this example eliminating ‘Minute’ and ‘Hour’ because those are not specified in the dataFIG.101B. Our technology eliminates ‘Day’, it does not result in bucketing/grouping of at least two unique values in the data as the data is already in the equivalent day buckets/groups. ‘Week’, ‘Month’ and ‘Quarter’ are all presented to the user because they result in at least two buckets/groups and group at least two unique data values. ‘Year’ and ‘Decade’ are not presented to the user because they do not result in more than one group/bucket given this data. The user is also presented with a ‘Custom’ specification option where they can select custom increments of days, weeks or even months in this example. The user selects ‘Week’10063in the hint10073which gets populated10028into the finished formula10027giving the buckets in cells ‘A4’ to ‘A11’10046from the illustrative steps exampled inFIG.101AthroughFIG.101E.

FIG.102AandFIG.102Bexamples that our multi-value bucketing/grouping function generated date buckets are usable with their associated data as our previous numerical examples.FIG.102Aexamples the user populating cells ‘A4’ to ‘A12’10262with weekly date buckets from the formula10224in cell ‘A4’10242.FIG.102Bthen examples the user doing the ‘COUNT’ formula calculation10227in cell ‘B7’10267using the bucket/group value10228and its associate data referenced in cell ‘A7’10266from the illustrative steps exampled inFIG.68AthroughFIG.68E.

Multi-Value Bucketing/Grouping Functions—Alphabetical/Alphanumeric Buckets/Groups with No Fill and Constraints

FIG.103AthroughFIG.105Cexamples our multi-value bucketing/grouping function technology supporting alphabetical text buckets/groups with no fill and a constraint, and their usage with their associated data in cell functional formulas.FIG.103Aexamples the user finishing the formula10323with a click10353in a hint10343to populate cells ‘A4‘ through’A14’10386inFIG.103Bwith alphabetical buckets generated from the automatic illustrative steps exampled inFIG.104AthroughFIG.104E. The charity user then does the ‘AVERAGE(donation)’ and ‘COUNT(donation)’ calculations in cells ‘B9’ and ‘C9’10378referencing the bucket/group value ‘J’10376and its associated data via the illustrative steps automatically done for the cell formulas as exampled inFIG.105A/FIG.105B10553andFIG.105C10548. Exampling the use of alphabetical buckets/groups in cell formulas which in this situation are simple data summarization calculations but could have instead been complex analytical formulas using multiple range/array and/or non-range/array functions with multiple algebraic operators and functions within functions.

There are other mix and match combinations of capabilities and types of data supported by our ‘WRITE . . . BUCKET . . . ’ function variants, however rather than continuing to do the mix and match examples we are going to move to exampling how our bucketing/grouping technology can be added as an option to existing functions. We will example two different types of function situations, one where it is used in a function that populates multiple cells and the second where it is used in a function that populates a single cell.

Bucketing/Grouping Optional Specification in Functions Populating Multiple Cells—2D Function

Our technology supports many different functions which can populate multiple cells with values. A number of these have been the subject of previous patent filings and referenced herein.FIG.106AthroughFIG.113Bexamples one of our two-dimensional multiple cells populating functions (WRITE_CALC_2D) with the addition of our new optional buckets/groups capability working for both numeric and date bucketing/grouping. ‘WRITE_CALC_2D’ is a versatile version of the two-dimensional functions that were the subject of our U.S. Provisional Patent Application No. 63/240,828. It combines several capabilities including the ability to do calculations with range/array functions, non-range/array functions and/or complex algebraic operations. We will start with the charity user doing a simple ‘SUM’ calculation and then will later example complex calculations using bucketed/grouped values.

FIG.106Aexamples the charity user looking to setup a two-dimensional summary of their donations by district and date buckets/groups. They have created the formula10624they desire:

However, this formula will not bucket/group the ‘district’ and ‘date’ values so in this embodiment the user clicks ‘Options’10653in the hint10663. This opens the ‘OPTIONS’ hint10667inFIG.106B. The user then selects the ‘BUCKETS’ specification10744inFIG.107Ato open the ‘BUCKET SETTINGS’ popup inFIG.107B. This gives the user the option to bucket/group the ‘WRITE_CALC_2D headings10738. The user starts by clicking the ‘district’ bucket ‘Increment’ dropdown selector10728inFIG.107Ato get the ‘district’10757Intelligent bucketing/grouping options10768inFIG.107C. The user then selects ‘10’10788inFIG.107Das shown in10772inFIG.107E. The user then decides to change the grouping/bucketing labels by clicking10773(and notes that this embodiment defaults to the ‘FILL’ checked10774as ON, which they are fine with). This click opens the ‘Label’ selector10824inFIG.108Awhich gives the user the option to select a different labelling approach or set a custom one. They select the ‘[num] to [num]’ option10839inFIG.108B.

The user then decides they would like ‘date’ buckets/groups as well, so inFIG.108Cthe click the bucket selector10858in getFIG.108Dthe ‘date’ Intelligent bucketing/grouping options10888with the ‘OFF’ default10878, ‘Week’, Month’, ‘Quarter’10888, and ‘custom’ selections in the dropdown selector10868. The user then changes the ‘OFF’ setting10878to ‘Month’10893inFIG.108Eto see inFIG.109Athat the default label for ‘Month’ in this embodiment is ‘Abbr.’ (abbreviation). They want to see fully spelled out ‘Month’ labels, so they click the selector dropdown10933to get the selections10973inFIG.109B. The user then changes the ‘Abbr.’ setting10963to ‘Full’10993inFIG.109Cand sees they have set the ‘BUCKET’ specification to what they want inFIG.109D. They click ‘Save’10948and are returned to the ‘OPTIONS’ hint inFIG.109Ewhere they can see that the ‘BUCKETS’ specification shows ‘ON’10978. At that point the user is happy and clicks ‘Go back’10987and then hits ‘ENTER’ to get the outcome in cells ‘A4’ to ‘D11’11067inFIG.110. Those results are shown for the formula11025in cell ‘A4’ using an embodiment where the bucketing/grouping specification arguments are invisibly recorded although as we will example later those arguments could also be visible.

FIG.111AthroughFIG.111Fillustratively examples the automatic generation of the six 10-increment filled numeric buckets11073inFIG.110. Those steps determine the bucket increment specifications presented to the user as exampled inFIG.111C. In this embodiment those specifications are limited to specifications that generate two or more buckets where at least one bucket combines unique values such as the ‘50 to 59’ bucket11148combines the unique values ‘52’. ‘53’, ‘54’ and ‘56’. The steps also example the auto determination of the bucket start value inFIG.111Dand end value inFIG.111E. These values match the integer data type of the ‘district’ data and therefore the bucket/group values begin and end with integers (e.g., ‘0 to 9’ and ‘10 to 19’).FIG.112AandFIG.112Billustratively examples the automatic generation of the three monthly increment date buckets11055inFIG.110.FIG.112CandFIG.112Dillustratively examples the automatic generation of the calc_2D ‘SUM’ values11075inFIG.110. ThenFIG.113AandFIG.113Billustratively examples the two-dimensional organization of the results and the formatting for population to the cells ‘A4’ to ‘D11’11067inFIG.110. In this embodiment the specification arguments are recorded invisibly (per our contemporaneous U.S. Application No. 63/337,576, titled “Methods and Systems for Spreadsheet Function and Flex Copy-Paste Control of Formatting and Use of Selection List Panels”) and therefore the formula11025inFIG.110has not changed from 10624 inFIG.106Adespite the specification changes. However, in other embodiments those specification argument(s) could have been visible.

FIG.114AthroughFIG.118Dexamples numeric and date buckets/groups optionally setup in our ‘WRITE_CALC_2D’ function with multiple constraints and visible and invisible bucket/group option function formula arguments.FIG.114Aexamples the charity user looking to setup a two-dimensional summary of their donations by district and date buckets/groups as they did inFIG.106AthroughFIG.113B. However, this time it is for a formula with multiple constraints11435and11432. They have created the formula11424they desire:

However, this formula has the two constraints ‘source{“Mail”}’ and ‘country(“US”)’ because the user does not want to include ‘Mail’ donations and only wants donations from the ‘US’. Like before, the ‘WRITE_CALC_2D’ function will not bucket/group the ‘district’ and ‘date’ values in this embodiment unless the user clicks ‘Options’11463in the hint11473. This opens the ‘OPTIONS’ hint11467inFIG.114B. The user then selects the ‘BUCKETS’ option11457and then executes all the actions inFIG.107BthroughFIG.109Ehitting ‘ENTER’ to get the result in cells ‘A4’ to ‘D11’11564inFIG.115A. The formula11522is unchanged (because of the invisible bucketing arguments) although now that the formula has been executed in this embodiment it green outlines the formula bar and the populated cells. It also displays a ‘CHANGE OPTIONS’ button11522in the formula bar as per contemporaneous U.S. Application No. 63/337,576, titled “Methods and Systems for Spreadsheet Function and Flex Copy-Paste Control of Formatting and Use of Selection List Panels”.

FIG.115AthroughFIG.115Dexamples the formula difference between invisible option arguments and visible ones.FIG.115Aformula11524examples the invisible option (including ‘BUCKETS’) arguments with its function formula syntax inFIG.115C.FIG.115Bformula11529examples the visible option (including ‘BUCKETS’) arguments11537with its function formula syntax inFIG.115Dexampling an options argument group with three options populated11598in this example.

FIG.116AthroughFIG.118Dexamples the illustrative steps automatically executed by eitherFIG.115AorFIG.115B. The constraints are applied inFIG.116BandFIG.116C, intelligent options inFIG.117AandFIG.117C, bucketing/grouping with filling inFIG.117BandFIG.117D,2DSUM calculations inFIG.118AandFIG.118Band organizing and formatting of the results inFIG.118CandFIG.118D.

In the preceding ‘WRITE_CALC_2D’ examples employing our added optional bucket/grouping capability (FIG.106AthroughFIG.118D), the bucket start has been automatically set by our technology. However, our technology also supports a user specified start point as we will now example starting with the results11067exampled inFIG.110. Those results and their formula11025are replicated in the formula16524and results16563inFIG.165A. However, the charity user has decided they want the ‘District:’ buckets/groups16572to start at ‘1’ rather than ‘0’. Therefore, in this embodiment the user clicks the ‘CHANGE OPTIONS’ button16522to open the ‘OPTIONS’ hint examples inFIG.166A. The user then clicks the ‘BUCKETS’ option16623to open the ‘BUCKET SETTINGS’ UIFIG.166Bwhere the user clicks into the ‘district’ ‘Start:’ box with the value ‘0’16683. They then change that value to the desired ‘1’16687inFIG.166Cwhich in this embodiment automatically increments the ‘End:’ value by one from ‘59’16684to ‘60’16688. The user is fine with that value, although they could have specified another value, so they click ‘Save’16698to return to the ‘OPTIONS’ hintFIG.166D. The user then clicks ‘Go back’16647and hits ENTER/RETURN to deliver the revised bucket values16576inFIG.165Bstarting with ‘1’.

Bucketing/Grouping Optional Specification in Functions Populating Multiple Cells—1D Function

FIG.168AthroughFIG.171Cexamples the use of our optional bucketing/grouping capability in one of our one-dimensional multiple cells populating functions, our ‘WRITE_V’ function, with one dimensional paired (nested) NON bucketed/grouped values and bucketed/grouped values. It is an example of the functions which were the subject of U.S. application Ser. No. 16/191,402, entitled, “Methods and Systems for Connecting A Spreadsheet to External Data Sources with Ordered Formulaic Specification of Data Retrieved” filed Nov. 14, 2018, now U.S. pat. No. 11,36,929, issued 15 Jun. 2021.

FIG.168Aexamples the user in this embodiment starting to access the optional bucketing capability by clicking the ‘WRITE OPTIONS’ button16822to alter the settings for the formula16823in cell ‘A3’16832that has populated the values in cells ‘A3’ through ‘B21’16862. They want to create some vertical headings that they can then use in a number of different analyses and would like one of the fields in those headings to be bucketed/grouped. In the hint or other selection UI presented to the user they select the bucket settings to get in this embodiment UI like the one inFIG.169A. The only field shown there is ‘district’ because the other field ‘source’ has nothing to bucket (i.e., only having two different values and therefore any bucket would be the equivalent of a total). In this example the user then clicks the “OFF’ dropdown triangle16922to open the automatically (intelligently) screened specifications16937inFIG.169Bwhere the generation is exampled inFIG.170Dapplying our screening logic limiting specifications to those that generate two or more buckets where at least one bucket combines values (i.e., has more than one data field value in the bucket). The charity user then selects the bucket increment ‘10’16987inFIG.169Cand then clicks ‘Save’16993inFIG.169D. They then return to the hint or other UI where they confirm the changes to get the result shown in the cells ‘A3’ through ‘B8’ inFIG.168B16847. The values for ‘district’ have been bucketed/grouped with the resulting collapsing of the number of rows in the output. These cells are usable in regular spreadsheet cell calculations with their associated data. In this embodiment the bucket/group arguments are invisible in the formula16828, although they could have been visible as previously exampled. All the application actions during the bucketing/grouping process and in delivering the results are exampled inFIG.170AthroughFIG.171C.

Bucketing/Grouping Optional Specifications in Functions Populating Multiple Cells—1D Paired (Nested) Buckets/Groups

FIG.172AthroughFIG.175Gexamples the use of our optional bucketing/grouping capability in one of our one-dimensional multiple cells populating functions (‘WRITE_V’) with one dimensional paired (nested) bucketed/grouped values.FIG.172Aexamples the user in this embodiment starting to access the optional bucketing capability by clicking the ‘WRITE OPTIONS’ button17222to alter the settings for the formula17223in cell ‘A3’17232that has populated the values in cells ‘A3’ through ‘B27’17262. They want to create vertical headings that they can then use in a number of different analyses and would like both of the fields in those headings to be bucketed/grouped. InFIG.173Athe user then clicks the ‘BUCKETS’ option17323to get the ‘BUCKET SETTINGS' popupFIG.173A. It shows both of the fields populated in the formula17223inFIG.172A. However, in this embodiment, all of the settings are greyed out because Buckets' are “OFF’17363for both of the fields. For brevity's sake we show the user selections inFIG.173C(rather than showing each step of each selection as we have exampled previously). They selected a ‘date’ field ‘Buckets’ increment of ‘Month’17367and a ‘district’ field ‘Buckets’ increment of ‘20’17377. They look at the automatically set starts and ends17388and decide they are good, so they click ‘Save’17398returning back to the hint inFIG.173Dwhere they click ‘Go back’17347and then ENTER/RETURN to see the resulting bucketed/grouped results in cells ‘A3’ to ‘B11’17247inFIG.172B. The bucketing has shrunk the ‘WRITE_V’ output from the 50 cells ‘A3’ through ‘B27’17262inFIG.172Ato the eighteen cells ‘A3’ through ‘B11’17247inFIG.172B. These cells are usable in regular spreadsheet cell calculations with their associated data. In this embodiment the bucket/group arguments are invisible in the formula17228, although they could have been visible as previously exampled. All the application actions during the bucketing/grouping process and in delivering the results are exampled inFIG.174AthroughFIG.175Gincluding the intelligent options, the automatic start and end determination, the integer matching for district and the automated filling. Our technology supports double and beyond bucketing/grouping of values in the different multiple cell populating functions.

Bucketing/Grouping Optional Specifications in Functions Populating Multiple Cells—1D Function with Calcs

FIG.119AthroughFIG.124Cexamples text (alphabetical) buckets/groups optionally set up in one of our one-dimensional multiple cells populating functions (‘WRITE_CALC_V’). It is also a very versatile version of the one-dimensional functions which were the subject of our U.S. application Ser. No. 17/374,901 ADAP 1008-1. It combines several capabilities including the ability to do calculations with range/array functions, non-range/array functions and/or complex algebraic operations. We will start with the Charity user doing some simple average and count data summarization calculations for their donors by first letter of last name.

FIG.119Aexamples the charity user looking to setup a one-dimensional summary of their average and number of donations by last name buckets/groups. They have created the formula11924they desire:

However, this functional formula by itself does not bucket/group the ‘last’ values so in this embodiment the user clicks ‘Options’11953in the hint11963. This opens the ‘OPTIONS’ hint11967inFIG.119B. The user then selects the ‘BUCKETS’ option11947inFIG.119Bto open the ‘BUCKET SETTINGS' popup inFIG.120Afor the field ‘last’12021. The user then clicks the ‘last’ field ‘Increment’ dropdown selector12027inFIG.120Bto get ‘OFF’, “Letter’ and ‘custom’ options12057inFIG.120C. The user then selects ‘Letter’12077inFIG.120Das shown in12087inFIG.120E. The user then decides they are fine with ‘Labels’ default setting of ‘[letter]’12088but decides to change the ‘FILL’ default checked by clicking it unchecked12089. They see that unchecked result12084inFIG.120Fand then click ‘Save’12093to return to the ‘OPTIONS’ HINT12163inFIG.121A. The user then clicks ‘Go back’12173to get the formula hint12157inFIG.121Bwhere they click ‘ENTER’12167to populate the results12263inFIG.122A. This functional formula with its optional specifications has created one set of alphabetical buckets12262and two sets of calculations12264using those buckets/groups, all with their heading labels12253. All employing the ‘date’ constraint12234.

FIG.123AthroughFIG.124Cillustratively examples the steps delivering those results12263inFIG.122A.FIG.123Cexamples the constraining/filtering of the data for the date range ‘2/1/22/ . . . ‘3/31/22’.FIG.123Dexamples the bucket/group creation with NO filling.FIG.124AandFIG.124Bexamples the data organization and then the ‘AVERAGE’ and ‘COUNT’ calculations.FIG.124Cthen examples the formatting of the results and the addition of the heading labels for cells ‘A3’ to ‘C14’12263inFIG.122A. This embodiment has the invisible option arguments (including ‘BUCKETS’) as per our contemporaneous U.S. Application No. 63/337,576, titled “Methods and Systems for Spreadsheet Function and Flex Copy-Paste Control of Formatting and Use of Selection List Panels”.FIG.122Bexamples the same result but with the option arguments12237visible in the cell formula12227. Our technology supports visible and invisible arguments in a broad spectrum of one- and two-dimensional functions for a very broad spectrum of data summarization and data analytics. We will now example such a function employing our bucketing/grouping capability as an option doing complex analytics.

Bucketing/Grouping Optional Specifications in Functions Populations Multiple Cells—2D Complex Analytics

FIG.125AthroughFIG.131Bexamples our Bucketing/grouping optional specification in functions populating multiple cells supporting two-dimensional complex analytics for both numeric and date bucketed values with filling. The scientist has already setup the ‘exp’ numeric buckets and ‘date’ date buckets with filling as previously exampled and indicated in this embodiment by the ‘BUCKET[ON]’12563shown for ‘Options’ in the hint12573inFIG.125A. When the user completes the formula12523by clicking on the ‘factor_3’ value12583and hitting ‘ENTER/RETURN’ they are delivered the results in ‘A4’ to ‘D10’12567inFIG.125Bfor the completed formula12527. That ‘WRITE_CALC_2D’ formula contains the complex calc12528:

It contains multiple range/array functions (i.e., SUM, COUNT, and DEVSQ), multiple non-array/range functions (i.e., SQRT, COS and LOG10), functions within functions (e.g., SQRT in SUM), and many algebraic operations going well beyond data summarization into complex analytics applied to the numeric and date bucketed/grouped data.

FIG.126AthroughFIG.131Billustratively examples the steps automatically delivering those results12567inFIG.125B.FIG.126Cexamples the numeric bucket/group intelligent options.FIG.126Dexamples the creation of the ‘exp’ field buckets/groups.FIG.127Bexamples the ‘date’ bucket/group intelligent options.FIG.127Cexamples the creation of the ‘date’ field buckets/groups.FIG.128throughFIG.130Billustratively examples the bucketed/grouped associated data retrieval and all the calculations for each two-dimensional bucket combination calculated value shown inFIG.130B.FIG.131Aexamples the two-dimensional data organization and thenFIG.131Bexamples the formatting of the results and the addition of the heading labels for cells ‘A4’ to ‘D10’12567inFIG.125B.

Bucketing/Grouping Optional Specification in Functions Populating Multiple Cells—1D Paired (Nested) Buckets/Groups Complex Analytics

FIG.132AthroughFIG.133Bexamples our technology supporting one-dimensional complex analytics in a function employing our bucketing/grouping optional specifications for both numeric and date paired (nested) bucketed values with filling. Like in the previous example, the scientist has already setup the ‘exp’ numeric buckets and ‘date’ date buckets with filling as previously exampled and indicated in this embodiment by the ‘BUCKET[ON]’13263shown for ‘Options’ in the hint13273inFIG.132A. When the user completes the formula13223by clicking on the ‘factor_3’ value13283and hitting ‘ENTER/RETURN’ they are delivered the results in ‘A4’ to ‘D10’13267inFIG.132Bfor the completed formula13227. That ‘WRITE_CALC_V’ formula contains the complex calc13228:

It contains multiple range/array functions (i.e., SUM, COUNT, and DEVSQ), multiple non-array/range functions (i.e., SQRT, COS and LOG10), functions within functions (e.g., SQRT in SUM), and many algebraic operations going well beyond data summarization into complex analytics applied to the numeric and date bucketed/grouped data.

FIG.126AthroughFIG.130B,FIG.133AandFIG.133Billustratively examples the steps automatically delivering those results13267inFIG.132B.FIG.126Cexamples the numeric bucket/group intelligent options.FIG.126Dexamples the creation of the ‘exp’ field buckets/groups.FIG.127Bexamples the ‘date’ bucket/group intelligent options.FIG.127Cexamples the creation of the ‘date’ field buckets/groups.FIG.128throughFIG.130Billustratively examples the bucketed/grouped associated data retrieval and all the calculations for each one-dimensional bucket combination calculated value shown inFIG.130B.FIG.133Aexamples the one-dimensional data organization and thenFIG.133Bexamples the formatting of the results and the addition of the heading labels for cells ‘A3’ to ‘C18’13267inFIG.132B.

As we have shown our technology supports one-dimensional and two-dimensional multi-value populating functions using option specified buckets/groups for calculations ranging from very simple to very complex. In the examples thus far, the calculations were done by the function employing the bucketing/grouping optional specification. However, we will now example calculations done by cell formulas referencing the buckets/groups generated by the optional specification employing function.

Bucketing/Grouping Optional Specification in Functions Populations Multiple Cells—Cell Reference Use in Formulas and Copy Paste

FIG.134AthroughFIG.135Dexamples cell functional formula referenced use of one of the optionally created buckets/groups in the results of a multi-value populating function and then in the copy paste replication of that cell.FIG.134Aexamples in cell ‘C4’13444a cell formula13423value created by a ‘COUNT’ function calculation referencing in ‘A4’13442a bucketed/grouped value of ‘A’ generated by the formula13496inFIG.134Cvia an option setting with an invisible argument.FIG.135A/FIG.135B13523andFIG.135Cillustratively examples the automatically done steps for the formula13423in cell ‘C4’13444doing the ‘COUNT’ calculation for the first bucket ‘A’. The charity user then decides to copy paste cell ‘C4’13444to the cells ‘C5’ to ‘C14’13464clicking ‘Copy’13412and then clicking ‘Paste’13416inFIG.134Bto get the result in cells ‘C5’ to ‘C14’13468.FIG.135A,FIG.135BandFIG.135Dillustratively example the automatically done steps for the copy paste generation of the results in cells ‘C5’ to ‘C14’13468inFIG.134B. All those cell values having used the bucketed/grouped values in cells ‘A5’ to ‘A14’13466and their associated data as exampled by formula13427for cell ‘C5’13449using the bucketed/grouped value in cell ‘A5’13446.

Bucketing/Grouping Optional Specification in Functions Populating Multiple Cells—Cell Reference Use in Flex Copy Paste

FIG.136AandFIG.136Bexamples cell functional formula referenced use of one of the optionally created buckets/groups in the results of a multi-value populating function and then in FLEX copy paste replication of that cell.FIG.136Aexamples in cell ‘C4’13644a cell formula13623value created by a ‘COUNT’ function calculation referencing in ‘A4’13642a bucketed/grouped value of ‘A’ generated by the formula13696inFIG.136Cwith an invisible bucket option setting.FIG.135A/FIG.135B13523andFIG.135Cillustratively examples the automatically done steps for the formula13623in cell ‘C4’13644doing the ‘COUNT’ calculation for the first bucket ‘A’. The charity user then decides this time to do a FLEX copy paste cell ‘C4’13644to the cells ‘C5’ to ‘C9’13654realizing that they are simply setting the direction of the paste not the size of it. They then click ‘Copy’13612and then click the ‘Paste’ dropdown triangle13616inFIG.136Bto get the dropdown selections13627from which they click ‘Flex’13637to get the results in cells ‘C5’ to ‘C14’13668.FIG.135A,FIG.135BandFIG.135Dillustratively example the automatically done steps for the FLEX copy paste generation of the results in cells ‘C5’ to ‘C14’13668inFIG.136B. All those cell values having used the bucketed/grouped values and their associated date in cells ‘A5’ to ‘A14’13666.

Bucketing/Grouping Optional Specification in Functions Populating Multiple Cells—Use Via Referenced Cell

FIG.137AthroughFIG.138Bexamples use of the buckets/groups generated by an optional specification employing function by cell reference (referring to the value and associated data) and by functional calculations referencing that referencing cell and using the value and associated data as if they had referenced the original cell.FIG.137Astarts with cells ‘A4’ to ‘D11’13764which were the result11067inFIG.110. Those results have numeric and date buckets/groups generated by the function option selections inFIG.106AthroughFIG.109E. These buckets/groups are now used directly and indirectly (via a cell) in cell values and cell formulaic calculations.

Cell ‘F4’13756inFIG.137Aexamples a cell generating a bucketed/grouped value of ‘February’, via a formula ‘=B4’13758shown inFIG.137Dthat references the cell ‘B4’13753inFIG.137Awhich contains a bucketed/grouped value generated by the ‘WRITE’ function option result for the formula13724in cell ‘A4’. Our technology supports a cell referencing a bucketed/grouped value to get that value and the associated data with it. So that a formula referencing that cell (‘F4’13756) would access the same values and associated data as if they were referencing the original cell (B4’13753).

Cell ‘F6’13766inFIG.137Aexamples how this use of the referenced cell gives the same result as the use of the original cell as either the formula inFIG.137BorFIG.137Cgives the same result ‘4’. The formula inFIG.137Buses both bucketed/grouped values ‘A6’13717and ‘B4’13718generated by the ‘WRITE’ function formula13724. The formula inFIG.137Cuses one bucketed/grouped values ‘A6’13728from the original area and ‘F4’13729which gets it value by referencing ‘B4’ and yet in our technology delivers the same result of ‘4’13766via the automatically done calculations, illustratively exampled inFIG.138AandFIG.138Bfor the direct use of the data andFIG.138CandFIG.138Dfor the indirect use of the ‘February’ data in ‘F4’.FIG.137EandFIG.137Fexample the same capability for an even simpler formula where the user gets the same result of ‘10’13776whether the formula directly references cell ‘B4’13753as done by13797inFIG.137For indirectly references ‘B4’13753as done by ‘F4’13787inFIG.137E.

While we could show further examples pf the optional specification generated bucket/group value usage by other cells directly or indirectly, instead we will example that type of usage by our single value bucketing/grouping functions and then our multi-value bucketing/grouping functions.

Single Value Bucketing/Grouping Functions—Use Via Referenced Cell

FIG.139throughFIG.140Eexamples use of the buckets/groups generated by three single value bucketing/grouping function results referenced by three cells (getting the value and associated data) and by functional calculations referencing those cells and using their values and associated data as if they had referenced the original cells.FIG.139starts with cells ‘A4’ to ‘A6’13952which were the result of6156inFIG.61Bhaving numeric buckets/groups generated by the actions inFIG.62AthroughFIG.62G. This includes creating the first bucket/group value and then copy paste generating the next two. In this example, the formulas for the cells in rows 4 through 6 are shown in blue in rows 9 through 11 (using a ‘FORMULATEXT’ function). The three ‘BUCKET_10’ function generated values ‘A4’ to ‘A6’13952have their formulas shown in ‘A9’ to ‘A11’13972. Those values are then replicated in cells ‘E4’ to ‘E7’13956with their formulas of ‘=A4’, ‘=A5’ and ‘=A6’ shown in ‘E9’ to ‘E11’13976. Those referenced values are then used in the ‘STDEV’ and ‘AVERAGE’ formula calculations done in cells ‘F4‘ to’F6’13959with their formulas shown in ‘F9’ to ‘F11’13978. As the calculations illustratively exampled inFIG.140AthroughFIG.140Eshow our technology retains the bucketed/grouped data association (seeFIG.140A) across the referenced cells supporting the calculations working as if they referenced the original cells ‘A4’ to ‘A6’13952when instead they reference the referring cells ‘F4’ to ‘F6’13956.

FIG.141examples use of three buckets/groups generated by one multi-value bucketing/grouping function referenced by three cells (getting the value and associated data) and by functional calculations referencing those cells and using their values and associated data as if they had referenced the original cells.FIG.141starts with cells ‘A4’ to ‘A6’14152which are generated by the formula14125in cell ‘A4’14132. It generates non-filled numeric buckets/groups. In this example, the formulas for the cells in rows 4 through 6 are shown in blue in rows 9 through 11 (using a ‘FORMULATEXT’ function). The three ‘WRITE_BUCKET_10’ function generated values ‘A4’ to ‘A6’14152have their formulas shown in ‘A9’ to ‘A11’14172. The values in ‘A4’ to ‘A6’14152are then replicated in cells ‘E4’ to ‘E7’14156with their formulas of ‘=A4’, ‘=A5’ and ‘=A6’ shown in ‘E9’ to ‘E11’14176. Those referenced values are then used in the ‘STDEV’ and ‘AVERAGE’ formula calculations done in cells ‘F4’ to ‘F6’14159with their formulas shown in ‘F9’ to ‘F11’14178. As the calculations illustratively exampled inFIG.140AthroughFIG.140Eshow our technology retains the bucketed/grouped data association (seeFIG.140A) across the referenced cells supporting the calculations working as if they referenced the original cells ‘A4’ to ‘A6’14152when instead they reference the referring cells ‘F4’ to ‘F6’14156.

There are many different variants of using the bucket/group generated cell values however we will now move to the last major type of usage which is in functions or as a function optional specification that generates a selectable list of multiple values within a single cell.

Bucketing/Grouping Optional Specification in Function Populating Multiple Value Selections within a Single Cell

Our technology supports addition of bucketing/grouping as an optional specification to the spreadsheet functions subject of our U.S. application Ser. No. 17/359,418.FIG.142AthroughFIG.148Dexamples one embodiment of our bucketing/grouping optional specification in one of our functions populating multiple value selections within a single cell (e.g., ‘DROPDOWN’ function).

FIG.142Aexamples the charity user creating a dropdown selector cell ‘B3’14243for the formulaic data field, externally or in-cell data sourced, ‘district’14223. However, the formula14224as written will generate a dropdown with a selector for each value of ‘district’ and the user would like ‘10’ increment buckets/groups. This can be accomplished with the addition of our new technology ‘Buckets’ as an option in the hint14263. So, the user clicks the ‘Buckets’ option14257inFIG.142Bopening in this embodiment the ‘BUCKET SETTINGS’ popup inFIG.143A. This popup is for the field ‘district’ and shows all the settings14322greyed out because they are ‘OFF’. The user then clicks the ‘Increment’ dropdown triangle14327inFIG.143Bto open the intelligent option list14357inFIG.143C. InFIG.143Dthe user selects the ‘10’ increment14398and then decides they are fine with the other settings for the ‘Labels’ and with the ‘Fill’ checked on14383, so they click ‘Save’14393inFIG.143Eto return to the hint14563inFIG.145A. In this embodiment which has invisible ‘Bucket’ arguments the bucket/group settings are displayed in the hint14553but not in the cell formula14524. Had the user instead been using an embodiment with visible ‘Bucket’ arguments then those arguments14517would have shown up in the formula14527and14547and the hint14557as exampled inFIG.145B(or in just the formula and not the hint). In either embodiment the user clicks ‘ENTER’14573or14577to populate the first bucket/group into cell ‘B3’14643inFIG.146A or14647inFIG.146B.FIG.144AthroughFIG.144Eexample the illustrative steps automatically taken by our technology to deliver the buckets/groups in the ‘DROPDOWN’ function with the dropdown options shown inFIG.144F.

FIG.147AthroughFIG.148Dthen examples the user using that ‘DROPDOWN’ to change the bucketed/grouped value. The user clicks the dropdown triangle14743inFIG.147A or14747inFIG.147Bto expose the list of bucket/group values14783inFIG.147C or14787inFIG.147D. The user then changes the selection by clicking ‘40-49’14863inFIG.148A or14867inFIG.148Bto see that selection in cell ‘B3’14883inFIG.148A or14887inFIG.148B. In this embodiment this change does not change the formulas (14724inFIG.147Avs.14873inFIG.148C or14727inFIG.147Avs.14877inFIG.148D) because the selected value is not visible in it. Other embodiments could show that selected value.

Before we example the usage of these bucketed/grouped selectable list values in cell formulas and formula calculations we will example how another embodiment of our technology generates the value.

Bucketing/Grouping Function Use in a Function Populating Multiple Value Selections within a Single Cell

FIG.149AthroughFIG.151Dexamples the use of one of our single value bucketing/grouping functions (e.g., ‘BUCKET_10’) in the field of one of our functions populating multiple value selections within a single cell (e.g., ‘DROPDOWN’ function).FIG.149Aexamples a ‘BUCKET_10’ function for ‘BUCKET_10(district)’14923, in the field argument of the formula “DROPDOWN(BUCKET_10(donation))’14924. This formula automatically executes the illustrative example steps inFIG.144A,FIG.144B,FIG.144DandFIG.144Eto deliver the ‘0-9’ value to cell ‘B3’14943inFIG.149Awith invisible ‘BUCKET’ arguments or to ‘B3’14947inFIG.149Bwith visible ‘BUCKET’ arguments14928exampled in the formula14927.

FIG.150AthroughFIG.151Dthen examples the user using the ‘DROPDOWN’ to change the bucketed/grouped value. The user clicks the dropdown triangle15043inFIG.150A or15047inFIG.150Bto expose the list of bucket/group values15083inFIG.150C or15087inFIG.150D. The user then changes the selection by clicking ‘40-49’15163inFIG.151A or15167inFIG.151Bto see that selection ‘40-49’ in cell ‘B3’15183inFIG.151C or15187inFIG.151D. Neither of the ‘DROPDOWN’ function formulas15173inFIG.151A or15177inFIG.151Bdisplayed the selected value in the formula although in another embodiment that value would have been visible.

Selectable List of Buckets/Groups within a Cell—Reference Usage

FIG.152AthroughFIG.153Eexamples the use of a function populating multiple value selections within a single cell (‘DROPDOWN’) populated cell with a selected bucket/group value and its associated data referenced in a ‘SUM’ function formula. The ‘DROPDOWN’ cell15243inFIG.152Acould have been created by either of the previously described approaches, the function option or the function within a function and it would perform the same way in our technology. The ‘SUM’ formula15224for cell ‘B4’15253references the ‘district’ value for the cell in ‘B3’15243containing the bucketed/grouped value ‘0-9’. The automatically calculated value of ‘$40,625.00’15253is illustratively exampled inFIG.153AthroughFIG.153D. However, when the charity user changes the ‘DROPDOWN’ cell value to ‘40-49’15247inFIG.152Bthat automatically triggers a recalculation of the value in cell ‘B4’15257to the value “$15,225.00’ as exampled inFIG.153AthroughFIG.153CandFIG.153E. Our technology has supported the formulaic use in cell calculations referencing the selectable list of buckets/groups within a cell. While this example was done with a very simple functional formula, our technology supports referenced usage of selectable lists of buckets/groups with a cell in complicated analytical formulas. Our technology also supports selection of multiple buckets/groups within a single cell as exampled next.

Bucketing/Grouping Optional Specification in Function Populating Multiple Value Selections within a Single Cell—Multiple Group Selection

Our U.S. application Ser. No. 17/359,418 supports selection of multiple values within a single cell. With the addition of our bucketing/grouping technology the combination supports selection of multiple buckets/groups within a single cell and then its usage by other cell formulas.

FIG.154AthroughFIG.155Fexamples the result of the use of a bucketing/grouping optional specification in a function populating multiple value selections within a single cell and supporting the selection of multiple different bucketing/group values with invisible and visible bucketing/grouping formula arguments.FIG.154Aexamples the outcome of the charity user setting up a bucketing/grouping specification as exampled in the automatically executed steps illustratively exampled inFIG.155AthroughFIG.155F.FIG.155Bexamples the constraint (filter) of the data to ‘country{“US”}’15425as shown in the formula15424inFIG.154A.FIG.155Cexamples the creation of the intelligent option selections and those selections not offered. The user then selects ‘Week’ which is applied by our application to create the buckets/groups inFIG.155D. Once difference with the ‘DROPDOWN_MANY’ function in this embodiment is that it also displays the number of dates within each bucket, so our application also does those counts.FIG.155Eexamples the values which are displayed in the ‘DROPDOWN_MANY’ value selector UI15464inFIG.154AwhileFIG.155Fexamples the default value which in this embodiment is ‘!ALL’ shown in cell ‘B3’15443inFIG.154A.FIG.154Bexamples the exact same set of user and application actions asFIG.154AandFIG.155AthroughFIG.155Fexcept for an embodiment where the option arguments are visible15437as shown in the formula15427inFIG.154Bfor the equivalent cell ‘B3’15447showing the same ‘DROPDOWN_MANY’ value selector UI15467.

FIG.156AandFIG.156Bexample the charity user having changed the bucket/group selections to four values (15674inFIG.156A and15677inFIG.156B) in the ‘DROPDOWN_MANY’ value selector UI (15664inFIG.156A and15667inFIG.156B) with invisible and visible bucketing/grouping formula arguments. In this embodiment the four values (in this example bucketed/grouped values) are indicated in the cell with ‘*4*’ shown in ‘B3’ (15643inFIG.156A and15647inFIG.156B) exampling our technology supporting the selection of multiple bucket/group values within a single cell.

FIG.157AandFIG.157Bexample the same UI selector outcome asFIG.156AandFIG.156Bhowever achieved using ‘BUCKET’ functions within the ‘DROPDOWN_MANY’ functions. The invisible bucket argument example inFIG.157Ahas a formula15724:

which uses ‘BUCKET_WEEK(date)15714for the field input, thus supplying weekly buckets of the field ‘date’ for the values in the UI selector15764. In this example the user has selected the same four values15774inFIG.157Aas in15674inFIG.156Aand for usage purposes the cell values (15743inFIG.157A and15643inFIG.156A) and their related data are the same.FIG.157Bexamples the same outcome except for a ‘BUCKET’ function with visible bucket arguments15717for the same four selections15777in the same ‘DROPDOWN_MANY’ UI value selector15767. As exampled next, usage of these values and their related data is the same regardless of the different ways our technology supports creating those values.
Selectable List of Buckets/Groups with Multiple Values within a Cell—Reference Usage

FIG.158AthroughFIG.159Hexamples the use of a function populating multiple value selections with multiple values within a single (‘DROPDOWN_MANY’) populated cell with four selected bucket/group values and their associated data referenced in a ‘SUM’ function formula. The ‘DROPDOWN_MANY’ cell15843inFIG.158Acould have been created by either of the previously described approaches, the function option or the function within a function and it would perform the same way in our technology.FIG.158Aexamples usage of cell ‘B3’ (15443inFIG.154A or15447inFIG.154Bor its equivalents done with the formulas inFIG.157AorFIG.157Bselecting ‘!ALL’) in a ‘=SUM(donation(date{B3}))’15824formula in cell ‘B4’15853referring to the ‘date’ field bucketed/grouped values in ‘B3’15843. The calculations automatically done for cell ‘B3’15843and cell ‘B4’15853inFIG.158Aare illustratively exampled inFIG.159AthroughFIG.159F. When the charity user changes the ‘DROPDOWN_MANY’ selections in cell ‘B3’15847inFIG.158Bto the equivalent of the four values selected in15674inFIG.156A,15677inFIG.156B,15774inFIG.157A or15777inFIG.157Bthat automatically recalculates the value in cell ‘B4’15857as illustratively exampled inFIG.159AthroughFIG.159D,FIG.159GandFIG.159H. Thus, the calculation now done in cell ‘B4’15857has used the four bucketed/grouped values and their associated data from cell ‘B3’15847.

As we have shown our selectable list of buckets/groups within a cell supports numeric and date bucketing/grouping and its usage supports functional analytics. It also supports text bucketing/grouping as we will example next.

Bucketing/Grouping Optional Specification in Function Populating Multiple Value Selections within a Single Cell—Text Example

FIG.160AthroughFIG.161Eexamples the use of a text (alphabetical) bucketing/grouping optional specification in a function (‘DROPDOWN_MANY’) populating multiple value selections within a single cell and supporting the selection of multiple different bucketing/group values with a constraint (filter), and no filling.FIG.160Aexamples the selection of the bucket/group setting via an optional capability added to the ‘DROPDOWN_MANY’ function where the user has set an increment of ‘Letter’, a label of just the ‘letter’ and has filling ‘OFF’16053. They then finish the formula16024by clicking ‘ENTER’16063to populate the cell ‘B3’16047inFIG.160Bwith ‘!ALL’ via the automatically executed steps illustratively exampled inFIG.161AthroughFIG.161E. When the user clicks the triangle in cell ‘B3’16047they get in this embodiment the popup16067within in which they can select any combination of the alphabetical bucketed/grouped values.

While we could continue to example mix and match combinations of the bucketing/grouping capabilities previously exampled for our selectable list of buckets/groups within a cell, e.g., supporting complex analytical cell calculations or indirect usage via another referencing cell, we will instead briefly example the technology supporting our capabilities.

Computer System

FIG.181is a block diagram of an example computer system, according to one implementation. Computer system18110typically includes at least one processor18114which communicates with a number of peripheral devices via bus subsystem18112. These peripheral devices may include a storage subsystem18124including, for example, memory devices and a file storage subsystem, user interface input devices18122, user interface output devices18120, and a network interface subsystem18116. The input and output devices allow user interaction with computer system18110. Network interface subsystem18116provides an interface to outside networks, including an interface to communication network18185, and is coupled via communication network18185to corresponding interface devices in other computer systems or in the cloud and usable for cloud applications.

Storage subsystem18124stores programming and data constructs that provide the functionality of some or all of the modules and methods described herein. These software modules are generally executed by processor18114alone or in combination with other processors.

Memory18126used in the storage subsystem can include a number of memories including a main random-access memory (RAM)18130for storage of instructions and data during program execution and a read only memory (ROM)18132in which fixed instructions are stored. A file storage subsystem18128can provide persistent storage for program and data files, and may include a hard disk drive, SSD, a tape drive, an optical drive, or removable media cartridges. The modules implementing the functionality of certain implementations may be stored by file storage subsystem18128in the storage subsystem18124, or in other machines accessible by the processor.

Bus subsystem18112provides a mechanism for letting the various components and subsystems of computer system18110communicate with each other as intended. Although bus subsystem18112is shown schematically as a single bus, alternative implementations of the bus subsystem may use multiple busses.

Computer system18110can be of varying types including a workstation, server, computing cluster, blade server, server farm, or any other data processing system or computing device. Due to the ever-changing nature of computers and networks, the description of computer system18110depicted inFIG.181is intended only as one example. Many other configurations of computer system18110are possible having more or fewer components than the computer system depicted inFIG.181.

Some Particular Implementations

Some particular implementations and features are described in the following discussion.

Bucketing Function Implementations

One implementation is a method for generating one or more value range bucket/group and its associated data for populating one or more spreadsheet cell, as exampled inFIG.34AandFIG.35AthroughFIG.35GandFIG.34BandFIG.37AthroughFIG.37F, or populating one or more value range bucket/group for use in a spreadsheet cell formula, such as a formula calculating a SUM, COUNT or other predefined/built-in function formula as exampled inFIG.51BandFIG.53AthroughFIG.54F. Wherein at least one of the value range buckets holds multiple unique data values as exampled with the three unique values (‘1098.35’, ‘1500’ and ‘ ’ 1750)3536in the ‘1000-1999.99 . . . ’ bucket3537inFIG.35F. The function has either a user specified3428(inFIG.34B), or function specified3424(inFIG.34A) increment that defines the boundaries or each of the value range buckets/groups. The range of buckets has either a user specified (e.g.,16287and16288inFIG.162C), or function specified (e.g.,FIG.35DandFIG.35E) starting and ending point. The data associated with the bucket values, as exampled inFIG.35A3563for the bucket ‘0-999.99’3527inFIG.35F, is usable by formulas referencing the cell it is populated in (e.g.,4943inFIG.49A,4948inFIG.49B, and50AthroughFIG.50G) or when the bucket function is directly populated in a formula it is usable by that formula (e.g.,5187and,5188inFIG.51B, with52A throughFIG.52G).

Bucketing Function Implementations—Single Cell Population

In an implementation of our technology the bucket/group spreadsheet function populates a single cell with a bucket/group value and its associated data where the bucket value increment defines the boundaries between the value range buckets as exampled inFIG.34AthroughFIG.37Ffor function specified and user specified increments. The data associated with the bucket value is usable by formulas referencing the cell it is populated in, as exampled inFIG.35A3563for the bucket ‘0-999.99’3527inFIG.35F.

Bucketing Function Implementations—Multiple Cell Population

In another implementation of our technology the bucket/group spreadsheet function populates multiple adjoining cells each with a bucket/group value and its associated data where the bucket value increment defines the boundaries between the value range buckets as exampled inFIG.84AthroughFIG.85G. The data associated with the bucket values is usable by formulas referencing the cell it is populated in, as exampled for a single referenced cell in8943inFIG.89A,8948inFIG.89B, and90AthroughFIG.90Gand exampled for the full set of populated cells in9177inFIG.91B, and92AthroughFIG.92E.

Bucketing Function Implementations—Multiple Buckets Single Cell Population

In another implementation of our technology the bucket/group spreadsheet function populates multiple selectable buckets/group value ranges into a cell where the user can select a single bucket/group and its associated data as exampled inFIG.176AthroughFIG.177F. The same embodiment can also populate multiple bucket/group values and their associated data into a cell as exampled inFIG.178AthroughFIG.179F. The embodiment allows the user easy access for selection of the multiple buckets/groups as exampled inFIG.178AandFIG.178B. And as with all our populated bucket/group populated cells, the values and associated data are usable by other cell formula calculations.

Bucketing Function Implementations—Use in Range/Array Function Formulas

In another implementation, our bucket/group spreadsheet function is directly usable in a functional formula employing a range or array function, e.g., like those listed inFIG.167.FIG.51BandFIG.53AthroughFIG.54Fexamples that for a functional formula using SUM and a functional formula using COUNT with each of the two functions employing one of our bucket spreadsheet prebuilt functions within an argument. In our technology those functional formulas employing a bucket prebuilt function can be increment copy pasted as exampled inFIG.58AthroughFIG.60D.

Bucketing Function Implementations—Use in Multicell Populating Functions

In another implementation our technology supports the use of our bucketing/grouping functions in our multicell populating functions as exampled inFIG.80AthroughFIG.83D. Where the multicell populating functions (e.g., WRITE_V, WRITE_CALC_H and WRITE_CALC_2D), subject of our filings U.S. application Ser. No. 16/191,402, U.S. application Ser. No. 17/374,901 and U.S. Application No. 63/240,828, replicate the bucket values and their associated data for cell population and were functionally supported (e.g., WRITE_CALC_V) calculations within the function (the calc argument or arguments). The bucket values and their associated data are also usable in referenced formulas as exampled inFIG.134AthroughFIG.135D.

Bucketing Function Implementations—Use in Single Cell Multi-Values Selecting Functions

In another implementation our bucketing/grouping functions can be employed in an argument by our single cell multi-values selecting functions (e.g., DROPDOWN, DROPDOWN_MANY), subject of our filing U.S. application Ser. No. 17/359,418. They then populate one or more bucket/group within a cell as exampled inFIG.149AthroughFIG.151D. That embodiment then supports usage of those buckets (values and associated data) in other cell formulas as exampled inFIG.152AthroughFIG.153E. In another use of that embodiment where the bucketing/grouping function is used in a multi-value selecting function (e.g., DROPDOWN_MANY), the combination of functions can populate multiple buckets within a single cell as exampled inFIG.157AandFIG.157B. That embodiment then supports usage of those buckets (values and associated data) in other cell formulas as exampled inFIG.158AthroughFIG.159Hallowing users to select multiple buckets into a cell and then use those buckets and their associated data in calculations by other cells referencing that cell.

The data source applicability discussed in this section applies to all the preceding and following embodiments. All the embodiments of our bucketing/grouping predefined function technology supports formulaic data as exampled inFIG.36AthroughFIG.36C. That data can be sourced from our non-spreadsheet cell (NSC) externally sourced data described in our previously cited filings and exampled inFIG.36C. That formulaic data can be sourced from spreadsheet cells as described in our previously cited filings and exampled inFIG.36B. And that data can be sourced from any combinations of the data sources when appropriately matched or joined together as exampled in our previous filings using data from different data tables.

The additional bucketing related capability embodiments discussed next apply to all the preceding bucketing function embodiments.

Our technology works for the different data types as exampled inFIG.65AthroughFIG.66Ffor time/date data. Those figures example our spreadsheet technology uniquely supporting weekly increments for bucket functions generating a single bucket.FIG.100AthroughFIG.101Eexample an embodiment where our technology supports weekly increments for our bucket function technology that populates weekly bucket values to multiple adjacent cells. Where for these embodiments the starting/ending point of the week is function specified or user specified.

Our technology works for real numerical data as exampled inFIG.34AthroughFIG.37Fand integer numeric data as exampled inFIG.61AthroughFIG.62I. Where an implementation of our technology for the numerical data type has an automatically generated start value which is the closest number equal or lower than the lowest data value for the specified field which when divided by the increment value yields an integer.FIG.35Dexamples that for real data values andFIG.62Eexamples that for integer data values. Thereby ensuring the automatically specified bucket ranges are more normal bucketing breaks like ‘0 to 999.99 . . . ’rather than ‘150 to 1149.99 . . . ’ or ‘0 to 9’ rather than ‘3 to 12’.

In another embodiment our technology automatically sets the numeric bucket ranges to distinguish data fields which are integer from those that are real. Where the integer bucket ranges are integers as exampled inFIG.64D. Real bucket value ranges have at least one real value as exampled inFIG.35Ffor single cell populating bucket functions andFIG.98Ffor multicell populating bucket functions. In a related embodiment for the numeric data fields, our technology automatically sets the bucket ranges so there is no ambiguity as to where values fall, i.e., no overlapping values ranges.FIG.35Fexamples this for real values with the ‘0-999.99 . . . ’ not overlapping with the next bucket ‘1000-1999.99 . . . ’ and so on.FIG.64Dexamples this for integer values with the ‘0-9’ not overlapping with the next bucket ‘10-19’ and so on.

Another implementation of our technology supports text buckets/groups. Where the increment can be by first characters, sets of characters or other text combinations (e.g., charities and other entities often have id numbers where the first x characters are geographic or other designations). This is exampled for single cell populating bucket functions inFIG.70AthroughFIG.71Eand exampled for multiple cells populating bucket functions inFIG.103B10386.

In another embodiment the bucket/group range value labels can be changed by the user via a selection as exampled inFIG.46A4643andFIG.46B4676or can be custom created by the user for usage and replicated by our technology as exampled inFIG.47AthroughFIG.47C. Thus, offering users the capability to tailor the presentation of the bucket range values.

In another embodiment our technology supports the automatic filling of a missing bucket increment or multiple missing increments as exampled by the automatic settings with the fill checked on16274inFIG.162Bfor the bucket function16223inFIG.162A. Where the bucket filling is exampled inFIG.44D4437and4447for a single value bucket function.FIG.85G8538and8558examples automatic filling for a multiple value bucket populating function shown inFIG.164Bwith the automatic filling checked on16475.

In another embodiment of our technology our bucketing/grouping predefined spreadsheet function has the capability to constrain (filter) the data used in the determination of the buckets as exampled inFIG.61AthroughFIG.62Ifor a single value bucketing function. And for the multiple value bucket populating function as exampled inFIG.97AthroughFIG.99G. In similar embodiment for a bucketing function used as an argument in another function, the bucketing function uses the filtering (constraining) of the data by the other function in the determination of its buckets. This is exampled in the formula15724inFIG.157Awhere the constraining (filtering) of the bucket function15714values is done by the function ‘DROPDOWN_MANY’ into which the bucket function15714is populated as an argument. The DROPDOWN_MANY’ constraint argument ‘country{“US”}’15733constrains the bucket function15714range values shown in the UI15764.

Another embodiment of our technology supports our bucket values and their associated data for referenced use in other cell formulas.FIG.49AthroughFIG.50Gexamples the direct reference usage of the bucket/group values in a formula calculation within a different cell.FIG.89AthroughFIG.90Gexamples the direct reference usage in a formula calculation of a multi-value bucketing function populated bucket/group range value and its associated data. We called the preceding direct reference usage as opposed to the capabilities we will example next which we will call indirect reference (e.g., replicated) usage of the bucket values and their associated data.

FIG.13913956and13976example our embodiments' capabilities to support replication of the bucket/group values and their associated data in another cell. Values replicated this way support calculations using their associated data as if they were the originally populated cells.FIG.13913959(calculated values) and13978(formulas) example a formula calculation using the replicated data13956(cells). With those replicated cells using the originally created bucket/group values13952and their related data as exampled in the illustrative automatic calculations done inFIG.140AthroughFIG.140E. Whether the calculations use the original cell values or the replicated bucket cell values makes no difference in our technology to the outcome of the calculations.

A related embodiment supports use of our bucket/group values and their associated data in cell formulas employing range/array functions as exampled inFIG.49AthroughFIG.50G. The breadth of range or array function supported includes those functions exampled inFIG.167. A related embodiment supports the use of a broad spectrum of non-range/array functions working as exampled inFIG.73AthroughFIG.79Cand as exampled in those same figures supports a combination of range/array functions, non-range/array functions and algebraic operators in complex analytics and copy paste replication of the complex analytics. These analytics involve usage of functions in each of the Microsoft Excel compatibility, engineering, financial, math and trigonometry, statistical, and even text families of prebuilt functions. With a substantial number (greater than 100) being non-range/array functions all of which could not be used in PivotTables or Power Pivot PivotTables and certainly not usable in any normal spreadsheet cell calculation using buckets/groupings and their associated data.

Another embodiment of our bucket function technology supports bucket incremental progression copy paste when used as a cell formula or used in a cell formula.FIG.38AthroughFIG.39Eexamples the copy paste bucket progression (with no bucket filling) of a single value bucket function populated cell to ten paste cells.FIG.55AandFIG.55Bexample the copy paste bucket progression (with filling) done as part of a broader copy paste of a bucket function populated cell and two cells referencing the bucket function formula cell.FIG.180AandFIG.180Bexamples our bucket function technology supporting bucket incremental progression copy paste when used in an argument of a (another) function in a cell formula.

Another embodiment of our bucket function technology supports bucket incremental progression flex copy paste when used as a cell formula or used as an argument in a cell formula.FIG.40AandFIG.40Bexamples the flex copy paste bucket progression (with no bucket filling) of a single value bucket function.FIG.57AandFIG.57Bexamples the flex copy paste bucket progression (with filling) done as part of a broader flex copy paste of a bucket function populated cell and two cells referencing the bucket function formula cell.FIG.58AthroughFIG.60Dexamples our bucket function technology supporting bucket incremental progression flex copy paste when used in a cell formula.

Bucketing Function Implementations—Bucket Replication in Functional Formulas

In another embodiment of our bucket function technology supports incremental bucket replication by a function (e.g., WRITEs) using our bucket function in an argument.FIG.80AthroughFIG.83Dexamples this for a ‘WRITE_CALC_V’ function using a ‘BUCKET_10’ in one of its arguments. Where our multi-cell populating functions replicate the bucket function buckets the way they would otherwise replicate a field in that argument.

Other implementations may include a non-transitory computer readable storage medium storing instructions executable by a processor to perform any of the methods described above. Yet another implementation may include a system including memory and one or more processors operable to execute instructions, stored in the memory, to perform any of the methods described above. We will now shift our discussion of particular embodiments to our bucket/grouping technology added as an optional capability to our existing and future functions (i.e., functions not dedicated to bucketing/grouping).

Bucketing/Grouping Capability Added to Function Implementations

One implementation is a method for adding bucketing/grouping of data as an optional capability to predefined (built-in) spreadsheet functions. The new capability adds to a predefined (built-in) spreadsheet function an optional capability creating one or more bucket values.FIG.142AthroughFIG.148Dexamples a ‘DROPDOWN’ function using an optional bucketing/grouping specification for populating a button selectable value (with this specification a bucket) in one cell.FIG.106AthroughFIG.113Bexamples a ‘WRITE_CALC_2D’ function using optional bucketing/grouping specifications to populate multiple cells with bucket values and multiple cells with calculated values using those bucket values and their associated data. The function works as it previously did except now with the added capability of creating buckets for one or more of its argument inputs. The embodiment includes specifying one or more option of the predefined (built-in) spreadsheet function that creates a plurality of value range buckets exampled in11148inFIG.111F. Where the value range buckets have a starting11176and ending point11178and boundaries between those value range buckets defined by an increment exampled inFIG.107E10772. Where the associated data in at least one value range bucket holds multiple unique values as inFIG.111F11187(‘52’, ‘53’, ‘54’ and ‘56’) with an overall result in populating at least one value range bucket and its associated data into a spreadsheet cell as exampled inFIG.11011073. Where the associated data for each bucket is all the data associated with those bucket values as exampled for the ‘50 to 59’ bucket11187by the associated data11153inFIG.111A.

In another embodiment of our technology the optional specification of bucket/grouping capability appears as a text argument in the functional formula as exampled inFIG.115Bwhere the ‘WRITE_CALC_2D’ formula11527contains two bucketing/grouping arguments11537in the three option arguments11598exampled inFIG.115D.FIG.146Bexamples another function, ‘DROPDOWN’, employing the bucketing/grouping option with the argument visible14617in the formula14627as contrasted withFIG.146Agiving the exact same result with no visible bucketing/grouping argument in its formula14624.

In another embodiment of our technology the optional specification of bucket/grouping capability does not appear as a text argument in the functional formula as exampled inFIG.115Awhere the ‘WRITE_CALC_2D’ formula11524contains no bucketing/grouping arguments. Instead, the bucketing/grouping arguments are visible in a selector list panels such as those exampled inFIG.106AthroughFIG.109Egiving the result inFIG.110.FIG.146Aexamples another function, ‘DROPDOWN’, employing the bucketing/grouping option with the argument invisible in the formula14624as contrasted withFIG.146Bgiving the exact same result with a visible bucketing/grouping argument14617in its formula14627. Once the user has set the optional bucketing/grouping with invisible arguments the user can then reopen the option selection list panel(s) to see the settings and make any desired changes.

Bucketing Optional Specification Implementations—Multiple Cell Population

In another embodiment of our technology the optional bucket/group capability in the built-in function populates multiple adjoining cells each with a bucket/group value and its associated data where the bucket value increment defines the boundaries between the value range buckets as exampled inFIG.168BthroughFIG.171C. That functional capability can also populate multiple bucket/group cells and cells using those buckets/groups and their associated data in calculations as exampled inFIG.119AthroughFIG.124Cfor a WRITE_CALC_V function and exampled inFIG.106AthroughFIG.113Bfor a WRITE_CALC_2D function.

Bucketing Optional Specification Implementations—Single Cell Single-Value and Multi-Value Selecting Functions

In another embodiment our bucketing/grouping optional specification can be employed by our single cell multi-values selecting functions (e.g., DROPDOWN, DROPDOWN_MANY), subject of our filing U.S. application Ser. No. 17/359,418.FIG.142AthroughFIG.148Dexamples our bucketing optional specification utilized in our DROPDOWN function (an example of a single cell single-value selecting function) which then populates a single selected bucket into a cell. That embodiment then supports usage of those buckets (values and associated data) in other cell formulas as exampled inFIG.152AthroughFIG.153E. In a related embodiment our bucketing optional specification is added to our DROPDOWN_MANY function (an example of a single cell multi-value selecting function) which then populates multiple buckets within a single cell as exampled inFIG.154AandFIG.156B. That embodiment then supports usage of those buckets in other cell formulas as exampled inFIG.158AthroughFIG.159Hallowing users to select multiple buckets into a cell and then use those buckets and their associated data in calculations by other cells referencing that cell.

The data source applicability discussed in this section applies to all the preceding and following embodiments. All the embodiments of our bucketing/grouping options added to prebuilt functions supports formulaic data as exampled inFIG.36AthroughFIG.36C. That data can be sourced from our non-spreadsheet cell (NSC) externally sourced data described in our previously cited filings and exampled inFIG.36C. That formulaic data can be sourced from spreadsheet cells as described in our previously cited filings and exampled inFIG.36B. And that data can be sourced from any combinations of the data sources when appropriately matched or joined together as example in our previous filings using data from different data tables.

The additional bucketing related capability embodiments discussed next apply to all the preceding bucketing optional specification function embodiments.

Our technology bucketing optional specification addition to prebuilt spreadsheet functions works for the different data types as exampled inFIG.108D10868for time/date data.FIG.108E10883examples our spreadsheet optional specification bucket technology uniquely supporting weekly increments for bucket function buckets.FIG.154AthroughFIG.155Fexample an embodiment where our spreadsheet function optional specification bucketing technology supports weekly buckets for population into a single cell.

Our spreadsheet function bucket optional specification technology works for numerical data as exampled inFIG.111F. Where an implementation of our technology for the numerical data type has an automatically generated start value which is the closest number equal or lower than the lowest data value for the specified field which when divided by the increment value yields an integer as exampled inFIG.111D11176. Thereby ensuring the automatically specified bucket ranges have more typical round number bucketing breaks like ‘0 to 9’ rather than ‘3 to 12’ or ‘1000 to 1999.99 . . . ’ rather than ‘1023 to 2022.99 . . . ’.

In another embodiment our spreadsheet function bucket optional specification technology automatically sets the numeric bucket ranges to distinguish data fields which are integer from those that are real. Where the integer bucket ranges are integers as exampled inFIG.111F11148and the real bucket data fields have at least one real value as previously exampled. In a related embodiment for the numeric data fields our technology automatically sets the bucket ranges so there is no ambiguity as to where values fall, i.e., no overlapping values ranges.FIG.111Fexamples this for integer values with the ‘0-9’ not overlapping with the ‘10-19’ next bucket and so on (not buckets like ‘0-10’, ‘10-20’ and so on).

Another implementation of our spreadsheet function bucket optional specification technology supports text buckets/groups as exampled inFIG.119AthroughFIG.124C. Where the increment can be by first characters, sets of characters or other text combinations (e.g., charities and other entities often have id numbers where the first x characters are geographic or other designations) as exampled inFIG.123Dfor the first letter of the field values.

In another embodiment the bucket/group range value labels can be changed by the user via a selection as exampled inFIG.108B10839vs.FIG.108A10834or can be custom created by the user for usage and replicated by our technology as previously exampled inFIG.47AthroughFIG.47C. Thus, offering users the optional specification to tailor the presentation of the bucket range values.

In another embodiment our technology supports the automatic filling of a missing bucket increment or multiple missing increments as exampled inFIG.111Fwith buckets ‘10-19’11148and ‘30-39’11158. Thereby easily allowing consistent comparison across similar situations with different buckets missing data.

In another embodiment of our technology our bucketing/grouping optional specification can use the spreadsheet function's capability to constrain (filter) the data in the determination of the buckets as exampled inFIG.114AthroughFIG.118Dand most particularly in the steps illustratively exampled inFIG.116BandFIG.116C.FIG.154AthroughFIG.155Fexamples this capability working in a single cell multi-value selecting function ‘DROPDOWN_MANY’.

Another embodiment of our spreadsheet function bucket optional specification technology utilizes our bucket values and their associated data in formulas by another cell or multiple cells.FIG.137A13766,FIG.137B,FIG.138A, andFIG.138B, examples a cell formula calculation referencing a spreadsheet function optional specification populated bucket/group value and its associated data. These optional specification populated bucket values and their associated data are usable by formula like other formulaic data fields.

Another embodiment of our spreadsheet function bucket optional specification technology replicates our bucket values and their associated data into another cell for formulaic use as if it were the original cell.FIG.137AandFIG.137Dexamples the replication in cell ‘F4’13756(see formula13758inFIG.137D) of an optional specification generated bucket value in cell ‘B4’13753. Then in a related embodiment the replicated value and its associated data can be used in a formulaic calculation as if it were the original optional specification populated value and associated data as exampled inFIG.137A13766,FIG.137C13729,FIG.138C, andFIG.138D. These optional specification populated bucket values with their associated data and their replicates are usable by formulas like other formulaic data fields.

Another embodiment of our spreadsheet function bucket optional specification technology supports use of our bucket/group values and their associated data in cell formulas employing range/array functions as exampled inFIG.137A13776andFIG.137Eas well asFIG.137A13766,FIG.137B,FIG.138A, andFIG.138B. The breadth of range or array function supported includes those exampled inFIG.167. A related embodiment of our spreadsheet function bucket optional specification technology supports the use of a broad spectrum of non-range/array functions working as previously exampled inFIG.73AthroughFIG.79Cand as exampled in those same figures supporting a combination of range/array functions, non-range/array functions and algebraic operators in complex analytics and replication of the complex analytics. These analytics involve usage of functions in each of the Microsoft Excel compatibility, engineering, financial, math and trigonometry, statistical, and even text families of prebuilt functions. With a substantial number (greater than 100) being non-range/array functions all of which could not be used in PivotTables or Power Pivot PivotTables and certainly not usable in any normal spreadsheet cell calculation using buckets/groupings and their associated data.

Other implementations may include a non-transitory computer readable storage medium storing instructions executable by a processor to perform any of the methods described above. Yet another implementation may include a system including memory and one or more processors operable to execute instructions, stored in the memory, to perform any of the methods described above.

Clauses

1. A method for determining and populating at least one value range bucket and associated data into at least one spreadsheet cell or spreadsheet cell formula using a bucket spreadsheet function, including:responsive to a specification of the bucket spreadsheet function, creating at least one value range bucket, among a plurality of range buckets, wherein the associated data in the value range bucket holds multiple unique values in a range of retrieved values, wherein the range has a starting and an ending point (=upper and lower bounds) and a bucket value increment that defines boundaries between the value range buckets;populating the at least one value range bucket and the associated data into a regular spreadsheet cell or spreadsheet cell formula.

2. The method of clause 1, wherein the regular spreadsheet cell can be used in calculations, with its associated data.

3. The method of clause 1, further including:populating each of a plurality of adjoining spreadsheet cells using a bucket spreadsheet function with at least one value range bucket and associated data;wherein a bucket value increment defines boundaries between value range buckets.

4. The method of clause 1, further including:populating at least one spreadsheet cell using the bucket spreadsheet function with a plurality of value range buckets and associated data.

5. The method of clause 1, further including using the bucket spreadsheet function to specify input to a spreadsheet range or array function.

6. The method of clause 1, further including using the bucket spreadsheet function to specify input to a multicell populating function.

7. The method of clause 1, further including using the bucket spreadsheet function to specify input to a user interface picklist function in a cell.

8. The method of clause 7, where the user interface picklist function in a cell uses the bucket spreadsheet function to specify multiple buckets as input and output in the cell.

9. The method of clause 1, wherein the cell predefined spreadsheet function employs formulaic data.

10. The method of clause 9, wherein the formulaic data is sourced from non-spreadsheet cell externally sourced data.

11. The method of clause 9, wherein the formulaic data is sourced from spreadsheet cells.

12. The method of clause 1, wherein a buckets are created for time/date data types.

13. The method of clause 12, wherein a bucket increment specification of week is available for selection and successful use.

14. The method of clause 1, wherein the automatically generated integer/real data type starting point is the closest number below or equal to the first data point which when divided by the increment value yields an integer.

16. The method of clause 15, wherein the ending bucket value eliminates ambiguity over where values fall by eliminating overlapping buckets.

17. The method of clause 1, wherein buckets are created for text data types.

18. The method of clause 17, wherein bucket increment applies to a first character of text data.

19. The method of clause 1, wherein the value range buckets include at least one placeholder bucket that is empty and available to receive an added unique value.

20. The method of clause 1, wherein missing bucket increment(s) are automatically filled.

21. The method of clause 1, wherein the bucketing function has the capability to filter the data used in the determination of the buckets.

22. The method of clause 1, wherein the bucketing function used as an argument in another function uses the filtering of the data by the function in the determination of the buckets.

23. The method of clause 1, further including using the bucket range value and its associated data as input to another cell or cells in formula calculation(s).

24. The method of clause 1, further including using the bucket range value and its associated data as input to another cell or cells to replicate the bucket value and its associated data.

25. The method of clause 1, further including using the bucket range value and its associated data as input indirectly through another cell or cells in formula calculation(s).

26. The method of clause 23, wherein the formula calculation(s) that use the bucket range value and its associated data are spreadsheet range or array functions.

27. The method of clause 23, wherein the formula calculation(s) that use the bucket range value and its associated data are non-range/array functions.

28. The method of clause 23, wherein the formula calculations that use the bucket range value and its associated data are a combination range/array functions, non-range/array functions and algebraic operators.

29. The method of clause 1, wherein the bucket function applies bucket incremental progression copy paste when used as a cell formula or used in cell formula.

30. The method of clause 29, wherein the copy paste is flex copy paste.

31. The method of clause 1, wherein the spreadsheet bucket function usage in an argument of a multicell populating function or in an argument of picklist function supports incremental bucket replication by that function.

32. A non-transitory computer readable memory, the memory impressed with computer instructions that, when executed on hardware, cause the hardware to carry out the method of any of clauses 1-31.

33. A system including processing hardware coupled to memory, the memory impressed with computer instructions that, when executed, cause the hardware to carry out the method of any of clauses 1-31.

34. A method for determining and populating at least one value range bucket and associated data into at least one spreadsheet cell using a spreadsheet function with a specification that defines the value range bucket and its associated data, including:responsive to specification of the spreadsheet function, creating a plurality of value range buckets,wherein the associated data in at least one of the value range buckets holds multiple unique values in an increment of retrieved values,the associated data in the plurality of value range buckets has a starting and an ending point and boundaries between value range buckets are defined by an increment;populating at least one value range bucket and associated data into a spreadsheet cell.

35. The method of clause 34, wherein specification of the spreadsheet function includes specification of a bucket function as an argument that appears as text in the spreadsheet function, wherein the bucket function defines the plurality of value range buckets.

36. The method of clause 34, further including:responsive to a first signal from a user invoking a selection list panel, causing display of the selection list panel that includes arguments for defining the plurality of value range buckets; andreceiving at least one second signal from the user selecting at least one argument option defining the plurality of value range buckets;wherein the definition of the plurality of value range buckets is visible in the selection list panel and not reproduced as text in an argument of the spreadsheet function in the spreadsheet cell.

37. The method of clause 34, further including:populating each of a plurality of adjoining spreadsheet cells using the spreadsheet function and its specifications with at least one value range bucket and associated data;wherein a bucket value increment defines boundaries between value range buckets.

38. The method of clause 34 further including:populating at least one spreadsheet cell using the spreadsheet function and its specifications with a plurality of value range buckets and associated data.

39. The method of clause 34, wherein the cell function employing the bucket specification is one of our multicell populating functions.

40. The method of clause 34, wherein the bucketing function can be used in our single cell multi-value selecting functions.

41. The method of clause 34, wherein the function employed can populate multiple buckets within a single cell.

42. The method of clause 34, wherein the cell predefined spreadsheet function employs formulaic data.

43. The method of clause 42, wherein the formulaic data is sourced from non-spreadsheet cell externally sourced data.

44. The method of clause 42, wherein the formulaic data is sourced from spreadsheet cells.

45. The method of clause 34, wherein buckets are created for time/date data types,

46. The method of clause 45, wherein a bucket increment specification of week is available for selection and successful use.

47. The method of clause 34, wherein the automatically generated integer/real data type starting point is the closest number below or equal to the first data point which when divided by the increment value yields an integer.

48. The method of clause 34, wherein the bucket values matches the integer or real data type of the data specified for bucketing in that integer data has only integer bucket values and real data has at least one bucket value as real.

49. The method of clause 48, wherein the ending bucket value eliminates ambiguity over where values fall by eliminating overlapping buckets.

50. The method of clause 34, wherein buckets are created for text data types.

51. The method of clause 50, wherein bucket increment is the first character of the value.

52. The method of clause 34, wherein value increment bucket labels can be changed by users.

53. The method of clause 34, wherein missing bucket increment is automatically filled.

54. The method of clause 34, wherein the bucketing specification utilizes the function's capability to filter the data used in the determination of the value range buckets.

55. The method of clause 34, further including using the bucket range value and its associated data as input to another cell or cells in a formula calculation.

56. The method of clause 34, further including replicating the bucket range value and its associated data as input to another cell or cells.

57. The method of clause 56, further including using the replicated bucket range value and its associated data in formula calculation as if it were the pre-replication bucket range value and associated data.

58. The method of clause 55, wherein the formula calculation that uses the bucket range value and its associated data is a spreadsheet range or array functions.

59. The method of clause 55, wherein the formula calculation that uses the bucket range value and its associated data contains a non-range/array function.

60. The method of clause 55, wherein the formula calculation that uses the bucket range value and its associated data are a combination of range/array functions, non-range/array functions and algebraic operators.

61. A non-transitory computer readable memory, the memory impressed with computer instructions that, when executed on hardware, cause the hardware to carry out the method of any of clauses 34-60.

62. A system including processing hardware coupled to memory, the memory impressed with computer instructions that, when executed, cause the hardware to carry out the method of any of clauses 34-60.