Results 1 to 3 of 3

Thread: Complex Formula was working, now failing

  1. #1
    Board Regular
    Join Date
    Sep 2017
    Location
    Houston, TX
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Complex Formula was working, now failing

    I have a complex formula that was previously working for the last 6 months.

    Now when I try to run it, it just gives me a #VALUE ! error.

    The data set over the last 6 months and now is basically the same volume.

    I haven't change the formula, the layout of the columns, tab names, nothing ....

    I tried to evaluate the formula but excel freezes and fails.

    About a week ago something happened with my Excel and it was like many of the settings were reset. Many of my macros that previously worked, now don't.

    Previously, when I would open a workbook and press ALT+F8, I could run the selected macro.
    Now, it shows the various macros, but the RUN button is not available.

    I am wondering if the Excel reset caused something to happen which is affecting my formula.

    The formula is the following:

    =IF(OR(ISBLANK(A2),G2>H2),"",SUM(--(MMULT((ROW(INDIRECT(G2&":"&H2))>=TRANSPOSE(IF(Holds!$A$2:$A$4639=A2,IF(Holds!$I$2:$I$4639="YES",Holds!$K$2:$K$4639))))*(ROW(INDIRECT(G2&":"&H2))<=TRANSPOSE(IF(Holds!$A$2:$A$4639=A2,IF(Holds!$I$2:$I$4639="YES",Holds!$L$2:$L$4639)))),Holds!$K$2:$K$4639^0)>0))+J2)

    Entered with CSE.

    Previously when I would run it, it would take like 10 mins to run on several thousand rows of data.
    The data was built as a table and so it would populate all cells in that column for all the rows in the table.

    Now, when I run it and it gives me the error, it just fills that first cell in the first row.

    I cannot for the life of me figure out why it is failing!! I have been using it for 6 months without any issues.

    Any thoughts, ideas, pointers, etc., would be greatly appreciated.


    Thanks to everyone!!

    -Spydey

  2. #2
    Board Regular
    Join Date
    Sep 2017
    Location
    Houston, TX
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex Formula was working, now failing

    Ok, I just spend two days trying to figure out the issue.

    I researched online, looked at examples, read many a post, etc.

    Literally within 1 min of me posting my plea for help, I figured it out!!!

    LOL

    In my population in the Holds tab, I had a #VALUE ! error that was causing a feedback to the whole formula.

    I got rid of it and now it works again!!!

    I wish I had found it before taking the time to write up a request for help .... lol

    Thanks to anyone and everyone who was going to help. I appreciate it!

    -Spydey

  3. #3
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,234
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Complex Formula was working, now failing

    "Literally within 1 min of me posting my plea for help, I figured it out!!!"

    Nice when that happens
    Two plus two equals five for large values of two.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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