Multiple advanced formula calcs

[alwayslearn]

I am new to the site and have been asking questions all weeked with incredible result and for that I thank you all who helped.

I am creating a spreadsheet to analyze given contracts in a market. I am having a few issues with some of the formulas.

There are two TXT files where the data comes from but my issue is that I need to create the spreadsheet to be able to work with any data set(not just the amount or columns in each.

All the calculations in the chart have to be under commandbutton3 (the analyze button). I put my comments about how each column shoudl be calculated.

I have attached the spreadsheet and the two txt files if anyone wants to take a look and see if they can help me out.

both txt files should be saved to c:\

Thanks.

[mikerickson]

The import routine didn't work for me, so the columns C&D may be swapped.
In cell M10 put =COUNTIF(A:A,K10) and drag down.
In N1 =(SUMPRODUCT(--(A10:A70=L10),--(B10:B70="Sell"),D1070)-SUMPRODUCT(--(A10:A70=L10),--(B10:B70="Buy"),D1070))
In O1, =SUMPRODUCT(--(A10:A70=L10),--(B10:B70="Buy"),C10:C70)/SUMPRODUCT(--(A10:A70=L10),--(B10:B70="Buy"))

In P1, =SUMPRODUCT(--(A10:A70=L10),--(B10:B70="Sell"),C10:C70)/SUMPRODUCT(--(A10:A70=L10),--(B10:B70="Sell"))

In Q1=SUMPRODUCT(--(A10:A70=L10),C10:C70,D1070,1-2*(B10:B70="Buy"))

[Bob Phillips]

I have never seen the point of two SUMPRDUCTS to calculate an average when a simple array AVERAGE does it

=AVERAGE(IF(($A$10:$A$100=$L10)*($B$10:$B$100="Buy"),$C$10:$C$100))

=AVERAGE(IF(($A$10:$A$100=$L10)*($B$10:$B$100="Sell"),$C$10:$C$100))

[alwayslearn]

M10 formula gives me a value of 0--why is the criteria K10? This formula has to count the number of populated cells per product.

[Bob Phillips]

Probably should be L10

[alwayslearn]

Perfect.
XLD--the array formulas for the AVG do not calculate.

[alwayslearn]

Actually they are both calculating to 0

[Bob Phillips]

Did you array-enter them?

[alwayslearn]

I did not-I am not sure how. I would just use the sumproduct formulas but it seems as though as the formula get copied down it leaves out parts of my imported data.

[Bob Phillips]

To array-enter a formula, hit Ctrl-Shift-Enter, not just Enter.

[alwayslearn]

Thanks.

[alwayslearn]

So I have the code entered and I am getting some errors on my analyze button click. Can you take a look at the SUB Calcs and let me know what you think.

I attached the new file.

Thanks.

[alwayslearn]

It looks like It is having an issue with my copy paste special code. I can get my last three formulas to work ok with this but not with the first four.

[alwayslearn]

Got it--it was the the paste part of the code that was throwing it off.

Thanks everyone for the help.