TOCOL/TOROW Treat Thunks as Errors
I just reported a bug to Excel in which the TOROW and TOCOL functions, if asked to delete errors, will also delete valid thunks. ISERROR returns FALSE when presented with a thunk, so, arguably, TOCOL shouldn't treat it as an error either. I'm running Office 365 on Windows 11 on a Samsung Yoga laptop. Steps to reproduce: Paste this into a single cell in a spreadsheet: =LET(th, VSTACK(LAMBDA(4)), (@TOCOL(th,2))()) Expected behavior: should return 4. (Delete the ",2" and it does exactly that.) Actual behavior: #CALC error Impact: Breaks workflows for SCAN, MAP, BYCOL, and BYROW Workaround: Use FILTER and ISERROR Details: An Excel "thunk" is a degenerate LAMBDA with no parameters. So if we used LET to make f equal to LAMBDA(4), then f() would always return 4. If a thunk is the final result of a function, it generates a #CALC error, but it's fine for intermediate results. This seems useless, but it's the only way to get functions like SCAN, MAP, and BYROW/COL to return anything but a scalar. For example, I have a piece of code where I need to repeatedly square a large matrix and save the values for further processing. If I get a zero value, I can save a lot of processing by "aborting" the operation. Since you can't abort a SCAN, I just return #NA. Then I'd like to use TOCOL(result, 2) to strip off the unnecessary values. But TOCOL discards everything. I can work around this by using a combination of ISERROR and FILTER, but I shouldn't have to.Advanced Excel Formula discussion - Problem with dynamic range
Let's assume I want to rent a house. For each house, if the landlord has an agent, I'll contact the agent; If not, I contact the landlord directly. As below: Landlord Agent House Landlord James Mary W James Linda Michael X David Y Linda Z James Column F: =IFERROR(XLOOKUP(E2:E5,A:A,B:B,E2:E5),E2:E5) This is correct Column G: =XLOOKUP(E2:E5,A:A,B:B,E2:E5) #VALUE! for X-David Column H: =XLOOKUP(E2:E5,A:A,B:B,CHOOSECOLS(E2:E5,1)) Wrong value for X-David, because it returned the first value in range of XLOOKUP([if_not_found]) field My question is: the only difference between G and H is CHOOSECOLS(). If I put =CHOOSECOLS(E2:E5,1) or =E2:E5 in a separate cell, it gives me same result. However, in XLOOKUP, they are recognized differently. What is the logic behind this? Thank you for spending time on reading this.
How should I write this Process?
I am not sure how to do this in function for without going through a long chain of manual brute force steps and hope I'm in the right discussion group for this. I have hunted the help hits on my search engine, but I'm not even sure if I am asking the right question so I am getting frustrated. The picture below shows where I am now. I want to set up a Function the will roll a d100 x number of times (with individual rolls reported, not added together) with x equaling the row's Chances cell (an assigned value). The 1 and 47 are the established Red/Blue cut offs for a different cell to process (outside this question, but will take suggestions if you have them). Ideally they would display as "81, 28, 42" etc. but I am interested in the function lay out. When it comes to Functions, my knowledge is limited the very basic ones and I am using baby talk when I do use them, so please be kind. Is this even possible or I am I just being crazy/unrealistic?
Data Pulling
I'm trying to figure out the best way to pull data together from multiple tabs within an excel file. The file is made so that it can track issues within reports that are discovered. I'll try to break it down the best I can to explain what I have and what I'm trying to do. Within the file there are 5 tabs that are considered locations and they are named: LX, LV, CR, HR, FL Each tab is set up the same way with the only real important information in these columns of a table Column A - PCR Date Column C - PCR Owner Column G, I, K, M - Modification Category (1-4) these fields are actually drop down options from the Category Master List Within the file there is a 6th tab that has at able in it that is called Category Master List. Column A - Options for the dropdown in the above tab Modification Category columns Column B - Breakdown into one of three main categories (Billing, Compliance, Quality) Now this is what I'm trying to do: Fiscal Year Data (September 2025-August 2026) - I need to see how many times each of the dropdown options within the Master List show up in column G, I, K, M for each of the 5 location tabs. Monthly Data - I need to see how many times each of the dropdown options within the Master List show up in column G, I, K, M for each of the 5 location tabs along with how many times there is an entry with a date within that month. I would like to try and figure out how to put everything into one data table/chart type thing so it's easier to see and compare. Currently, everything needed is across a few different pivot tables and I would really like the data to pull itself automatically instead of me having to go in and copy all the data from one spot into another just to get the pivot results. Below is what I'm having to look at currently.
Dependent calculations in tables
Hi all, I'm using a formula to calculate amounts into a master column in budget sheets depending on the currency. I'm pulling currency rates with the stock function into a separate sheet with named cells for each rate that I'm using. My budget table has a "Total" column where I put in the amount, a "Cur" column where I select the currency, and then the master column where it's converted into the currency that I'm working with for each budget. The formula I'm currently using is this: =IFS([@Cur]="EUR";[@TOTAL];[@Cur]="NOK";nokeur*[@TOTAL];[@Cur]="SEK";sekeur*[@TOTAL];[@Cur]="GBP";gbpeur*[@TOTAL];[@Cur]="";) nokeur, sekeur, gbpeur are named cells containing the currency rates. The last argument is only to not get a REF error on empty rows. (Semicolons instead of commas due to my language settings) This has gone through a bunch of revisions over the years and I'm constantly trying to shorten the formula as much as possible. This is the shortest I've managed to get it though. I wanted to check if anyone has any advise on how to make it more elegant. the formula does exactly what I want so it's not a problem per se, it's more out of interest. Also a related topic: sometimes when others are using my sheets, they will mistakenly put values in the Master column, overwriting the formula. This is hard to spot, and ideally I'd like to lock those cells/that column so you can't replace what's there. Locking the workbook can of course achieve this but it also prevents the user from adding additional lines in the table. Is there any smart way to get around this? I've also tried with conditional formatting to clearly highlight the cells if they don't contain a formula but I haven't managed to make it work properly.
