# Array Formula Alternative - efficiency

#### aaa89

##### New Member
I'm trying to track trades. I've noticed a slow down in excel even before I've entered much data. After searching online, I read that array formulas will slow down excel calculations.

So Im trying to rebuild my formulas starting with the array's first.
This is my sheet setup. Trades will be entered on rows going downward.
From left to right O to P Ill enter the trade info price, stop, exit, share size, etc.
Columns U to AE does various calculations, calculating Risk, Profits, etc.

To avoid errors and DIV/0, I used in all columns an IF to check if O to P are filled out, greater than 0.
Example formula in U
=IF(AND(O7:P7>0,N7="LONG"),(O7-P7),IF(AND(O7:P7>0,N7="SHORT"),P7-O7,"")) Ctrl + shift + enter.

Some formulas are even larger, but they all contain as one of the IF's "O7:P7>0,", which make it an array. Is there an alternative more efficient formula that I can use in all columns? I'm not so familiar with the DSUM / SumProduct / anything else that would be better?

The table is set to 400 rows. I haven't even entered 4 rows, and its showing some sluggish signs.

On the second sheet I have a few tables that sums the trades based on categories I assigned them to. So this adds weight to the sheet, cause as I enter a trade on sheet 1, its added to the appropriate category on sheet 2. Most of the formulas on sheet 2 are SUMIFS, and AVERAGEIFS, COUNTIFS, these should be pretty efficient speedy formulas.
I'm using 2007 and 2010.

Any suggestions how to improve this, and how to speed up the sheet would be appreciated.
Thanks lot

Last edited:

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### Jon von der Heyden

##### MrExcel MVP, Moderator
Try;

=LOOKUP(N7,{"LONG";"SHORT"},CHOOSE({1;2},O7-P7,P7-O7))

BTW, there is no division in your formula so no risk of #DIV/0 error.

I also think it's a mistake to assume that because a formula is an array formula that it s immediately less efficient. Your formula is only evaluating 2 cells... The problems arise when people start doing complex evaluation of ranges (arrays) of considerable size.

Last edited:

##### MrExcel MVP
I'm trying to track trades. I've noticed a slow down in excel even before I've entered much data. After searching online, I read that array formulas will slow down excel calculations.

So Im trying to rebuild my formulas starting with the array's first.
This is my sheet setup. Trades will be entered on rows going downward.
From left to right O to P Ill enter the trade info price, stop, exit, share size, etc.
Columns U to AE does various calculations, calculating Risk, Profits, etc.

To avoid errors and DIV/0, I used in all columns an IF to check if O to P are filled out, greater than 0.
Example formula in U
=IF(AND(O7:P7>0,N7="LONG"),(O7-P7),IF(AND(O7:P7>0,N7="SHORT"),P7-O7,"")) Ctrl + shift + enter.

Some formulas are even larger, but they all contain as one of the IF's "O7:P7>0,", which make it an array. Is there an alternative more efficient formula that I can use in all columns? I'm not so familiar with the DSUM / SumProduct / anything else that would be better?

The table is set to 400 rows. I haven't even entered 4 rows, and its showing some sluggish signs.

On the second sheet I have a few tables that sums the trades based on categories I assigned them to. So this adds weight to the sheet, cause as I enter a trade on sheet 1, its added to the appropriate category on sheet 2. Most of the formulas on sheet 2 are SUMIFS, and AVERAGEIFS, COUNTIFS, these should be pretty efficient speedy formulas.
I'm using 2007 and 2010.

Any suggestions how to improve this, and how to speed up the sheet would be appreciated.
Thanks lot

=IF(INDEX(FREQUENCY(O7:P7,0),2)=2,IF(N7="LONG",O7-P7,IF(N7="SHORT",P7-O7,"")),"")

which needs just enter.

#### aaa89

##### New Member
Thanks for you reply. The lookup puts a #N/A on rows which no trades are entered yet, and doesn't check if O to P are filled in. I want all columns U to AE blank unless all fields are filled in.

#### Jon von der Heyden

##### MrExcel MVP, Moderator
Try;

=LOOKUP(N7,{"LONG";"SHORT"},CHOOSE({1;2},O7-P7,P7-O7))

BTW, there is no division in your formula so no risk of #DIV/0 error.

