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.
Bookmarks