StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTrying to improve efficiency of array formula
text
Body
copied!<p>I have a SUM array formula that has multiple nested IF statements, making it very inefficient. My formula spans over 500 rows, but here is a simple version of it:</p> <pre><code>{=SUM(IF(IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17>0, IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17,0))} </code></pre> <p>As you can see, the first half of the formula checks where the array is greater than zero, and if they are, it sums those in the second part of the formula.</p> <p>You will notice that the same IF statement is repeated in there twice, which to me is inefficient, but is the only way I could get the correct answer. </p> <p>The example data I have is as follows:</p> <p><a href="http://clients.estatemaster.net/SecureClientSite/Download/TempFiles/example.jpg" rel="nofollow noreferrer">Sample Data in spreadsheet http://clients.estatemaster.net/SecureClientSite/Download/TempFiles/example.jpg</a> The answer should be 350 in this instance using the formula I mentioned above.</p> <p>If I tried to put in a MAX statement within the array, therefore removing the test to find where it was greater than zero, so it was like this: </p> <pre><code>{=SUM(MAX(IF(B2:B6>B8:B12,B2:B6,B8:B12)-B14:B18,0))} </code></pre> <p>However, it seems like it only calculates the first row of data in each range, and it gave me the wrong answer of 70.</p> <p>Does anyone know a away that I can reduce the size of the formula or make it more efficient by not needing to repeat an IF statement in there?</p> <hr> <p><strong>UPDATE</strong> </p> <p>Jimmy</p> <p>The MAX formula you suggested didnt actually work for all scenarios.</p> <p>If i changed my sample data in rows 1 to 5 as below (showing that some of the numbers are greater than their respective cells in rows 7 to 11, while some of the numbers are lower)</p> <p><a href="http://clients.estatemaster.net/SecureClientSite/Download/TempFiles/example2.jpg" rel="nofollow noreferrer">Sample Data in spreadsheet http://clients.estatemaster.net/SecureClientSite/Download/TempFiles/example2.jpg</a></p> <p>The correct answer im trying to achive is 310, however you suggested MAX formula gives an incorrect answer of 275.</p> <p>Im guessing the formula needs to be an array function to give the correct answer. </p> <p>Any other suggestions?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload