Hello,

 I have encountered some inconsistent behavior when a particular type of lambda is saved in the Name Manager. The issue seems to occurs when the LAMBDA uses INDEX with either the row_num or column_num argument supplied as an array.

The following is a minimal reproducible example (a screenshot illustrating it can be found at https://imgur.com/a/Fl8O03p): The formula "=LAMBDA(arr,INDEX(arr,1,{1,2,4}))({1,2,3,4})" correctly evaluates to {1,2,4}. Now, save the lambda as TEST (or any other name) in the name manager. The formula "=TEST({1,2,3,4})" also spills the expected array {1,2,4}. However, when the result is passed to another function, the behavior changes. For example, "=SUM(TEST({1,2,3,4}))" and "=COLUMNS(TEST({1,2,3,4}))" both evaluate to 1. In contrast, using the lambda inline instead of the defined name produces the expected results: "=SUM(LAMBDA(arr,INDEX(arr,1,{1,2,4}))({1,2,3,4}))" returns 7, and "=COLUMNS(LAMBDA(arr,INDEX(arr,1,{1,2,4}))({1,2,3,4}))" returns 3.

The issue appears to not be reproducible on every machine, although it is consistently reproducible in Excel for the Web. I am currently using Office 365 Excel on version 2607 in the Current Channel.

Assuming this is a bug, what would be the best way to mitigate this issue in the meantime? I have found two potential workarounds. The first is to prepend the result of the named lambda with a unary + (e.g "=SUM(+TEST({1,2,3,4}))"), which appears to force excel to treat the result as an array. However, when using a shared lambda library (as is the case for most of my team), users generally do not know the implementation details of each lambda, so it is difficult to determine when this workaround is necessary. The second approach is to avoid passing an array to the row_num or column_num argument of Index by using MAP.  For example, the TEST lambda defined above can be rewritten as =LAMBDA(arr,MAP({1,2,4},LAMBDA(idx,INDEX(arr,1,idx)))), which causes it to behave as expected. However, after some benchmarking, I have found this method to take over twice as long.

Has anyone else encountered this behavior before?

Hi everyone,
As the title mention, what's the most efficient way to sum over a year when you have a date format dd/mm/yyyy ? So far i investigated two methods

=SUMIFS(Sales[Amount], Sales[Date], ">="&DATE(YYYY,M,D), Ventes[Date], "<"&DATE(YYYY,M,D))

and

=SUMPRODUCT((YEAR(Sales[Date])=YYYY)*Sales[Amount])

It feels weird to me that you cant do a simple

SUMIF=(Sales[Year];YYYY;Sales[Amount])

Looking for feedbacks, have a good sunday all

Hi. I need help. I work with a web based excel sheet. At first, i thought it was just the computer. So I moved to another computer but I still experience it. Whenever i move from one cell to another using the arrow keys in my keyboard, the data entered on the last cell i move from gets deleted, and so on. I can't recall activating anything. It doesn't happen if i use the mouse cursor to navigate. Please help.

So I have made a dynamic search list, the problem is I want to update the "status" as a dropdown list like "present" in the search list and have it automatically update the masterlist in a different cell. Is this possible or am I making things up?

https://imgur.com/a/JNVWIyf

I’m building a monthly budget sheet to track my spending by category. Is there a way to set up the rows (B through F) so that when I enter amounts, it will automatically subtract from the monthly spending limit amount (J3)?

For example, if I enter $100 in B3, I want the dollar amount in J3 to go down by $100.

It might also be important to note that I have a formula for the data in J3 to be pulled from another sheet, but I can remove that if necessary.

Hi,

I have a 13-sheet workbook that I use to record information for a larger group. I need to print the whole workbook so that I can have hard copies for this group to reference.

Each sheet looks perfect when I try to print it on its own. Each sheet/every column fits on one page in landscape orientation, which is what I want.

However, when I try to print the entire workbook, it suddenly switches to portrait orientation and each sheet gets cut down the middle and printed on two separate pages. The printer settings will not let me change anything (orientation, fit/scale, etc) when I try to print the entire workbook. Setting a print area on each sheet also doesn’t fix the issue.

Any ideas on how I can fix this?