Calculating the average gain & loss for numbers in one column?

**Vladmir1111** · 03-27-2009, 12:51 PM

Hi,

I wanted to know how I can calculate the average gain and average loss for data that is inputted into one column? For example;

Column A
$12,000
-$5,000
$6,500
-$1,000
$8,000
-$4,000

Average Gain ??
Average Loss ??

Thanks and look forward to hearing from you.

Regards,

Vladmir

**NBVC** · 03-27-2009, 01:10 PM

=Average(if(A1:A10>0,A1:A10))

=Average(if(A1:A10<0,A1:A10))

adjust ranges to suit.

Both these formulas must be confirmed with CTRL+SHIFT+ENTER not just ENTER you will see { } brackets appear around the formula when done correctly.

**DonkeyOte** · 03-27-2009, 01:11 PM

Non-Array

Gain: =SUMIF(A:A,">0")/COUNTIF(A:A,">0")
Loss: =SUMIF(A:A,"<0")/COUNTIF(A:A,"<0")

If using XL2007 make use of non-array AVERAGEIF

Gain: =AVERAGEIF(A:A,">0",A:A)
Loss: =AVERAGEIF(A:A,"<0",A:A)

**JimmyA** · 03-27-2009, 01:12 PM

Hello There

Try this;

=AVERAGEIF(A2:A8,">0")
=AVERAGEIF(A2:A8,"<0")

Just change the range as you require.

Hope this helps

Jim

**Vladmir1111** · 03-29-2009, 01:37 AM

DonkeyOte, JimmyA, NBVC

Thank you for your input and swift response, it's even quicker than an amazon.com auto response thanking you for your order(s).

Much appreciated, be well and take care.

Regards,

Vladmir