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
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: