What formula would I use to average a group of numbers and ignore the
negatives? For instance, if I'm averaging +8 and -6, I want the
result to be 7, not 1. Thanks!

That's not really ignoring the negatives, but this worked ok for me:

=AVERAGE(ABS(A1:A10))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

If you really wanted to ignore the negatives (pretend that they didn't exist):
=AVERAGE(IF(A1:A100,A1:A10))
This is also an array formula.

Steve wrote:

What formula would I use to average a group of numbers and ignore the
negatives? For instance, if I'm averaging +8 and -6, I want the
result to be 7, not 1. Thanks!

--

Dave Peterson

And this array formula:
=AVERAGE(IF(A1:A100,A1:A10))
ignores 0's as well as negatives.

=AVERAGE(IF(A1:A10=0,A1:A10))
(array entered still, would ignore just the negatives)

==
Yeah, you didn't ask about this. I know. But I had to correct my error.

Dave Peterson wrote:

That's not really ignoring the negatives, but this worked ok for me:

=AVERAGE(ABS(A1:A10))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

If you really wanted to ignore the negatives (pretend that they didn't exist):
=AVERAGE(IF(A1:A100,A1:A10))
This is also an array formula.

Steve wrote:

What formula would I use to average a group of numbers and ignore the
negatives? For instance, if I'm averaging +8 and -6, I want the
result to be 7, not 1. Thanks!

--

Dave Peterson

--

Dave Peterson

Dave,

Those formulas don't seem to work right if there are blank (MT) cells in the
range!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
And this array formula:
=AVERAGE(IF(A1:A100,A1:A10))
ignores 0's as well as negatives.

=AVERAGE(IF(A1:A10=0,A1:A10))
(array entered still, would ignore just the negatives)

==
Yeah, you didn't ask about this. I know. But I had to correct my error.

Dave Peterson wrote:

That's not really ignoring the negatives, but this worked ok for me:

=AVERAGE(ABS(A1:A10))
This is an array formula. Hit ctrl-shift-enter instead of enter. If
you do it
correctly, excel will wrap curly brackets {} around your formula.
(don't type
them yourself.)

If you really wanted to ignore the negatives (pretend that they didn't
exist):
=AVERAGE(IF(A1:A100,A1:A10))
This is also an array formula.

Steve wrote:

What formula would I use to average a group of numbers and ignore the
negatives? For instance, if I'm averaging +8 and -6, I want the
result to be 7, not 1. Thanks!

--

Dave Peterson

--

Dave Peterson

=AVERAGE(IF(ISNUMBER(A1:A10)*(A1:A10=0),A1:A10))

Jerry

Ragdyer wrote:

Dave,

Those formulas don't seem to work right if there are blank (MT) cells in the
range!

Jerry,
Follow this scenario:
A1:A10
5,-6,2,13,2,-3,25,mt,mt,mt
OP's request - Average as if all numbers were positive.
Sum would equal 56.
Average would equal 8.

Your formula yields 9.4

Same scenario,
Dave's formulas return 5.6 - 5.875 - 9.4

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jerry W. Lewis" wrote in message
...
=AVERAGE(IF(ISNUMBER(A1:A10)*(A1:A10=0),A1:A10))

Jerry

Ragdyer wrote:

Dave,

Those formulas don't seem to work right if there are blank (MT) cells in
the
range!

I didn't consider empty cells.

Another way that should work, though:

=AVERAGE(IF(ISNUMBER(A1:A10),ABS(A1:A10)))

(still array entered)

Ragdyer wrote:

Dave,

Those formulas don't seem to work right if there are blank (MT) cells in the
range!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
And this array formula:
=AVERAGE(IF(A1:A100,A1:A10))
ignores 0's as well as negatives.

=AVERAGE(IF(A1:A10=0,A1:A10))
(array entered still, would ignore just the negatives)

==
Yeah, you didn't ask about this. I know. But I had to correct my error.

Dave Peterson wrote:

That's not really ignoring the negatives, but this worked ok for me:

=AVERAGE(ABS(A1:A10))
This is an array formula. Hit ctrl-shift-enter instead of enter. If
you do it
correctly, excel will wrap curly brackets {} around your formula.
(don't type
them yourself.)

If you really wanted to ignore the negatives (pretend that they didn't
exist):
=AVERAGE(IF(A1:A100,A1:A10))
This is also an array formula.

Steve wrote:

What formula would I use to average a group of numbers and ignore the
negatives? For instance, if I'm averaging +8 and -6, I want the
result to be 7, not 1. Thanks!

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Dave Peterson wrote:
=AVERAGE(ABS(A1:A10))
This is an array formula. Hit ctrl-shift-enter instead of enter.

Thanks everyone. This one looks to be the easiest, and seems to do
the trick.

One way is with this array formula:

=SUM(ABS(A1:A10))/COUNT(A1:A10)

Must be entered with CSE (<Ctrl <Shift <Enter),
Which will make XL *automatically* enclose the formula in curly brackets,
Which cannot be done manually.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Steve" wrote in message
...
What formula would I use to average a group of numbers and ignore the
negatives? For instance, if I'm averaging +8 and -6, I want the
result to be 7, not 1. Thanks!