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