CODEWITHSUNDEEP
excelworksheet-functionarray-formulas
user40432
user40432

Reputation: 71

Trying to improve efficiency of array formula

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:

{=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))}

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.

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.

The example data I have is as follows:

Sample Data in spreadsheet http://clients.estatemaster.net/SecureClientSite/Download/TempFiles/example.jpg The answer should be 350 in this instance using the formula I mentioned above.

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: 

{=SUM(MAX(IF(B2:B6>B8:B12,B2:B6,B8:B12)-B14:B18,0))}

However, it seems like it only calculates the first row of data in each range, and it gave me the wrong answer of 70.

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?

UPDATE

Jimmy

The MAX formula you suggested didnt actually work for all scenarios.

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)

Sample Data in spreadsheet http://clients.estatemaster.net/SecureClientSite/Download/TempFiles/example2.jpg

The correct answer im trying to achive is 310, however you suggested MAX formula gives an incorrect answer of 275.

Im guessing the formula needs to be an array function to give the correct answer.

Any other suggestions?

Upvotes: 4

Views: 1478

Answers (5)

Charles Williams
Charles Williams

Reputation:

A more calculation-efficient (and especially re-calculation efficient) way is to use helper columns instead of array formulae:

C1: =MAX(A1,A7)-A13
D1: =IF(C1>0,C1,0)

copy both these down 5 rows

E1: =SUM(D1:D5)

Excel will then only recalculate the formulae dependent on any changed value, rather than having to calculate all the virtual columns implied by the array formula every time any single number changes. And its doing less calculations even if you change all the numbers.

Upvotes: 1

Jimmy
Jimmy

Reputation: 91432

=MAX( MAX( sum(A1:A5), sum(A7:A11) ) - sum(A13:A17), 0)

Upvotes: 3

Nathaniel Reinhart
Nathaniel Reinhart

Reputation: 1183

What about this?

=MAX(SUM(IF(A1:A5>A7:A11, A1:A5, A7:A11))-SUM(A13:A17), 0)

Edit:

Woops - Missed the throwing out negatives part. What about this? Not sure it it's faster...

=SUM((IF(A1:A5>A7:A11,IF(A1:A5>A13:A17,A1:A5,A13:A17),IF(A7:A11>A13:A17,A7:A11,A13:A17))-A13:A17))

Edit 2:

How does this perform for you?

=SUM((((A1:A5>A13:A17)+(A7:A11>A13:A17))>0)*(IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17))

Upvotes: 0

DJ.
DJ.

Reputation: 16247

This seems to work:

{=SUM(IF(A1:A5>A7:A11,A1:A5-A13:A17,A7:A11-A13:A17))}

EDIT - doesn't handle cases where subtraction ends up negative

This works - but is it more efficient???

{=SUM(IF(IF(A1:A5>A7:A11,A1:A5,A7:A11)>A13:A17,IF(A1:A5>A7:A11,A1:A5,A7:A11)-A13:A17,0))}

Upvotes: 0

Karl
Karl

Reputation: 9155

You may want to look into the VB Macro editor. In the Tools Menu, go to Macros and select Visual basic Editor. This gives a whole programming environment where you can write your own function.

VB is a simple programming language and google has all the guidebooks you need.

There, you can write a function like MySum() and have it do whatever math you really need it to, in a clear way written by yourself.

I pulled this off google, and it looks like a good guide to setting this all up. http://office.microsoft.com/en-us/excel/HA011117011033.aspx

Upvotes: 0

Related Questions