+ Reply to Thread
Results 1 to 3 of 3

Simplify nested IF statements to speed up calculations

  1. #1
    Registered User
    Join Date
    06-14-2013
    Location
    Germany
    MS-Off Ver
    Excel 2013
    Posts
    20

    Simplify nested IF statements to speed up calculations

    Hello guys, I have a large table with very time consuming array formula calculations with nested IF criteria. The data table (Sheet1) contains 800,000 rows and ca.100 columns and the results table (Sheet2) contains 20,000 rows. Basic calculations like MEDIAN and STDEV.P and COUNT are performed if multiple criteria apply.
    For example: {=MEDIAN(IF('Sheet1'!$FC:$FC<0.5,IF('Sheet1'!$FH:$FH>4,IF('Sheet1'!$L:$L='Sheet2'!$J2,IF(ISNUMBER('Sheet1'!AC:AC),'Sheet1'!AC:AC)))))}

    The problem is that calculating ten columns of 20,000 rows referencing to the 800,000 row data-table takes hours even with 100% CPU usage and 8 threads.
    I already sorted both tables the same way and remove all formulas finished calculating by pasting only values after each batch of calculations is done. Still it takes ages.
    Is there a way to economize this kind of formula to optimize excels calculating speed?

    Thank you and best regards, S.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Simplify nested IF statements to speed up calculations

    First, avoid entire column references, restrict it to the actual used area.
    Look into using Dynamic Named Ranges to accomodate fluxuating data size.

    And this is where helper columns are useful.
    I understand the desire to avoid extra columns.
    But it's basically 'Pick your poison', extra columns, or slow calculations.

    On sheet 1, say FD2 and filled down
    =IF(AND(FC2<0.5,FH2>4,ISNUMBER(AC2)),AC2,"")

    Then try
    =MEDIAN(IF('Sheet1'!$L$2:$L$800000='Sheet2'!$J2,'Sheet1'!FD$2:FD$800000))

  3. #3
    Registered User
    Join Date
    06-14-2013
    Location
    Germany
    MS-Off Ver
    Excel 2013
    Posts
    20

    Re: Simplify nested IF statements to speed up calculations

    Ah I see that basically summarizes most of the criteria beforehand. Thank you, I´ll try that.

+ 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. Simplify numerous conditional statements
    By ykobure in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-05-2015, 02:41 AM
  2. Simplify blocks of conditional statements
    By ykobure in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-04-2015, 02:38 AM
  3. Replies: 1
    Last Post: 12-04-2013, 01:04 PM
  4. How to Simplify Nested If Function…
    By Rajeshkumar R in forum Excel General
    Replies: 7
    Last Post: 03-07-2012, 05:09 AM
  5. Speed calculations
    By ABSTRAKTUS in forum Excel General
    Replies: 3
    Last Post: 05-21-2010, 05:46 PM
  6. simplify nested if statement
    By maacmaac in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-08-2007, 03:16 PM
  7. Simplify a function to save speed?
    By a94andwi in forum Excel General
    Replies: 2
    Last Post: 11-21-2006, 10:35 AM

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