How can my array-formula be improved?
Our take
In the evolving landscape of personal finance management, the exploration of more efficient formulas and methodologies in spreadsheet applications is both timely and essential. The recent inquiry into improving an array formula for tracking expenses highlights a common challenge faced by many users: the limitations of traditional spreadsheet formulas, especially in Excel, where character limits can stifle creativity and functionality. As the discussion unfolds, it becomes clear that users are seeking innovative ways to streamline their processes without becoming overwhelmed by complexity. This resonates with themes explored in pieces like That 5-minute task in Excel and XLOOKUP replaced VLOOKUP for me and honestly I don’t know why I waited so long, which emphasize the importance of adapting to new tools and features in order to enhance productivity.
The user’s experience with an 8192-character formula underscores a significant limitation in Excel's design. The challenge of rewriting sections multiple times to stay within this limit not only illustrates the frustration that can accompany traditional spreadsheet practices but also raises a crucial question: How can users leverage more advanced features to overcome these barriers? The mention of the MAP function and Power Query connection points to a growing awareness of alternative strategies that could simplify data management. However, the need for manual refreshes when using Power Query can present its own challenges, particularly for those who value real-time data updates. This highlights the broader movement towards integrating more dynamic solutions that allow users to manage their data seamlessly and effectively.
Moreover, the potential integration of Python within Excel, while currently not available to all users, suggests a forward-thinking approach to data analysis and manipulation. The prospect of using Python scripts directly in Excel cells represents a significant leap towards more robust data processing capabilities. This evolution is particularly relevant in a world increasingly driven by data and analytics, where the ability to perform complex calculations and data manipulations quickly can significantly enhance user productivity and decision-making. As noted in the original inquiry, users are eager for solutions that not only simplify their workflows but also empower them to tackle more complex financial analyses with ease.
In reflecting on the current state of spreadsheet technology and personal financial management, it is evident that the conversation is shifting towards more innovative and user-friendly solutions. The need for accessible tools that foster user empowerment is paramount, as seen in the ongoing discussions around Excel's functionalities. Looking ahead, we must ask: How can spreadsheet technology continue to evolve to meet the demands of users who seek both simplicity and power in their data management practices? The answer may lie in a commitment to developing more intuitive interfaces and integrating advanced capabilities that align with the needs of modern users. As we navigate this landscape, staying attuned to these developments will be crucial for anyone looking to enhance their financial tracking and analysis.
The following image is part of a spreadsheet I build for keeping track of my personal finances.
All my transactions are listed in one worksheet. The part you see is a table that sums up all expenses for each month of a given year and different categories. The table is build using a single formula in the top left cell.
Further below you can see the formula and additional commentary that explains the formula.
My formula scrapes excels limit of 8192 characters per formula and I had to rewrite sections multiple times to stay in the limit. For any future projects this might become a problem.
How can I improve this script?
The MAP-function seemed promising but I couldn’t figure out how to make it work for my needs.
What would be a better way to achieve this altogether?
A Power Query connection would probably work but I‘d have to manually hit refresh whenever I‘d add new data.
What would a professional solution look like?
Using office 2024, the PY-Function isn’t available for me. Being able to use python in an excel-cell seems game-changing to me. Would that be a pretty good solution?
The formula:
=LET( comt1; "External variables"; year; G28; source_table; table_transactions; categories; G4:G11; comt2; "month-variables"; january; EDATE(year; 0); february; EDATE(year; 1); march; EDATE(year; 2); april; EDATE(year; 3); mayo; EDATE(year; 4); june; EDATE(year; 5); july; EDATE(year; 6); august; EDATE(year; 7); september; EDATE(year; 8); october; EDATE(year; 9); november; EDATE(year; 10); december; EDATE(year; 11); comt3; "Individual categroy-variables from external range"; catg1; INDEX(categories; 1); catg2; INDEX(categories; 2); catg3; INDEX(categories; 3); catg4; INDEX(categories; 4); catg5; INDEX(categories; 5); catg6; INDEX(categories; 6); catg7; INDEX(categories; 7); catg8; INDEX(categories; 8); comt4a; "Header row from months with formatting"; header_row; HSTACK( TEXT(january; "[$-de-DE]MMMM;@"); TEXT(february; "[$-de-DE]MMMM;@"); TEXT(march; "[$-de-DE]MMMM;@"); TEXT(april; "[$-de-DE]MMMM;@"); TEXT(mayo; "[$-de-DE]MMMM;@"); TEXT(june; "[$-de-DE]MMMM;@"); TEXT(july; "[$-de-DE]MMMM;@"); TEXT(august; "[$-de-DE]MMMM;@"); TEXT(september; "[$-de-DE]MMMM;@"); TEXT(october; "[$-de-DE]MMMM;@"); TEXT(november; "[$-de-DE]MMMM;@"); TEXT(december; "[$-de-DE]MMMM;@"); "Total" ); comt4b; "Vertical header-col, that will later be displayed on the left of the table"; header_col; VSTACK( TEXT(year; "[$-de-DE]JJJJ;@"); catg1; catg2; catg3; catg4; catg5; catg6; catg7; catg8 ); comt6; "Data for each cell"; comt6a; "Defining function to determin the value for each cell"; func_filter_elements; LAMBDA( table; month; catg; LET( data_month; FILTER( table; (YEAR(INDEX(table;; 1)) = YEAR(month)) * (MONTH(INDEX(table;; 1)) = MONTH(month)) ); comt1; "Finden aller Datensätze mit einer bestimmten Kategorie im jeweiligen Datumsbereich"; filter; FILTER( data_month; INDEX(data_month;; 3) = catg; ); comt2; "Davon auswählen der WERT-Spalte"; choosecols; CHOOSECOLS( filter; 2 ); comt3; "Summe aller Werte dieser Spalte bilden"; sum; sum( choosecols ); comt4; "Wenn es in einem jeweiligen Datumbereich keine Werte gibt -also nicht nur das FILTER-Ergebnis leer ist sondern schon das Array, das durchsucht werden soll leer ist- resultiert die FILTER-Operation in einem #CALC-Error"; comt4a; "#CALC-Error handling"; comt4b; "Wenn kein Fehler -> sum"; comt4c; "Wenn Fehler -> Prüft, ob Fehler = #CALC ist (weil die Filter-Operationen keine Werte geliefert haben)"; comt4d; "Wenn Fehler = #Calc -> Ausgabe leeres Feld"; comt4e; "Wenn anderer Fehler -> Ausgabe sum (was dann den Fehler ausgibt)"; handling; IFERROR( sum; IF( ERROR.TYPE(sum) = 14; 0; sum ) ); formatting; TEXT(handling; "0,00 ;[Red]-0,00 "); handling ) ); comt7; "Making rows by calling individual elements"; func_row_cats; LAMBDA( catg; HSTACK( func_filter_elements(source_table; january; catg); func_filter_elements(source_table; february; catg); func_filter_elements(source_table; march; catg); func_filter_elements(source_table; april; catg); func_filter_elements(source_table; mayo; catg); func_filter_elements(source_table; june; catg); func_filter_elements(source_table; july; catg); func_filter_elements(source_table; august; catg); func_filter_elements(source_table; september; catg); func_filter_elements(source_table; october; catg); func_filter_elements(source_table; november; catg); func_filter_elements(source_table; december; catg) ) ); row_cat1; func_row_cats(catg1); row_cat2; func_row_cats(catg2); row_cat3; func_row_cats(catg3); row_cat4; func_row_cats(catg4); row_cat5; func_row_cats(catg5); row_cat6; func_row_cats(catg6); comt8; "Combining values for every month-category combination to an array"; comt8a; "Array only consists of number values - no headers, no totals"; data1; VSTACK( row_cat1; row_cat2; row_cat3; row_cat4; row_cat5; row_cat6 ); comt9; "Total-column that will be displayed on the far right of the table"; comt9a; "Consists only of sixs rows - thus no total-row-values included"; total_col; VSTACK( SUM(CHOOSEROWS(data1; 1)); SUM(CHOOSEROWS(data1; 2)); SUM(CHOOSEROWS(data1; 3)); SUM(CHOOSEROWS(data1; 4)); SUM(CHOOSEROWS(data1; 5)); SUM(CHOOSEROWS(data1; 6)) ); data2; HSTACK( data1; total_col ); comt10; "Total-row"; total_row; HSTACK( SUM(CHOOSECOLS(data2; 1)); SUM(CHOOSECOLS(data2; 2)); SUM(CHOOSECOLS(data2; 3)); SUM(CHOOSECOLS(data2; 4)); SUM(CHOOSECOLS(data2; 5)); SUM(CHOOSECOLS(data2; 6)); SUM(CHOOSECOLS(data2; 7)); SUM(CHOOSECOLS(data2; 8)); SUM(CHOOSECOLS(data2; 9)); SUM(CHOOSECOLS(data2; 10)); SUM(CHOOSECOLS(data2; 11)); SUM(CHOOSECOLS(data2; 12)); SUM(CHOOSECOLS(data2; 13)) ); data3; VSTACK( data2; total_row ); comt11; "Row total excl Inv"; func_total_inv_row; LAMBDA( col_nr; SUM(CHOOSEROWS(CHOOSECOLS(data3; col_nr); 7) - CHOOSEROWS(CHOOSECOLS(data3; col_nr); 2)) ); total_inv_row; HSTACK( func_total_inv_row(1); func_total_inv_row(2); func_total_inv_row(3); func_total_inv_row(4); func_total_inv_row(5); func_total_inv_row(6); func_total_inv_row(7); func_total_inv_row(8); func_total_inv_row(9); func_total_inv_row(10); func_total_inv_row(11); func_total_inv_row(12); func_total_inv_row(13) ); data4; VSTACK( data3; total_inv_row ); comt12; "Appending header row and header column"; data5; VSTACK( header_row; data4 ); data6; HSTACK( header_col; data5 ); result; data6; result) 
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Is there a way to reference an array of arrays?Is there a way to get Excel to use an array of arrays in in a formula? Instead of writing a formula that references an array and dragging it down through a column, can I write the formula in a way that results in an output of SUM(O2#),SUM(O3#),SUM(O4#),SUM(O5#),...,SUM(O1000#)}? I work with enormous data sets, but they vary in size. Since I need the workbooks to work with any amount of data, I end up dragging formulas down extra far, leaving rows that just output empyy values when the data doesn't reach that far. If I can make the formula reference an array of arrays then I could eliminate all those garbage rows. Thank you. Unfortunately, because a security restrictions, I can't use macros or VBA at all. I need to do it everything through standard excel syntax. EDIT: Sorry, I didn't make it clear that I used SUM() as an example to try and simplify and generalize. Here's a more explicit example of what I'm doing: ID Data Col C Col D A 14 =UNIQUE(ID#) =IF($C1="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C2,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) A 23 =IF($C2="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C2)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) A 42 =IF($C3="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C3,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 1 =IF($C4="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C4,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 2 =IF($C5="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C5,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B =IF($C6="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C6,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B =IF($C7="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C7,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 90 =IF($C8="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C8,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 94 =IF($C9="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C9,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) =IF($C10="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C10)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) ... =IF($C1000="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C1000,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) Output: ID Data Col C Col D Col E Col F F A 14 A 14 23 42 A 23 B 1 2 90 94 A 42 "" B 1 "" B 2 "" B "" B "" B 90 "" B 94 "" "" ... "" There are a lot of data manipulations omitted, so if something seems weird (like the empty cells in Data Col) please ignore it. The questions is how to eliminate the unneeded rows in Col D. Thank you. EDIT2: Studying the BYROW, SCAN, and MAP suggestions, led to this page on Exceljet, https://exceljet.net/glossary/array-of-arrays , that suggests my question is a known problem in Excel's engine and there is no work-around. Thank you to everyone who tried to help. submitted by /u/TwitchyDingo [link] [comments]
- Sumifs formula based on rows of datesI am looking for help with a formula for my monthly budget. Below is a screenshot to help explain what I'm looking for. I have columns for the type of expense (column A), category (column B) and details per category (column C). I for each of those I have column J, which is the single amount to pay per bill and columns K and L for the frequency per year and the 1st date. From this, I created a sequence formula to get the list of dates for the year, which is listed on the row along the next few columns. Here's where things get dicey for me. I want to sumifs all the car payments for the month of Feb. But I don't know how to combine that with searching the row for the #of feb dates and then multiplying that by the single payment, per row, that will then get sumif-ed into a single amount for Feb Car. Let me know if this is even possible or if this is too big a problem and I'll have to reorganize my budget. https://preview.redd.it/2lwkwxdfx9lg1.png?width=1873&format=png&auto=webp&s=63dacd71676ee443972a2e70805c73529069a967 submitted by /u/ApprehensiveDuck4379 [link] [comments]
- Summarise 2D Dynamic Array?Using Excel 365 on Windows 11 This should be simple, but I haven’t manged to find a way to do it (and Copilot has just generated a bunch of #REF! and #CALC! errors…) I’m trying to do some basic forecasting over multiple years, but using dynamic ranges so the start and end dates, and the number of categories forecast can update automatically. I can get to a dynamic forecast by month, but am drawing a blank when I try to summarise that by year. A simplified version of the sheet currently looks like this: https://preview.redd.it/li08zsp8w7rg1.png?width=584&format=png&auto=webp&s=2d61ac0c1d887bb3b18bb3f08a9542fbc72f439b Data entry in B4:E10 Months dynamic range (pink) calculated in C14 =EOMONTH(D2,SEQUENCE(1,F2,0,1)) and spills C14 to AL14, or wherever the last month is. Categories dynamic range (pale blue) calculated in B15 =FILTER(B4:.B10,B4:.B10<>"")) and spills B15 to B20 here. Years dynamic range (peach) is just a helper row =YEAR(C14#). It might not even be necessary other than visually. The Forecast dynamic range (green) is then =XLOOKUP($B15#,$B$4:.$B$10,$C$4:.$C$10,0)*(C14#>=XLOOKUP($B15#,$B$4:.$B$10,$D$4:.$D$10,0))*(C14#<=XLOOKUP($B15#,$B$4:.$B$10,$E$4:.$E$10,0)) All I want to do is put one formula in C25 to calculate the blue dynamic range total by year for each category. The years are a dynamic range (UNIQUE of C14 above) and the categories are just B15#. This it turns out is completely beyond me - I can calculate each row individually using SUMIFS quite easily, but cannot persuade it to calculate one SUMIF for each line using one formula Anyone got a good way to deal with this? Thanks. submitted by /u/sprainedmind [link] [comments]