PDA

View Full Version : Multiple advanced formula calcs



alwayslearn
09-07-2008, 01:50 PM
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
09-07-2008, 02:42 PM
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"),D10:D70)-SUMPRODUCT(--(A10:A70=L10),--(B10:B70="Buy"),D10:D70))
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,D10:D70,1-2*(B10:B70="Buy"))

Bob Phillips
09-07-2008, 03:20 PM
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
09-07-2008, 03:22 PM
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
09-07-2008, 03:30 PM
Probably should be L10

alwayslearn
09-07-2008, 03:35 PM
Perfect.
XLD--the array formulas for the AVG do not calculate.

alwayslearn
09-07-2008, 03:44 PM
Actually they are both calculating to 0

Bob Phillips
09-07-2008, 04:02 PM
Did you array-enter them?

alwayslearn
09-07-2008, 04:11 PM
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
09-07-2008, 04:20 PM
To array-enter a formula, hit Ctrl-Shift-Enter, not just Enter.

alwayslearn
09-07-2008, 05:03 PM
Thanks.

alwayslearn
09-07-2008, 05:44 PM
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
09-07-2008, 06:17 PM
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
09-07-2008, 06:37 PM
Got it--it was the the paste part of the code that was throwing it off.

Thanks everyone for the help.