How to average a column of numbers that are greater than 0?
 

I am trying to average a column of numbers. I want to only average the cells
that contain a number greater than 0. How do I do that?

How to average a column of numbers that are greater than 0?
 

you can use a pivot table, or auto filter with this condititional (greater
than 0) and in this case use =subtotal(1,a2:a500)

hth
regards from Brazil
Marcelo

"JimNColorado" escreveu:

I am trying to average a column of numbers. I want to only average the cells
that contain a number greater than 0. How do I do that?

How to average a column of numbers that are greater than 0?
 

this is an array formula that must be entered/edited using ctrl+shift+enter

=AVERAGE(IF(E1:E210,E1:E21))

--
Don Guillett
SalesAid Software

"JimNColorado" wrote in message
...
I am trying to average a column of numbers. I want to only average the
cells
that contain a number greater than 0. How do I do that?

How to average a column of numbers that are greater than 0?
 

Jim


=AVERAGE(IF(D2:D18<0,D2:D18))

Enter as an array Ctrl/shift/enter


Don


"JimNColorado" wrote in message
...
I am trying to average a column of numbers. I want to only average the
cells
that contain a number greater than 0. How do I do that?

How to average a column of numbers that are greater than 0?
 

Jim

opps! Should be:
=AVERAGE(IF(B2:B180,B2:B18))



Don

"JimNColorado" wrote in message
...
I am trying to average a column of numbers. I want to only average the
cells
that contain a number greater than 0. How do I do that?

How to average a column of numbers that are greater than 0?
 

I'll admit my bias against array formulas and offer an alternative...
Average is simply sum/count. So =sumif(range,"0")/countif(range,"0") will
average the positive values.

"JimNColorado" wrote:

I am trying to average a column of numbers. I want to only average the cells
that contain a number greater than 0. How do I do that?

How to average a column of numbers that are greater than 0?
 

I support this result as the most logical....
"bpeltzer" wrote in message
...
I'll admit my bias against array formulas and offer an alternative...
Average is simply sum/count. So =sumif(range,"0")/countif(range,"0")
will
average the positive values.

"JimNColorado" wrote:

I am trying to average a column of numbers. I want to only average the
cells
that contain a number greater than 0. How do I do that?

How to average a column of numbers that are greater than 0?
 

That worked. Thanks. I would have never figured it out.

"bpeltzer" wrote:

I'll admit my bias against array formulas and offer an alternative...
Average is simply sum/count. So =sumif(range,"0")/countif(range,"0") will
average the positive values.

"JimNColorado" wrote:

I am trying to average a column of numbers. I want to only average the cells
that contain a number greater than 0. How do I do that?

How to average a column of numbers that are greater than 0?
 

why?

--
Don Guillett
SalesAid Software

"Sasa Stankovic" wrote in message
...
I support this result as the most logical....
"bpeltzer" wrote in message
...
I'll admit my bias against array formulas and offer an alternative...
Average is simply sum/count. So =sumif(range,"0")/countif(range,"0")
will
average the positive values.

"JimNColorado" wrote:

I am trying to average a column of numbers. I want to only average the
cells
that contain a number greater than 0. How do I do that?

How to average a column of numbers that are greater than 0?
 

because I don't like array functions... it is not something special... I
just dont like to use them...
"Don Guillett" wrote in message
...
why?

--
Don Guillett
SalesAid Software

"Sasa Stankovic" wrote in message
...
I support this result as the most logical....
"bpeltzer" wrote in message
...
I'll admit my bias against array formulas and offer an alternative...
Average is simply sum/count. So =sumif(range,"0")/countif(range,"0")
will
average the positive values.

"JimNColorado" wrote:

I am trying to average a column of numbers. I want to only average the
cells
that contain a number greater than 0. How do I do that?

How to average a column of numbers that are greater than 0?
 

If it's *most* logical to you, may I ask then, why would you use
Sumif(range,"0").
Seems illogical to *need* " add if it's greater then zero", *unless* there's
a stipulation to exclude negative values.

A simple:
Sum(range)/Countif(range,"0")
would be adequate without further qualification from the OP.

What do you think?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Is something else going to happen
"Sasa Stankovic" wrote in message
...
because I don't like array functions... it is not something special... I
just dont like to use them...
"Don Guillett" wrote in message
...
why?

--
Don Guillett
SalesAid Software

"Sasa Stankovic" wrote in message
...
I support this result as the most logical....
"bpeltzer" wrote in message
...
I'll admit my bias against array formulas and offer an alternative...
Average is simply sum/count. So =sumif(range,"0")/countif(range,"0")
will
average the positive values.

"JimNColorado" wrote:

I am trying to average a column of numbers. I want to only average the
cells
that contain a number greater than 0. How do I do that?

How to average a column of numbers that are greater than 0?
 

What if I want to do the same thing but I want to include negative values.

For example

A1 B1 C1 D1 E1
1000 1500 -400 300

So I want to average A1 to D1 but i also want to have the formula include
cell E1 because there may be times when E1 has a value - positive or negative.


"Ragdyer" wrote:

If it's *most* logical to you, may I ask then, why would you use
Sumif(range,"0").
Seems illogical to *need* " add if it's greater then zero", *unless* there's
a stipulation to exclude negative values.

A simple:
Sum(range)/Countif(range,"0")
would be adequate without further qualification from the OP.

What do you think?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Is something else going to happen
"Sasa Stankovic" wrote in message
...
because I don't like array functions... it is not something special... I
just dont like to use them...
"Don Guillett" wrote in message
...
why?

--
Don Guillett
SalesAid Software

"Sasa Stankovic" wrote in message
...
I support this result as the most logical....
"bpeltzer" wrote in message
...
I'll admit my bias against array formulas and offer an alternative...
Average is simply sum/count. So =sumif(range,"0")/countif(range,"0")
will
average the positive values.

"JimNColorado" wrote:

I am trying to average a column of numbers. I want to only average the
cells
that contain a number greater than 0. How do I do that?

How to average a column of numbers that are greater than 0?
 

You want the average of 5 cells, not just the AVERAGE of A1:E1?

=SUM(A1:E1)/5 returns 480

AVERAGE(A1:E1) returns 600 because AVERAGE ignores blanks.

Whatever suits you.


Gord Dibben MS Excel MVP


On Mon, 10 Dec 2007 16:58:00 -0800, StephenAccountant
wrote:

What if I want to do the same thing but I want to include negative values.

For example

A1 B1 C1 D1 E1
1000 1500 -400 300

So I want to average A1 to D1 but i also want to have the formula include
cell E1 because there may be times when E1 has a value - positive or negative.


"Ragdyer" wrote:

If it's *most* logical to you, may I ask then, why would you use
Sumif(range,"0").
Seems illogical to *need* " add if it's greater then zero", *unless* there's
a stipulation to exclude negative values.

A simple:
Sum(range)/Countif(range,"0")
would be adequate without further qualification from the OP.

What do you think?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Is something else going to happen
"Sasa Stankovic" wrote in message
...
because I don't like array functions... it is not something special... I
just dont like to use them...
"Don Guillett" wrote in message
...
why?

--
Don Guillett
SalesAid Software

"Sasa Stankovic" wrote in message
...
I support this result as the most logical....
"bpeltzer" wrote in message
...
I'll admit my bias against array formulas and offer an alternative...
Average is simply sum/count. So =sumif(range,"0")/countif(range,"0")
will
average the positive values.

"JimNColorado" wrote:

I am trying to average a column of numbers. I want to only average the
cells
that contain a number greater than 0. How do I do that?

How to average a column of numbers that are greater than 0?
 

No what I want is for the formula to ignore all Zero values.

So if only 4 out of the 5 cells have a value - positive or negative - i want
it to average the 4 cells not the 5

But if all 5 cells have values - positive or negative - i want it to average
the 5 cells.

"Gord Dibben" wrote:

You want the average of 5 cells, not just the AVERAGE of A1:E1?

=SUM(A1:E1)/5 returns 480

AVERAGE(A1:E1) returns 600 because AVERAGE ignores blanks.

Whatever suits you.


Gord Dibben MS Excel MVP


On Mon, 10 Dec 2007 16:58:00 -0800, StephenAccountant
wrote:

What if I want to do the same thing but I want to include negative values.

For example

A1 B1 C1 D1 E1
1000 1500 -400 300

So I want to average A1 to D1 but i also want to have the formula include
cell E1 because there may be times when E1 has a value - positive or negative.


"Ragdyer" wrote:

If it's *most* logical to you, may I ask then, why would you use
Sumif(range,"0").
Seems illogical to *need* " add if it's greater then zero", *unless* there's
a stipulation to exclude negative values.

A simple:
Sum(range)/Countif(range,"0")
would be adequate without further qualification from the OP.

What do you think?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Is something else going to happen
"Sasa Stankovic" wrote in message
...
because I don't like array functions... it is not something special... I
just dont like to use them...
"Don Guillett" wrote in message
...
why?

--
Don Guillett
SalesAid Software

"Sasa Stankovic" wrote in message
...
I support this result as the most logical....
"bpeltzer" wrote in message
...
I'll admit my bias against array formulas and offer an alternative...
Average is simply sum/count. So =sumif(range,"0")/countif(range,"0")
will
average the positive values.

"JimNColorado" wrote:

I am trying to average a column of numbers. I want to only average the
cells
that contain a number greater than 0. How do I do that?