I also think it's a mistake to assume that because a formula is an array formula that it s immediately less efficient. Your formula is only evaluating 2 cells... The problems arise when people start doing complex evaluation of ranges (arrays) of considerable size.
Couple of follow on remarks (I would add as edit usually but you're fortunate enough to attract Aladins attention ).

1. My statement "no risk of #DIV/0 error" isn't entirely accurate. You can still encounter the error is either O7 or P7 is already #DIV/0.

2. I completely omitted the zero test part of the expression because I assumed it was being (incorrectly) used to avoid #DIV/0.

You could use:

=IF(COUNTIF(O7:P7,0),"",LOOKUP(N7,{"LONG";"SHORT"},CHOOSE({1;2},O7-P7,P7-O7)))

#### aaa89

##### New Member
Couple of follow on remarks (I would add as edit usually but you're fortunate enough to attract Aladins attention ).

1. My statement "no risk of #DIV/0 error" isn't entirely accurate. You can still encounter the error is either O7 or P7 is already #DIV/0.

2. I completely omitted the zero test part of the expression because I assumed it was being (incorrectly) used to avoid #DIV/0.

You could use:

=IF(COUNTIF(O7:P7,0),"",LOOKUP(N7,{"LONG";"SHORT"},CHOOSE({1;2},O7-P7,P7-O7)))

In column U I dont risk the DIV/0 but I have many other columns with div, so there I get DIV/0 and # Value.

Example in AA it starts to get more dizzy
=IF(AND(X7>0,O7:S7>0,N7="LONG"),X7/(Q7-O7),IF(AND(X7<0,O7:S7>0,OR(N7="LONG",N7="SHORT")),(X7/U7)/1,IF(AND(X7>0,O7:S7>0,N7="SHORT"),X7/(O7-Q7),"")))

This cell does a calculation on Long and short on wining trades, and on losing trades.

Would which you prefer the INDEX / LOOKUP? Not to familiar with either of them. Still trying to figure out how the INDEX works so I can customize and use it for the other columns.

#### aaa89

##### New Member

=IF(INDEX(FREQUENCY(O7:P7,0),2)=2,IF(N7="LONG",O7-P7,IF(N7="SHORT",P7-O7,"")),"")

which needs just enter.

I like this formula, But I can't use it when i dont understand what im doing... Because on other columns I get FALSE when i try to add this "=IF(INDEX(FREQUENCY(O7:P7,0),2)=2" before the cell formula.
Can you explain me what I just did? Whats the ,2 ,=2 for the INDEX? and whats the FREQUENCY doing in this case?
I only knew to use the FREQUENCY to count...
Thanks

Last edited:

#### aaa89

##### New Member
You could use:

=IF(COUNTIF(O7:P7,0),"",LOOKUP(N7,{"LONG";"SHORT"},CHOOSE({1;2},O7-P7,P7-O7)))

I still get the N/A - the COUNTIF(O7:P7,0),"", does NOT leave the cell blank even if O to P are not filled in.

##### MrExcel MVP
I like this formula, But I can't use it when i dont understand what im doing... Because on other columns I get FALSE when i try to add this "=IF(INDEX(FREQUENCY(O7:P7,0),2)=2" before the cell formula.
Can you explain me what I just did? Whats the ,2 ,=2 for the INDEX? and whats the FREQUENCY doing in this case?
I only knew to use the FREQUENCY to count...
Thanks

Rich (BB code):
``````=IF(INDEX(FREQUENCY(O7:P7,0),2,1)=2,
IF(N7="LONG",O7-P7,IF(N7="SHORT",P7-O7,"")),"")``````

Better even...
Rich (BB code):
``````=IF(INDEX(FREQUENCY(O7:P7,0),2,1)=COLUMNS(O7:P7),
IF(N7="LONG",O7-P7,IF(N7="SHORT",P7-O7,"")),"")``````

The FREQUENCY(O7:P7,0) bit creates an array result consisting of the number of the numeric items <= 0 and the number of numeric items > 0. That is, it determines occurrence frequencies for <=0 and >0 numeric items in the target range. An array result of:

{0;2}

means nil occurrences of <=0 and 2 occurences of >0. Note that the foregoing array result is a 2x1 matrix (or a two dimensional column vector).

INDEX(FREQUENCY(O7:P7,0),2,1)

picks out the 2nd item from the array result.

The condition part of the IF bit tests whether the target range consists of all >0 numbers with:

INDEX(FREQUENCY(O7:P7,0),2,1)=COLUMNS(O7:P7)

#### Jon von der Heyden

##### MrExcel MVP, Moderator
I still get the N/A - the COUNTIF(O7:P7,0),"", does NOT leave the cell blank even if O to P are not filled in.

And with respect to my formula, accommodating blanks:

=IF(SUM(COUNTIF(O7:P7,{0;""})),"",LOOKUP(N7,{"LONG";"SHORT"},CHOOSE({1;2},O7-P7,P7-O7)))

Replies
0
Views
521
Replies
1
Views
201
Replies
4
Views
400
Replies
0
Views
356
Replies
2
Views
310

### Forum statistics

1,195,600
Messages
6,010,652
Members
441,558
Latest member
lambierules ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back