+ Reply to Thread
Results 1 to 2 of 2

Array Formula for Large Data Set Taking Too Long to Calculate

  1. #1
    Registered User
    Join Date
    07-11-2014
    Location
    Pennsylvania
    MS-Off Ver
    2010
    Posts
    5

    Array Formula for Large Data Set Taking Too Long to Calculate

    I have a spreadsheet that has a large data set, at least 5,000 rows and often as many as 15,000 rows by 65 columns. I am using an array formula that is causing the sheet to take up to 2 or 3 minutes to recalculate (before creating this array formula it would take about 10 seconds to calculate).

    The spreadsheet has about 50 columns, I am only showing an example of how the array formula is being used to keep the question as simple as possible.

    The task is to find the lowest price of 3 possible prices and then alter that price based on the source. It is important to know what the source of the lowest price is from so that the formula can alter that price based on the source [if the source wasn't important I was just MIN() ].

    Here is the formula =IF(SUM(B1:D1)=0,0,IF(MIN(IF(B1:D1,B1:D1))=B1,MIN(IF(B1:D1,B1:D1))-1,IF(MIN(IF(B1:D1,B1:D1,B1:D1))=C1,MIN(IF(B1:D1,B1:D1))+0.01,MIN(IF(B1:D1,B1:D1))+1)))

    The formula above resides in column A with "Source A" price in column B, "Source B" price in column C, and "Source C" price in column D

    As you can see I am altering the price by -1 if lowest price is from "Source A", by +.01 if lowest price is from "Source B", and by +1 if lowest price is from "Source C"

    Any help as to an alternative to the array formula would be appreciated.
    Last edited by clark153; 01-24-2015 at 08:30 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,006

    Re: Array Formula for Large Data Set Taking Too Long to Calculate

    It looks like you are using the same MIN(IF()) formula 5 times inthat formula...
    MIN(IF(B1:D1,B1:D1))

    Perhaps out that in a helper column and then run your formula based off teh helper?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Arrey formula taking long time to calculate - any other way or MACRO can be built ??
    By kedarlimaye87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-24-2013, 08:44 AM
  2. Array Formulas taking too long to calculate
    By CheeksExcelForum in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-17-2013, 06:56 PM
  3. Optimizing Formulas - taking too long to calculate large worksheet
    By lightsandsirens in forum Excel General
    Replies: 0
    Last Post: 08-21-2012, 10:38 AM
  4. [SOLVED] Array Formula taking too Long
    By gborja888 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-01-2012, 03:41 AM
  5. Excel (2003) array formula taking too long
    By Krazy Kasper in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-11-2008, 11:26 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