General Topics
Markets
Technical Topics
Brokerage Firms
Company Specific
Community Lounge
Site Support

# microsoft excel

Discussion in 'Trading Software' started by Gordon Gekko, Jun 10, 2002.

1. i know this isn't really a trading question, but since a lot of us here probably use excel, i think i might be able to get an answer.

say i have a column of 5 numbers. the numbers are how much % i made or lost.

for example, say my last 5 trades were:

+3%
-1%
+7%
+2%
-2%

is there a way i could have excel pay attention to positive or negative numbers? what i want to do is, from one list of numbers (like above), i want to calculate what my average win % is and what my average loss % is.

if excel can do that, can it also calculate what % of the above numbers are positive?

thanks

2. I have only the german version of Excel, but I think in english the functions are named "if" and "count".
I do it this way:
I check the value with "if", wether it's positive or negative and write then a "1" or a "0" in the columns next (one column for +, one for -).
(if(A1>=1, A1, ""))
With this columns I can calculate the averages. You can also use different columns for your +% and -% value, than it is easier.

3. Assuming those numbers are in column A, this should do the trick:

for average win put a formula in a nearby cell:
=SUMIF(A:A,">=0")/COUNTIF(A:A,">=0")

for average loss:
=SUMIF(A:A,"<0")/COUNTIF(A:A,"<0")

for win %:
=COUNTIF(A:A,">0")/COUNT(A:A)

Hope this helps!

4. this is exactly what i wanted..thanks guys!

5. Question...

For a cell that has a formula in it, but no value, yet, how do I get rid of the columns of zero's. I know there is a way to do it without affecting the formula, I just don't know it!

6. there may be another way to do it, but here's one way:

assuming this was put in a2 and it gets its result from a1. if the result is 0, it will put a blank in a2. if the result is not 0, it will do your formula.

7. Format column

Format
Cell
Custom
scroll to bottom of list
_(* #,##0_);_(* (#,##0);_(* ""_);_(@_)

The format before the first ; is how positive numbers are formatted
the next format is the negative numbers,
the third format is zero numbers,
i forget the fourth place

sportsguy

ET IS FREE FOR TRADERS BECAUSE OF THE FINANCIAL SUPPORT FROM THESE SPONSORS: