Array Formula Alternative - efficiency

aaa89

New Member
Joined
Aug 22, 2011
Messages
32
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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:
Upvote 0
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

Try instead...

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

which needs just enter.
 
Upvote 0
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.
 
Upvote 0
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)))
 
Upvote 0
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.
 
Upvote 0
Try instead...

=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:
Upvote 0
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)
 
Upvote 0
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)))
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top