Forum Widgets
Latest Discussions
Potential Bug: SEQUENCE Function Misbehaves with Dynamic start Parameter from BYROW/LAMBDA
Dear Microsoft Excel Team, I’m encountering unexpected behavior when using SEQUENCE inside a BYROW/LAMBDA construction, specifically when the start parameter of SEQUENCE is derived from the lambda variable. Environment: Microsoft® Excel® 适用于 Microsoft 365MSO (版本 2510 Build 16.0.19328.20190) 64 位 Steps to Reproduce: The following formula works correctly and returns two identical rows: =BYROW({4;3}, LAMBDA(p, LET(n, 4, TEXTJOIN(" ",, SEQUENCE(5,,1,n))) )) Output: 1 5 9 13 17 1 5 9 13 17 However, when replacing the literal 1 with a variable derived from p (even after forcing numeric conversion), the output becomes incorrect: =BYROW({4;3}, LAMBDA(p, LET(n, 4, k, p + 0, TEXTJOIN(" ",, SEQUENCE(5,,k,n))) )) Actual Output: 4 3 Expected Output: 4 8 12 16 20 3 7 11 15 19 This suggests that SEQUENCE is not correctly interpreting k (which should be 4 and 3) as the starting value. Instead, it appears to output the value of k itself as a scalar string. Based on community reports, this may be related to the fact that BYROW always passes each row as an array—even for single-cell rows—and the value is not automatically unwrapped to a true scalar . While p + 0 should coerce to a number, SEQUENCE’s start parameter may not be handling 1×1 arrays correctly. Could you please clarify if this is intended behavior or a bug? If it’s by design, is there a reliable workaround to extract a true scalar from the LAMBDA parameter for use in SEQUENCE? Best regards, MapalerSolved枫谷剑仙Nov 14, 2025Copper Contributor56Views0likes2Comments
Excel formula
How to revise the formula to become a string to calculate if the review year is not 36 months but can be 36 months or 48 months. The Original formula in column C is EOMonth(b3,36) column A column B Column C Document name Release date Next review date in 3 year time (mth-yyyy) Document management 1/8/2025 Aug 2028Solvedunique369Nov 10, 2025Copper Contributor109Views0likes6Commentsformula in excel
Dear Experts, i want to insert a formula in column C to auto calculate the next review date as shown. column A column B Column C Document name Release date Next review date in 3 year time (mth-yyyy) Document management 1/8/2025 Aug 2028 Question 1 I use formula using EOMonth(b3,36), it show 1/8/2028 and I dont know how to fix it to show only Aug 2028 ? Question 2 my column B format cell is Date format. What date format should i set in column C ?Solvedunique369Nov 10, 2025Copper Contributor59Views0likes5Comments
Nesting SortBy and Filter functions
Can someone suggest where I've gone wrong with this syntax please: =SORTBY(FILTER(Worklist,List!I2:I500=0),List!A2:A500,1,List!G2:G500,1,List!E2:E500,1) Currently returning the #VALUE! error. "Worklist" is a table on worksheet "List". All I am trying to do is to filter out all of the zero values in column 'I' and then sort the result by columns 'A', 'G' and 'E' I'm sure I'm doing something stupid! Many thanks.SolvedTerryBennettNov 08, 2025Copper Contributor72Views0likes6Comments
Conditional formatting g for all rows
I am trying to highlight lowest price on each row for the entire sheet. here is my data table Is there a way to apply the conditional format to the whole sheet so each row highlights the lowest price. thank youSolvedAp92Nov 08, 2025Copper Contributor35Views0likes1CommentScript changing dates to text
I"m trying to make a script to transform a column with dates to text. Normally i would just use the TEXT function but the issue is that the new text values need to replace the old date values. The dates are now in three columns with the number notation date and it needs to be a text with the form "dd/mm/yyyy". Since i need to do this for at least 50 files i tought about using a script to make a new column & use the text funcion to get the new values for all three columns Change the date notation on the new values to text copy the new text values to the old column Delete the new column. The issue i meet is at the second stap that the values are in a table and are not all the same length. At the moment i have this for first step in a script function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Insert at range E:E on selectedSheet, move existing cells right selectedSheet.getRange("E:E").insert(ExcelScript.InsertShiftDirection.right); // Set range E2 on selectedSheet selectedSheet.getRange("E2").setFormula("=TEXT([@Geboortedatum],\"dd/mm/jjjj\")"); // Insert at range Q:Q on selectedSheet, move existing cells right selectedSheet.getRange("Q:Q").insert(ExcelScript.InsertShiftDirection.right); // Set range Q2 on selectedSheet selectedSheet.getRange("Q2").setFormula("=TEXT([@[Datum Resultaat]],\"dd/mm/jjjj\")"); // Set range S2 on selectedSheet selectedSheet.getRange("S2").setFormula("=TEXT([@Startdatum],\"dd/mm/jjjj\")"); } And as second step function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Set format for range E:E on selectedSheet selectedSheet.getRange("E:E").setNumberFormatLocal("@"); // Set format for range Q:Q on selectedSheet selectedSheet.getRange("Q:Q").setNumberFormatLocal("@"); // Set format for range S:S on selectedSheet selectedSheet.getRange("S:S").setNumberFormatLocal("@"); } But i'm stuck at the third step. If there is an easier method that would be fine toSolveddylanvanNov 07, 2025Copper Contributor91Views0likes5Comments
Resources
Tags
- excel43,357 Topics
- Formulas and Functions25,139 Topics
- Macros and VBA6,508 Topics
- office 3656,213 Topics
- Excel on Mac2,696 Topics
- BI & Data Analysis2,440 Topics
- Excel for web1,975 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,676 Topics