How to calculate average percent?

JDfunky wrote:

How do I make the formula only calculate for the trades that are complete, and not for ones still opened?

You can do something like this:

Formulas:

D2: =IF(LEN(C2)>0,C2−B2,"") , filled down.

E2: =IF(LEN(C2)>0,D2÷B2,"") , filled down

B10: =SUMIF(C,"<>"&"",B) (This sums only if there is no entry in cell in the Sale column.)

C10: =SUM(C)

D10: =SUM(D)

E10: =D$10/B$10

SG

Weights:

one profit/loss of almost 2500 against profit/losses of tens and hundreds.

This difference in "weight" does not show up in the percentages.

Not sure whether you want to know this kind of average or not.

You might calculate the total return (SUM) and divide this by the number of trades (COUNT).

Paul.

"I can't see how there could be a dollar profit amount ($2335.09) yet the percentage profit is negative value (-59.6%). What am I doing wrong please?"

I suspect you're not calculating what you think you are calculating.

You've told us the formula for the calculation in F28. This result tells what the profit or loss (before fees) would be IF each purchase cost was the same amount, and the percentage gain or loss on each sale was as indicated in column E. With a list of transactions of varying amounts this average gives little or no useful information.

Consider a small subset of the trades, chosen to magnify the misrepresentation of reality possible with this average.

Here are four transactions:

row 6: a 100% loss ($135)

row 8: a 100% loss ($105)

row 10: a 98% loss ($271)

subTotals: -298% -$511

row 11: a 279% gain ($2499)

Totals: -19% +$1988

Avg/TX: -5% +$ 497

Is this a useful calculation?

A better one, as Paul suggests, be of your average return percentage:

Total return/Total cost (closed trades only) - 1, formatted as percent.

Regards,

Barry

PS: What is the formula in E28?