# Array Formula for Large Data Set Taking Too Long to Calculate

1. ## 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.  Register To Reply

2. ## 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?  Register To Reply

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