+ Reply to Thread
Results 1 to 6 of 6

Updating array formula from excel 2019 to use LET in excel 2021

  1. #1
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    990

    Updating array formula from excel 2019 to use LET in excel 2021

    Hi All, a couple of months back I was given help on the attached workbook, which by using array formulas solved the problem I faced. At the time I was using excel 2019 and was told by several people on here I should update to excel 2021 as things would be a lot simpler and no need to use array formulas. At the time HansDouwe gave me two formula for cells O5 & O6 on page "Records" which now I have excel 2021 I have added to the workbook and they work just fine. I'd now like to complete the grid using these formula but I just don't understand them.

    For example HansDouwe suggested this

    =LET(t,FILTER(ALL!L2:L9999,ALL!E2:E9999="H"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))

    I tried to adapt this for O4 by removing the part ALL!E2:E9999="H" which was no longer needed so

    =LET(t,FILTER(ALL!L2:L9999),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))

    but I get the response I haven't entered enough arguments for this formula.

    If somebody could break down the formula for me and tell me what it is actually asking that would be great. As always any help much appreciated.
    Attached Files Attached Files
    Last edited by Marvo; 01-31-2024 at 09:36 AM.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,205

    Re: Updating array formula from excel 2019 to use LET in excel 2021

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    990

    Re: Updating array formula from excel 2019 to use LET in excel 2021

    Thanks Fluff, that works lovely. However I still don't understand in order to add it to the rest of the block. What is the t and the s referencing? Or, how can I adjust that formula to work in I4 for example?

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,205

    Re: Updating array formula from excel 2019 to use LET in excel 2021

    t & s are just variables(you can change them to whatever you want) & they store the information so that you don't have to do the same calculation all the time.
    For I4 just change the range stored in t & change the <>0 & =0 to ="L" & <>"L"

  5. #5
    Forum Contributor Marvo's Avatar
    Join Date
    01-15-2009
    Location
    Northampton, England
    MS-Off Ver
    Excel 2021
    Posts
    990

    Re: Updating array formula from excel 2019 to use LET in excel 2021

    Cheers Fluff, will do that. Thank you very much.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,205

    Re: Updating array formula from excel 2019 to use LET in excel 2021

    You're welcome & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to replace pivot table in Excel 2019
    By chuath in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-11-2023, 05:20 PM
  2. Replies: 8
    Last Post: 12-05-2022, 08:38 PM
  3. Replies: 1
    Last Post: 10-11-2022, 08:55 PM
  4. Replies: 6
    Last Post: 07-07-2020, 12:26 PM
  5. [SOLVED] Excel 2019 - Formula doesn't recognize Cell
    By 200£Gorilla in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-02-2020, 02:09 PM
  6. Excel 2019 Spreadsheet date formula help
    By dtpunshon in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 01-10-2020, 05:52 AM
  7. Replies: 3
    Last Post: 03-20-2014, 06:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1