Recent DiscussionsMost RecentNewest TopicsMost LikesSolutionsTagged:TagRe: Named ranges with certain formulas don't return correct result PeterBartholomew1 Thanks for looping me in here. Engineering have completed an investigation, and this is a long-standing bug. It's difficult to see any scenarios where the current behavior would be preferred as it's effectively truncating the users array, so we plan to take up a fix. Being a calc fix, we'll roll this out very slowly to ensure there are no unforeseen issues. Re: A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays. SCAN iterates through the provided array argument in row-major order and returns its output in the same shape as the array argument. So this behavior is expected, array-of-array are not encountered as each iteration of SCAN only returns a scalar. Re: A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays. 1x1 and scalars are distinct in Excel. =TYPE(1) returns 1. =TYPE({1}) returns 64. The Arrays of Arrays limitation is a long standing limitation, it doesn't serve a purpose per se - its just something that was never supported even in the CSE days. Now that arrays are more prominent, the limitation is more apparent. Changing this behavior has backcompat implications, so anything we do here in future needs careful engineering and consideration. It also needs to be weighed and prioritized against other customer asks. Re: Ways of performing Accumulation with Dynamic Arrays PeterBartholomew1 This has been a great thread! Thanks to all the contributors. We just announced a new wave of lambda functions that I think will greatly help these scenarios. In particular, REDUCE, SCAN, BYROW and BYCOL. Re: Using dynamic arrays to populate named ranges for ListBox Controls is unstable - user error?.That does look rather odd. This is the first report I've had of this so, unfortunately, I don't have any quick answers. Would you mind crafting a simple workbook that reproduces the issue that you could share?Re: Excel Filter function not working in M365 IngeborgHawighorst Spot on. Dynamic Arrays and the supporting function are available to all subscription users. Re: How old is VLOOKUP? ExcelTL It's actually a little older, that's when it arrived in Excel. I believe VLOOKUP first appeared in Lotus 1-2-3 which predated Excel. According to Wikipedia, Lotus 1-2-3 launched in January 1983, so VLOOKUP appeared sometime around then. VLOOKUP was actually the successor to the original lookup style function, LOOKUP(), which first appeared in VisiCalc in 1979. LOOKUP was created to help with Bob Frankston's tax return. You can read about it here. LOOKUP and VLOOKUP have an amazing history, dating all the way back to the very early days of spreadsheeting. Hats off to the early spreadsheet pioneers. Re: Dynamics Arrays & Array Formulas in Excel Online Myles Gallagher There will be no delay on Excel Online getting Dynamic Arrays. When ready for General Availability, Dynamic Arrays will go to all endpoints at roughly the same time. Re: #SPILL! Error when doing VLOOKUP? Hi Jennifer Corcoran , This is likely because your VLOOKUP is looking up multiple values in the first argument (the red text below). =VLOOKUP(A:A,B:B,1,FALSE) By looking up A:A you are asking Excel to lookup a million cells. In the past this worked because Excel didn't know how to lookup multiple values, so it threw all but one away. This behaviour was called "implicit intersection". With the introduction of Dynamic Arrays, Excel now supports looking up multiple values and no longer does implicit intersection silently. If there isn't enough space to return the values you will see the #SPILL error. To modify your formula to return just a single value, you can use one of the following techniques: 1. Modify you formula to use the new implicit intersection operator @ to select one lookup value. =VLOOKUP(@A:A,B:B,1,FALSE) 2. Simply reference a single cell and copy down. =VLOOKUP(A2,B:B,1,FALSE) Both work but my preferred option is 2 as it is the simplest. Regards, Joe [Excel Team] Re: Dynamic-aware Excel incorrectly flagging our custom XLL functions as being array functions? Hi GabrielMichaud, As UDF's are able to return arrays or ranges, Excel must save it as a CSE array for Pre-DA Excel to ensure it calculates the same way. The only exception is where it is consumed by a function that expects arrays or it is implicitly intersected using @. So for instance, you can enter =SUM(MYUDF()) or =@MyUDF() in DA Excel and it will not be array entered in Pre-DA Excel. For a full list of functions that can return an array visit this page: https://support.office.com/en-us/article/excel-functions-that-return-ranges-or-arrays-7d1970e2-cbaa-4279-b59c-b9dd3900fc69 We recently fixed the RTD array disconnect issue and have released it to insiders for testing. Please let me know if it has addressed the disconnect issues. Regards, Joe Excel Program Manager Re: convert from single column to multiple columns Assuming your data starts in A1. Place the following formula into B1: =OFFSET($A$1,(ROW(A1)-1)*4+COLUMN(B1)-2,0) And copy down and across. Re: Find the smallest value other than zero A slightly easier variation of Mohammed's formula for users without MINIFS. Like Mohammed's formula, this one also needs to be array entered. =MIN(IF(A2:A10*1=0,"",A2:A10))
Recent Blog ArticlesNewest TopicsMost LikesTagged:TagAnnouncing TRIMRANGE and accompanying trim references We are excited to announce a new function, TRIMRANGE, and a set of new reference operators. New aggregation functions: GROUPBY and PIVOTBY The new GROUPBY and PIVOTBY functions allow you to perform data aggregations using just a formula. Stale Value Formatting Stale Formatting is a significant improvement to one of spreadsheeting's oldest features - Manual calculation mode. Announcing New Text and Array Functions We are excited to announce fourteen new Excel functions that will allow you to easily manipulate text and arrays. Workbook Link support coming to Excel for the web We are excited to announce that Workbook Link support is rolling out to Excel for the web. This functionality that was previously only available in Excel for Windows and Excel for Mac. Announcing XLOOKUP We are excited to announce XLOOKUP, the successor to the iconic VLOOKUP and HLOOKUP functions. Excel Dynamic Array Improvements Since announcing dynamic arrays, we’ve received an overwhelmingly positive response from the community. Being a fundamental change to the way formulas work, we wanted to take some time to gather and ...Preview of Dynamic Arrays in Excel With dynamic arrays, you can write a single formula and get an array of values returned. One formula, many values. This will allow you to build more capable spreadsheets, faster, with fewer formulas ...
MicrosoftMicrosoftMicrosoftMicrosoft
