Array Formula Alternative - efficiency

[aaa89]

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

 

[aaa89]

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

Thanks all for your help. Since I'm not so familiar with the INDEX & FREQUENCY, LOOKUP, CHOOSE, I took only the first half of your formula, good enough to avoid the array, and I didn't have to retype all my formulas.

I used this =IF(SUM(COUNTIF(O7:P7,{0;""})),"", - as the first IF in all columns to check if they were all filled in, and then continued with my simple formula, I previously had. (I adjusted the range accordingly O7:P7 / S7, based on what needs to be filled in prior to calculating that column.
For example;
=IF(SUM(COUNTIF(O7:P7,{0;""})),"",IF(N7="LONG",(O7-P7),IF(N7="SHORT",P7-O7,"")))

=IF(SUM(COUNTIF(O7:S7,{0;""})),"",IF(N7="LONG",(Q7-O7)/U7,IF(N7="SHORT",((O7-Q7)/U7),"")))

Works great.

I got Three Questions -

1. Can you explan me more what this ,{0;""}) does? I get that I counts the range, but can figure out this part. It would help if i understood what I'm doing.

2. Should I re-write all formulas to the full formula any of you suggested to improve efficiency, or the formula I now have without the array is equally fine? See the following question, might be I can even stick with the orriginal arrray formula I had.

3. I tried deleting the second sheet I have in this workbook, and this sheet started working perfectly fine, fast without stalling. It apears that some formula on that sheet is causing this slow down. I think I know what it is.

Workbook structure is as follows.
Sheet 1 consists of a table 400 rows, were each individual trade will be entered and calculated.

Sheet 2 - has a few tables, doing various calculations dividing the trade results into categories.
Here is where I think I've done the mistake causing this massive slow down.
The Table on sheet 1 has only 400 rows, but can easily be expanded if needed. i didnt want to have to rewrite the ranges on sheet 2 - So I set them to check all rows... Is it posible that this is causing the workbook to slow down?

Example formula
=IF(COUNTIFS('Individual Trade Entry'!$G$7:$G$1048576,C20,'Individual Trade Entry'!$S$7:$S$1048576,">=0")=0,"",IF($I$10>0,COUNTIFS('Individual Trade Entry'!$Y$7:$Y$1048576,">="&$I$10,'Individual Trade Entry'!$G$7:$G$1048576,C20),COUNTIFS('Individual Trade Entry'!$Y$7:$Y$1048576,">0",'Individual Trade Entry'!$G$7:$G$1048576,C20)))

'Individual Trade Entry' is sheet 1.

If this is possibly the cause, how can I set that it updates the range on sheet 2, as I expand down the table on sheet 1? Should I give each column a "NameList," would that auto update, as I expand the table on sheet 1?

Your help would greatly be appreciated.
Thanks