microsoft excel

G

Gordon Gekko

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
 
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.

I hope I could help you.
 
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!
 
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!

Thanks is advance!!!!
 
Originally posted by Huios
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!

Thanks is advance!!!!
More...
there may be another way to do it, but here's one way:

if(a1="0","",your formula)

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.
 
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
 
You must log in or register to reply here.
Back
Top