lambda

  1. LearningByDoing

    LAMBDA function and its conversion options for locals / regions

    Hello My questions relate to the LAMBDA function and its conversion options in relation to the locales / regions. 1. the creation of a LAMBDA according to the English locale (comma instead of semicolon, for lists { , } instead of { \ } etc.) causes problems when used in the European locale. For...
  2. J

    Need to refer to previous rows and columns in spill range

    Below is my spill range formula. Variable "result" in the LET is populating the cells. I am struggling to define variables prevAmtRows and PrevAmtColumns which are assigned a value of 999. What l need PrevAmtRows to do is to calculate the values returned by result in the previous rows of...
  3. staticfluids

    Dynamic Array Formula (FILTER) But Show the Other Criteria with Blank

    Hi, first of all, sorry for my bad English. I have been looking for a solution here, trial and error by myself, and the Microsoft forum as well. But haven't found any solution that satisfies the needs. I think it's possible but I don't know how to put it into Excel formula. The logic is like...
  4. E

    Convert Excel Formula to DAX Which Averages Last 2 Values Until Certain Period of Time

    I have the following table in Excel: DATE VALUE 1/1/2024 100.00 2/1/2024 150.00 3/1/2024 200.00 4/1/2024 5/1/2024 6/1/2024 7/1/2024 8/1/2024 9/1/2024 10/1/2024 11/1/2024 12/1/2024 Lets say this table is called Table1. For the Months 4/1/2024 - 12/1/2024...
  5. M

    A slicer or dropdown to return values where the selected value is found within

    Hi Everyone, I have been doing my research and have brought a table close to where it needs to be in power query. The last thing I need to know is if the following is possible. I am struggling to find a solution to this: Assumption: These distributor names need to remain concatenated...
  6. J

    MARKETCAL2

    MARKETCAL2 will generate the days the market is closed for the year requested or the current year if that option is omitted, and is designed to work in conjunction with the MKTDAYS Lambda function to build an array of dates the market is open. It will generate a two column array with the name of...
  7. M

    There has to be a better way...

    For weeks I've been trying to find a better way to make a Wind Rose Chart or a Spiral Bar Chart to keep track of my Achievements in Steam. I tried to ask AI-aided Formula Editor but it's either not working or I'm not asking the right questions. I'm trying to find a better way to write the...
  8. J

    MKTDAYS

    MKTDAYS is the second of two posts intended to alleviate the problem with the STOCKHISTORY function where it returns an error when attempting to provide information for an invalid date as reported by @DRSteele . The first post was MARKETCAL which can be used to provide the required array of...
  9. S

    Dynamic Average of top values in specific ranges

    Hi everyone. Im trying to find the average of the top values of several- non continued ranges. see image for reference: What I need is being able to average the TOP value of range B4:B15 with the TOP (the ones in green) value of B17:28 and so on for the next following cells that are not in...
  10. Jeffrey Mahoney

    TEXTSPLITVAR

    TextSplitVar is a recursive formula to split a single cell or text array by the delimiter and return the variable chosen. It checks the number of variables and provides the last variable if the user asked for too large a number. It also adds one more delimiter at the end on the first iteration...
  11. J

    CELLWIDTH

    CELLWIDTH returns the cell width of the optionally provided Cell Reference. =LAMBDA([Cell],IF(ISOMITTED(Cell),INDEX(CELL("width"),1),INDEX(CELL("width",Cell),1))) It returns ONLY the cell width, not the Spilled Array CELL("width") now returns. Note that the value returned is the width of the...
  12. R

    Excel Lambda Function returns #VALUE and will not SUMPRODUCT

    I have a lambda function which will not let itself be included in a SUMPRODUCT depending on the YEARS comparison. All other parts of the formula are working but stick the LAMBDA function result in there and it just won't have it... Why ??? Row 5 in blue are figures as a result of the lambda...
  13. J

    Enabling Excel 4.0 macros and their use in a LAMBDA function

    I recently found the 2 year old video Excel Formula to List All Sheet Tab Names and include Hyperlinks which included a sample file. The solution created a Named Range called SheetNames that used the formula =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")&T(NOW()) However, both the...
  14. J

    GSHEETS

    GSHEETS(SURL) - Given the "Anyone with the link" Share URL from Google Sheets (SURL), provides URL to use in a Web Power Query to retrieve data from the Google Sheet file. =LAMBDA(SURL, LET(base,"https://docs.google.com/spreadsheets/d/", tail...
  15. J

    LAMBDA Function to create a dynamic array from data whose rows grow (or shrink) - _FredRange

    A while back I discovered an Excel Add-In called FRED Data provided by the Federal Reserve Bank of St. Louis. What it does is allow you to retrieve data from various Federal agencies such as the Consumer Price Index from the U.S. Bureau of Labor Statistics. The problem is that the data is...
  16. A

    When will LAMBDA function be available for the Semi-Annual Enterprise Channel?

    Hi all, I would like to know if anyone has any knowledge about the release date of LAMBDA functions for the Semi-Annual Enterprise Channel? It has already been release for the Current and Monthly Enterprise Channels but not mention to my particular channel. Please take into account that I am...
  17. shaowu459

    Excel Formula: An easy way to get all combinations of items in different columns

    Using REDUCE function in Office 365 we can get combinations of items in different columns very easily. =REDUCE(A1:A4,B1:D1,LAMBDA(x,y,TOCOL(x&"-"&TOROW(OFFSET(y,,,99),1))))...
  18. A

    Calculating Exponential Moving Average in one cell (Scan Function?)

    Hi all, I am trying to figure out how to calculate the Exponential moving average of a stock. The sheet shows the steps involved. I tried using the scan function but I couldn't figure it out. I hope someone smarter than me can help with the formula or point me in the right direction. I know I...
  19. A

    Why does non-recursive LAMBDA/LET slow things down?

    Hi all! Hopeful that you can help me speed up some of my formulas. I'm building a chain of LAMBDA functions and the most recent one is running really slowly and I can't figure out why because if I run the exact same formula just filling in the variables, it runs instantly. Details below: Runs...
  20. R

    Lambda Function that works in a TABLE

    Good evening, hope someone can help. I am trying to create lambda functions that work in various tables in a workbook. The worksheet calculates "Year To Date" (a running total) and "Previous Year to Date" (YTD from the previous row) The functions are kind of long (especially the Previous...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top