Hello everyone, I've been using excel for a few years now, but until
recently have really sat down and been getting help with formula's on
this options trading sheet I've been working on.

It's purpose is to track trade statistics, and see how one performs over
the year.

Attached I have 3 screenshots, the first is the transaction page, and
the second is the summary page, third is the graph

On the first page the issue that I'm having is every blank row is listed
with January, how can I make it be blank until I enter in a date? The
formula in use is
=TEXT(C79, "MMMM")

On the second page, the layout is as followed

TOTAL TRADES uses
=COUNT('2018 Options Trades'!C:C)

GAIN uses
=COUNTIF('2018 Options Trades'!M:M, "=0")
LOSS uses
=COUNTIF('2018 Options Trades'!M:M, "<=0")

WIN/LOSS RATIO uses
=COUNTIF('2018 Options Trades'!M:M, "=0")/A2

GAIN AVERAGE uses
=AVERAGEIF(Table2[$ GAIN/LOSS], "0")
LOSS AVERAGE uses
=AVERAGEIF(Table2[$ GAIN/LOSS], "<0")

TOTAL GAIN uses
=SUMIF(Table2[$ GAIN/LOSS], "0")
TOTAL LOSS uses
=SUMIF(Table2[$ GAIN/LOSS], "<0")

GAIN/LOSS YTD uses
=(G2+H2)

CONTRACT QTY uses
=SUM('2018 Options Trades'!E2:E497)

COMMISSION uses
=SUM('2018 Options Trades'!L2:L497)

The things I'm lost with are on the second part of this, it's broken
down by the months Win Rate/Net Profit.

The Net Profit I have calculated by
=SUMIF('2018 Options Trades'!D2:D18, "AAPL", '2018 Options
Trades'!M2:M18)
This is done based on the specific of range of cells that had trades
given in that specific month.

The problem I have with that is how can I have this auto calculate
future trades without having to readjust any ranges?

On the Win Rate side, that's all manual

How can this be done automatically as well?
And then under the TOTAL section what formula can I use to make it
determine the months win's over total trades and turn it into a
percentage?

And finally I have a graph page

How can I set it so it only draws for data that has full info across the
row and not the total 497 lines?

Thank you


+-------------------------------------------------------------------+ |Filename: Screen Shot 2018-04-20 at 3.03.49 PM.jpg | |Download: http://www.excelbanter.com/attachment.php?attachmentid=1054| |Filename: Screen Shot 2018-04-20 at 3.04.21 PM.jpg | |Download: http://www.excelbanter.com/attachment.php?attachmentid=1055| |Filename: Screen Shot 2018-04-20 at 3.30.41 PM.jpg | |Download: http://www.excelbanter.com/attachment.php?attachmentid=1056| +-------------------------------------------------------------------+



--
sjlarowe

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)