Recent Discussions
Cumulative Sum of Each Column or Row
After studying various posts (answers) by members of this community, I developed a function that returns the cumulative sum of each column or row of an array: =LAMBDA(a,[by_row],LET( f,IF(by_row, LAMBDA(b,IF(ROWS(b)=1,b, LAMBDA(b-VSTACK(0,DROP(TAKE(b,,-1),-1)))())), LAMBDA(b,IF(COLUMNS(b)=1,b, LAMBDA(b-HSTACK(0,DROP(TAKE(b,-1),,-1)))()))), IF(by_row, LAMBDA(f(SCAN(0,a,SUM)))(), LAMBDA(f(TRANSPOSE(SCAN(0,TRANSPOSE(a),SUM))))()))) My goal is maximum efficiency. I am new to the concept of lazy evaluation, so I'm wondering if you could explain the flow in detail and whether there is a pair of sets of parentheses too much. Of course, I'm open to improvements.Excel Challenge - Pivoting poorly structured data
This is from an ExcelBI challenge. I thought it may be worth while posting my solution here as a demonstration of modern Excel methods. Challenge Like many of such challenges, the natural solution approach is to use BYROW but that creates the usual 'array of arrays' error. Solution: Gradually I am moving to a point at which I have no formulas showing in the workbook other than calls to Lambda functions. In this case, the worksheet formuloa is = PIVOTBYCATEGORYλ(OrderTbl) The function works row by row apportioning the amounts against the listed categories PIVOTBYCATEGORYλ // Groups and pivots table by category = LAMBDA(table, LET( normalised, BYROWλ(table, APPORTIONλ), // Identify fields from normalised table dimension, TAKE(DROP(normalised,,1),,2), category, TAKE(normalised,,1), partCost, TAKE(normalised,,-1), // Pivot by category return, PIVOTBY(dimension, category, partCost, SUM,,0,,0), return ) ); The function APPORTIONλ divides the amount between categories so each record within the source data returns a number of rows APPORTIONλ // Splits by category and assigns costs = LAMBDA(record, LET( category, TOCOL(REGEXEXTRACT(INDEX(record,4),"\w+",1)), amount, INDEX(record,3) / COUNTA(category), year, YEAR(INDEX(record,1)), region, IF(LEN(INDEX(record, 2)), INDEX(record, 2), "Unknown"), broadcast, B∕CASTλ(HSTACK(region, year, amount), category), return, HSTACK(category, broadcast), return ) ); /* FUNCTION NAME: B∕CASTλ DESCRIPTION: Broadcasts the terms of a vector over the shape of a second array */ B∕CASTλ = LAMBDA(vector, array, IF({1}, vector, array)); The key to making the formula work is the function BYROWλ that I wrote to generalise the inbuilt but over-restrictive BYROW function. The PIVOTBY function returned the required crosstab from the normalised data array
Top n vs. Others in Excel
Hi all, I'm seeking some help because I'm kind of new to the more intermediate stuff in Excel. I have an Excel table with the following columns: Subcategory in column A, Brand in column B, Region in column C, Year in column D and Values Month in column E. I want to create a PivotTable and a Pivot line chart from this PivotTable that ranks the Top 5 Brands vs. Other Competitors by each region. For added context: There are 5 subcategories, 3 regions and 25 brands. Currently, I've tried grouping the remaining 20 brands as "Other Competitors" vs. the Top 5 brands within a selected region and possibly all regions (when no selection is made). I'm seeking a solution similar to this... Please mind the colours. I will sort those out later. But, the problem that I'm faced with is that upon selection of a region, the PivotTable won't update to the Top 5 brands of a selected region because they've already been grouped. How can I make this more dynamic so that I'm able to show The Top 5 brands vs. Others? Please help. EDIT: My operating system is Windows 10 (64-bit) and I use Excel 365 (Desktop version). For reference, I've attached a link to a sample file. https://1drv.ms/x/c/b2d878e32a062614/IQC1wcnwLICcQasOfnGcwKn0ASjpXp9xQ6rjnOP10Jal5cc?e=HaXEWd Thank you all once again.
PLEASE HELP ME. Excel Time Formatting Not Working?
PLEASE HELP ME!!! I created an excel workbook for tracking fitness workouts, I record the data such as weight lifted, number of reps, and rest times between sets. I formatted the cells so they express your rest times in terms of minutes and seconds (I used the custom "H:MM" format of time option). But whenever I record the amount of time you spend during cardio exercise, the formatting gets COMPLETELY SCREWED UP because when I type 28:44 (28 minutes and 44 seconds) of cardio duration, it instantly turns into 4:44 (4 minutes and 44 seconds). But if I type 20:00 (20 minutes) of cardio duration, it stays as 20:00 (20 minutes) and it never turns into something else. Why do some of the numbers like 28:44 transform into a complete different number when I enter them? I uploaded 3 screenshots below to better express what I mean. 'Is there a way to work around Circular reference in Dynamic Array?
Hi! I am trying to convert a table into a dynamic array, but the rows in the table is referring to other column's previous row value, so when I try to convert it into a dynamic array, it'll become a circular reference. Is there a way to work around it? I attached the example excel file to this post for your reference. This is just a simplified example of the actual table I am trying to convert. I tried the "Iterative Calculation" option , the calculation will be correct. But the problem is, the original excel table in my case is very complicated, so even if I set the "Max calculate times" to 10,000, the calculation can not be finished, leaving at least half of the values wrong. My alternative approach is to solve this half dynamically. But I consider it ugly... Leaving only col1 dynamic, and other columns will have to fill down the formula to "row 1048576", which is not pretty. (col1 is actually referring to other places in the real case) Is there a way to work around this and make the table fully dynamic? Thank you! Best Regards, Alan
Conditional Formatting multiple conditions
Hey all! I need some help writing a formula for some conditional formatting I'm looking to do for a contact log. Basically, I want to highlight dated cells if the interaction with a contact is -14 days and -21 days from today's date. I've figured that out and am using the formula =DATEDIF($E7,$F$1,"D")>14 and =DATEDIF($E7,$F$1,"D")>21 However I also want to run conditional formatting so that the cells only highlight when the Contact info in the log is a unique value, and this is what I'm currently struggling with. For Confidentiality, I can't show the values populated in the Contact Column, but I can show an example of my table's columns if that helps. So in essence, I want the date formatting to run only if the contact value is a unique value, so that it is not flagging an old log of an interaction and only flagging the most recent interaction with that contact name if it is dated past 2-3 weeks. Is there anything I can build that will operate in this way? Thanks!STOCKHISTORY and Stock Data not working
Is it only me? Or everyone else having the same problem? My Excel 365 cannot use STOCKHISTORY Function anymore. It shows #connect! Error. Also, I cannot use the stock data anymore. (The other data types are not functioning as well.) (The error message says "Sorry, we are having a temporary server error. We are trying to fix this problem.") Is it only me? Or everyone else is having this problem?
Unbelievable mess in Excel files: rows show upside down.
What is happening here? My rows in Excel show upside down (see bellow). Sometimes it disappeared and became normal after scrolling, but this time it stays like you see on the attachment. What can I do? My MacOS is Sequoia 15.7.3 and Microsoft Excel is version 15.28 (16115).Need help creating a dynamic graph from data extracted from a pivot table
Hi experts, I have hourly data collected from our shared solar system (14 lots). I can get that data into an excel table easily, then use a pivot table to get it summarised by Date-Month.Day (rather than by hour) and Lot. A calculated column in the pivot table gives the percentage ratio of the solar power delivered each day to each lot. [Sidenote: The solar power is not delivered equally every day, but is demand based with an overall objective of eventually sharing the power equally, where equally depends on the strata lot allocations, so some lots get a different percentage than others. Furthermore, the distribution is split into 3 phases, where a given set of 4 or 5 lots share the same phase] I've added slicers to the resulting pivot so I can look at each month of data for each phase. [Note that the system went into operation on Nov 22, so the November data is only a few days, beginning Nov 22] What I'm trying to achieve is to get the data graphed to show the Ratio of Solar Delivered per day per Lot. Something like this, which is fine for Phase 1 for the month of November only: To create this graph, I used array formulas in some spare cells in the pivot table to tabulate the data like this: The table extends dynamically as I add months and/or phases to the pivot table display - which is great. Just what I wanted. BUT... the graph stays stuck on showing just the first four lots and the first 9 days because that was the size of the table when I grated the graph. I WANT THE GRAPH TO EXPAND DYNAMICALLY AS THE TABLE EXPANDS I've tried changing the Chart data range to accommodate the extra data, but if I then change back to a smaller set of data, the graph size does not change. viz- below is how the graph looks after changing the Chart data range to accommodate some extra data, then reduced to the original data set: I WANT THE GRAPH TO CONTRACT DYNAMICALLY AS THE TABLE CONTRACTS In other words, when I change the slicers to show the original data set, I want the graph to return to its original format ~------------------------------------------------------------------------------------~ I've read posts that talk about formatting your data as a table. Bit if I try and format by "helper" data as a table, I get the following warning: If I exclude the calculated headings, I get #SPILL errors ------------------------------------------------------------------------------------ I'm at a loss to work out how to create a dynamic graph. I'm hoping someone in the community can help - good luck and happy new year. And thanks for taking the effort to read this rather long post. If I can figure out how to add my source file to this post, I'll add it. In the meantime, you can view/download my source file here: https://1drv.ms/x/c/c95331b296c5ed04/IQCxxcpJWbyOTIXiDxyvmg9mAS5xcAADjTrP0JXBbs1IHBI?e=JJvVYH RedNectar
Events
You’re passionate about what you do, and we’re passionate about empowering your potential. Microsoft 365 Community Conference - is your chance to keep up with AI, build game-changing skills, and take...
In-Person
Recent Blogs
- Microsoft Copilot can help you entertain family, get in the winter spirit, and make the most of this season.Jan 21, 2026
- 4 MIN READClipchamp reached new levels of innovation and enabled increased productivity in 2025!Jan 16, 2026
