Does a LET variable get computed even if it's only referenced inside IFNA's fallback argument?
In the formula below, expensiveMatch is defined in LET but only referenced as IFNA's fallback argument: =LET( matchRow, XMATCH(1, (A1=Sheet2!$A$1:$A$1000)*(B1=Sheet2!$B$1:$B$1000)), x, INDEX(Sheet2!$C$1:$C$1000, matchRow), expensiveMatch, INDEX(Sheet3!$C$1:$C$500, XMATCH(A1&B1, Sheet3!$A$1:$A$500&Sheet3!$B$1:$B$500)), IFNA(x, expensiveMatch) ) Question: When x resolves successfully (no #N/A), does expensiveMatch still get computed because it's a top-level LET variable - or does Excel skip it since IFNA's fallback argument is never reached? I know IFNA short-circuits its fallback argument, and LET avoids recomputing a variable if referenced multiple times - but I haven't found much documentation on the behavior of Let. Is the below a more efficient way to write the formula? =LET( matchRow, XMATCH(1, (A1=Sheet2!$A$1:$A$1000)*(B1=Sheet2!$B$1:$B$1000)), x, INDEX(Sheet2!$C$1:$C$1000, matchRow), IF(ISNA(x), INDEX(Sheet3!$C$1:$C$500, XMATCH(A1&B1, Sheet3!$A$1:$A$500&Sheet3!$B$1:$B$500)), x ) ) Is this restructuring necessary, or does the first version already skip the unused computation? Basically I'm wondering if Let computes lazily/defers calculation in the way SQL's optimizer works. Input from anyone with insight or knowledge into the calc engine would be greatly appreciated. Thanks (Currently on Microsoft Excel for Microsoft 365 MSO (Version 2607 Build 16.0.20228.20190) 64-bit)copy and paste a range of cells and keep formula eferences
My goal is to copy a range of cells and have all the formulas in the copied range of cells pasted into a new location keeping the original formula references. I.e, I will have two identical data in two sets of cells on a sheet. E.g. one of the cells in the range to be copied might have a formula similar to, "='sheet 2'!K75". I wish for that formula to be pasted into the destination cells and be "='sheet 2'!K75".Braille Translator Challenge
Using the Braille Alphabet lookup table provided: Part 1 - convert the given phrase from Braille to words. Part 2 - convert the given phrase from words to Braille. ✅ Solutions should be in the form of a single-cell formula. ✅ Generalized Lambda functions are welcome, provided the function definitions are included with your solution. ✅ Keep in mind, the "best" solution is not necessarily the shortest... Strive for conciseness, if you wish; just don't let your solutions be ordinary. 😉 Good luck and have fun!Welcome to the Excel Community
The Excel Community is a place we've built for all of you. You can learn more about how to do something with Excel, discuss your work, and connect with experts that build and use the product. With over half a billion Excel customers, we want to engage with you in fundamentally different ways and the community is a starting point for that. Our community helps answer your product questions with responses from other knowledgeable community members. We love hearing feedback and feature requests from you which helps us build the best version of Excel ever. If you have found an outage or a bug please post at our Answers forum. We look forward to getting to know you! Sangeeta Mudnal & Olaf Hubel on behalf of the Excel Team
Missing Stock Jio Financial Services in Excel Stocks Data Type
Hi! The stock Jio Financial Services Limited listed on Mon 25-Aug-23 (NSE symbol: JIOFIN). But this is missing in Excel. When I enter JIOFIN.XNSE and try to convert to a Stocks Data type, it says not found. Kindly update your Stocks database. VSUsing tables, lists, and dropdowns and not sure how to make this work…
Hi! I have an Excel workbook with several worksheets that need to be filled in by the user. The first worksheet (Sheet A) has fields for employee names/addresses/id#s, their work classification, their tax filing status, their gross pay, the various taxes that come out, etc. There is also a worksheet titled "Data Sheet" with a table ("Table A") that has the employee names/addresses/id#'s. When filling in "Sheet A", the user can select from a dropdown list of those employee names/address/id#'s (the list comes from "Data Sheet - Table A") and the employee's info fills into that field. These are the steps I followed to set up the "Data Sheet" and the dropdown list on "Sheet A". 1. Added a worksheet to the end and named it Data Sheet. 2. To add a table, made a list of employee names with a header name and then selected an item in the list and Insert>Table, named the table (no spaces) in the Table Name box, and made sure that the header row is checked. 3. Next, I defined the list. Clicked on an item in the list, then under Formulas, selected Define Name. 4. Gave the list a name different than the table name or head name. In the reference section, highlighted all the items, but not the header. This allows the list to be added to without having to change the range. 5. On "Sheet A", I selected the cells I want to use the drop-down for. Selected Data Validation on the Data tab. Selected allow “list”, then in the source box hit F3 so that the lists I've made would pop up and selected the correct one. 6. Made sure that the column where users will input their info is unprotected. I've since set up a second table ("Table B") on "Data Sheet" and defined the list, etc., to be the employee fica rate. On “Sheet A” the fica withheld from the employee's paycheck goes into the field next to the employee name/address/id# field. When the user selects an employee name/address/id# from the dropdown list on “Sheet A”, is it possible to make the corresponding fica field auto-calculate from “Data Sheet” at the same time? The list/table (“Data Sheet – Table B”) would need to be the percentages that each employee pays and then “Sheet A” would need to pull the corresponding percentage from “Data Sheet – Table B” and multiply it with the wages the user has entered and the result of that calculation would land in the “fica” field. Thank you so much!! DianeUsing Formulas (IFERROR?)
I am trying to figure out a way to use the same formula (or combined formulas we can use in each) that will return the % of change as either -100% if column B is 0 or 100% if column A is 0 but B is not. The formula we are using right now is IFERROR((-(E152-F152)/E152),"0.0"%) This returns any columns with errors as 0, but it also is returning the column that should be 100% as a 0. Is there another formula I can combine or a better one to use that will do this? 2026 Budgeted 2027 Proposed % of Change 6,565,464 - -100.0% - 140,388 0.0% - 50 0.0%
Sum of data across multiple columns & rows based on Criteria
I have a data set that ranges across multiple rows and columns. I am building a formula and looking to sum based on multiple criteria. I tried sumifs, however, that does not work. Below is my example using a hypothetical sumifs (keeping in mind, this does not work). Any suggestions on how to return my expected result would be greatly appreciated. =SUMIFS('Query Budget'!$C$10:$HV$1000,'Query Budget'!$A$10:$A$1000,F$1,'Query Budget'!$A$5:$HV$5,$D5)
