+ Reply to Thread
Results 1 to 10 of 10

SMALL IF Formula crashing file?! Can't figure this out!

  1. #1
    Registered User
    Join Date
    12-14-2020
    Location
    Virginia
    MS-Off Ver
    MS 365
    Posts
    12

    Question SMALL IF Formula crashing file?! Can't figure this out!

    My co-worker and I have been racking our minds over this for the last week and have no idea what's going on. A file I've been sending out every week for the last year suddenly doesn't work for everyone other than me; they open it up, change a value to toggle the output and then excel crashes. I've rebuilt the file 4 times and still it doesn't work, so it's not due to hidden names or external links. It looks like it may be due to a =SMALL(IF(),ROW()-ROW()) formula which works fine on my end and is a formula I use often that I've never had problems with (i use it in other files I send out weekly that no one has issues with).



    Does anyone have any ideas why this would occur? I've attached the file after removing any confidential info but it likely will not work for you. When people change cell D5 to 101-129 it crashes. The formula in question is : =IF($A7>$A$5,"",SMALL(IF(Data!$B$6:$B$511=$D$5,Data!$C$6:$C$511),ROW($C7)-ROW($C$6)))
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    53,334

    Re: SMALL IF Formula crashing file?! Can't figure this out!

    You cannot use a value range in this way - you would need to set the lower and upper extremes of the range as separate entities.

    Perhaps tell us what the ultimate aim of the formula is and one of us can suggest a better alternative.

    It doesn't crash for me - just brings back no results (unsurprisingly).

    AliGW on MS365 Insider (Windows) 32 bit
    C
    D
    5
    District:
    101-129
    6
    Salon #
    Salon Name
    7
    8
    9
    10
    11
    12
    13
    14
    15
    Sheet: Output
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    12-14-2020
    Location
    Virginia
    MS-Off Ver
    MS 365
    Posts
    12

    Re: SMALL IF Formula crashing file?! Can't figure this out!

    Sorry I mean any number from 100 to 129, not "101-129", so try 101 for example. But if it didn't crash and returned blanks then it still worked for you somehow...

    The purpose of the formula is to pull in the stores / salons associated with the district in numerical order. For example, district 100 would have stores 1, 2, 3, 4... 16 and then it would be blank after store 16.

    The first part =IF($A23>$A$5,"", isn't necessarily needed, I just included that to make it look cleaner but there are other ways to do that like an iferror or something.
    Last edited by budfox310; 09-21-2021 at 10:55 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    53,334

    Re: SMALL IF Formula crashing file?! Can't figure this out!

    Ah, I see!

    So this is what happens with 120:

    AliGW on MS365 Insider (Windows) 32 bit
    C
    D
    4
    For Period: Week Ending 9/12/21
    5
    District:
    120
    6
    Salon #
    Salon Name
    7
    323
    Store 1333
    8
    324
    Store 1337
    9
    325
    Store 1542
    10
    326
    Store 1634
    11
    327
    Store 1866
    Sheet: Output

    No crash for me.

  5. #5
    Registered User
    Join Date
    12-14-2020
    Location
    Virginia
    MS-Off Ver
    MS 365
    Posts
    12

    Re: SMALL IF Formula crashing file?! Can't figure this out!

    Now I'm even more confused as to what's going on! It works for me on both my personal and work laptops and yet every person at work that I've sent it to says it crashes and I can't figure out why, even after rebuilding it multiple times! I might not be able to figure out what's going on but maybe it'll work if I use another formula that does the same thing? Is there one?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    53,334

    Re: SMALL IF Formula crashing file?! Can't figure this out!

    There may be - can you confirm that you are still using Excel 2016? If you have MS365 (and, crucially, if your co-workers also have it), this may be doable with the new dynamic array functions.

    ... says it crashes and I can't figure out why ...
    Did you ask any of them to create a screencast of this? Did they say exactly what they meant by 'crash'?

  7. #7
    Registered User
    Join Date
    12-14-2020
    Location
    Virginia
    MS-Off Ver
    MS 365
    Posts
    12

    Re: SMALL IF Formula crashing file?! Can't figure this out!

    I have the latest version of MS 365 as do my co-workers. I'm sure there's something I could do using a help column or something? Maybe I could even rebuild the file to work off a pivot table?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    53,334

    Re: SMALL IF Formula crashing file?! Can't figure this out!

    There are new functions (FILTER, SORTBY, etc.) that could certainly be used. I am running short of time now (teatime fast approaches, and you will know what that means on this side of the pond ...). I am sure that others will pick up the thread.

  9. #9
    Registered User
    Join Date
    12-14-2020
    Location
    Virginia
    MS-Off Ver
    MS 365
    Posts
    12

    Re: SMALL IF Formula crashing file?! Can't figure this out!

    No worries, thanks for the help. I'm trying a new formula to see if that resolves the problem (just created a helper column in the data tab and used a sumifs formula on the output to pull in the store #). If the file still doesn't work for folks then I'll just quit

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    53,334

    Re: SMALL IF Formula crashing file?! Can't figure this out!

    Quitting can be good - Iíve just retired. Highly recommended!

+ 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. Excel file crashing on open
    By anasttin in forum Excel General
    Replies: 1
    Last Post: 05-16-2020, 06:28 PM
  2. Excel File crashing
    By mayoman157 in forum Excel General
    Replies: 1
    Last Post: 11-28-2017, 01:34 PM
  3. Excel crashing when trying to view code before macro run; not crashing after first run
    By goonerforlyf in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2015, 05:27 PM
  4. Replies: 4
    Last Post: 02-03-2014, 08:05 AM
  5. Sum formula to exclude monthly budget figure when actual figure is entered
    By rocketmail in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-02-2013, 04:22 AM
  6. why is it my excel file is always crashing?....
    By tweety127 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-02-2006, 06:26 PM
  7. File crashing when opening another
    By LAF in forum Excel General
    Replies: 0
    Last Post: 02-02-2005, 02:06 PM

Tags for this Thread

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