Use Ctrl-Shift-Z to redo actions
Does anybody know how to make it so I can use Ctrl-Shift-Z to perform the redo action in Excel? It was... doable in Word, but Excel is proving to be a much more difficult challenge. I've tried working with macros to get the action done, but I can't seem to figure out how to get it to do what I want at all. Any solution is more than welcome! It's pretty nuts that in 2022 you still can't edit keyboard shortcuts in Excel, so if I'm missing an obvious solution, please let me know!
AVERAGE reduced lambda on GROUPBY or BYROW formulas not working on specific workbook.
Currently using excel with spanish formulas. When using GROUPBY formula, i can use SUM, PRODUCT, LAMBDA, however AVERAGE (PROMEDIO) won't work on a specific workbook which previously has a lot of GROUPBYs that work properly. When I update the previously working formula, it now returns NAME error and PROMEDIO turns into Promedio. Inserting the function looks like this: I have no custom name called Promedio. This same GROUPBY formula works in different workbooks. Help would be appreciated. Best regards
Autocomplete not working
I have Microsoft 365 Version 2512: January 13, which is the latest update. I am operating Windows 11 Home Version 25H2. When I try to use the autocomplete facility in Excel it does not work. The 'Enable autocomplete for cell values' tick box & the one below in File; Options; Advanced are both ticked. I have tried this in all existing worksheets and IMPORTANTLY in a new blank worksheet. I have asked a friend to try it on his Windows 11 laptop with the same Excel and his does not work on his either. HOWEVER, it does work on my MacBook which is operating system Monterey Ver 12.7.6 & Excel Version16.89, which is an older version. There it works in both a new blank worksheet AND also if I copy the relevant file and paste into the Apple iOS system. I have posted questions on Q&A but have not had any response that indicates a valid reason for the problem or more importantly how to solve it. I would obviously like to speak to someone but I think this is likely to be impossible. It appears that autocomplete does not function in Excel in Windows 11? Can someone help?
Formula to compare a number as text and a partial match?
Hi, Trying to check if cell B is a match to A while also reporting any B that have a "0" missing in the front. (so a partial match?) I had used this formula. =IF(ISNUMBER(FIND(B2,A2)),TRUE,FALSE) Which tells me when there is a "no match", but I would also want the formula to let me know there is a 99% match - just missing a zero. is there a way to do this? thanks
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
Loss carry forward for limited years
Hello Excel Community, Excel Version: O365 (Enterprise) I’m running into a mental block trying to model loss carryforward with limited years using a spill formula. Below is a simplified example of what I’m trying to build a formula for. Rules: • A loss from any year can be carried forward for N years, after which it expires and can no longer be used. • In any year with a positive tax liability, if there is any remaining loss from the past N years, you deduct using a FIFO approach (oldest losses used first). • Any unclaimed balance from year N+1 expires, even if it hasn’t been fully used. I’ve found plenty of examples showing loss carryforward without expiration, but I’m struggling to build something that handles both FIFO and expiration in a dynamic array formula. I feel like this must be a solved problem and I’m just missing something obvious. Any help would be greatly appreciated! ------------------------------------------------------------------------------------------
