Hi folks im stumped...

Im trying to write a formula that will allow me to do an analysis of
an ever expanding list of trades. In the formula im trying to write,
i would like to check each trade to see if they match multiple
criteria and then ultimately count the total number of 'TRADES' as
well as tell me the "Average Contracts per Trade".

The problem arises when you consider that in my data entry , there are
trades that i may enter into for 4 contracts but scale out 1 contract
at a time. Rather then just create 1 trade of 4 contracts and use an
average price on exit (for complicated reasons), i need to parse the
trade out into 4 separate lines.

However, in the analayis i want this to be calculated as '1' trade
with an average of '4 contracts. So, in my list i have created a
separate column that i can TAG the lines that are part of the larger
trade entry. In the example above, i might use the letter 'a' as a
tag that tells me each of the 4 lines of trade data are part of a
single trade 'a' and thus they should be counted as just 1 trade of 4
contracts.

The problem is that i cant come up with a way to analyze this and go
thru the list of all trades to count them where some trades may be
only 1 line with no tag at all (because its solo), and some trades
that may be up to 4 lines with similar tag letters. In the list, each
row gives detail as to the number of contracts , price, date, product
description , etc. Again , i want to count the number of unique
TRADES and also the avergage number of contracts per trade.

Can someone offer any ideas or insight into how to solve this problem.

Thanks in advance.
scott

Hi Scott
if you ensure that for each trade one line is not tagged (e.g. the
first line is alsways not tagged) try the following if column C stores
your tag
=SUMPRODUCT((A1:A1000="some criteria)*(B1:B1000="another
criteria)*(C1:C1000<"a"))


--
Regards
Frank Kabel
Frankfurt, Germany

scott23 wrote:
Hi folks im stumped...

Im trying to write a formula that will allow me to do an analysis of
an ever expanding list of trades. In the formula im trying to write,
i would like to check each trade to see if they match multiple
criteria and then ultimately count the total number of 'TRADES' as
well as tell me the "Average Contracts per Trade".

The problem arises when you consider that in my data entry , there
are
trades that i may enter into for 4 contracts but scale out 1 contract
at a time. Rather then just create 1 trade of 4 contracts and use an
average price on exit (for complicated reasons), i need to parse the
trade out into 4 separate lines.

However, in the analayis i want this to be calculated as '1' trade
with an average of '4 contracts. So, in my list i have created a
separate column that i can TAG the lines that are part of the larger
trade entry. In the example above, i might use the letter 'a' as a
tag that tells me each of the 4 lines of trade data are part of a
single trade 'a' and thus they should be counted as just 1 trade of 4
contracts.

The problem is that i cant come up with a way to analyze this and go
thru the list of all trades to count them where some trades may be
only 1 line with no tag at all (because its solo), and some trades
that may be up to 4 lines with similar tag letters. In the list,
each
row gives detail as to the number of contracts , price, date, product
description , etc. Again , i want to count the number of unique
TRADES and also the avergage number of contracts per trade.

Can someone offer any ideas or insight into how to solve this
problem.

Thanks in advance.
scott