Formula to average ignoring negatives?
 

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

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!

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!

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.

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

I responded to your post, not the OP's. Your posted concern was that
Dave Patterson's formula treated empty cells as zeros. My post resolves
that.

Jerry

Ragdyer wrote:

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

Peterson. Dave Peterson.

<vbg

(in a 007 voice)

"Jerry W. Lewis" wrote:

I responded to your post, not the OP's. Your posted concern was that
Dave Patterson's formula treated empty cells as zeros. My post resolves
that.

Jerry

<"My post resolves that."

'Fraid it *doesn't*, Jerry!

Dave's new one does though.
--

Regards,

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

"Jerry W. Lewis" wrote in message
...
I responded to your post, not the OP's. Your posted concern was that
Dave Patterson's formula treated empty cells as zeros. My post resolves
that.

Jerry

Ragdyer wrote:

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

That's why I always try to use the first name only.
Especially with Debra!<g
--

Regards,

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

"Dave Peterson" wrote in message
...
Peterson. Dave Peterson.

<vbg

(in a 007 voice)

"Jerry W. Lewis" wrote:

I responded to your post, not the OP's. Your posted concern was that
Dave Patterson's formula treated empty cells as zeros. My post resolves
that.

Jerry

But Gerry's <VBG does answer the question in the subject--just not the question
in the body.



RagDyeR wrote:

<"My post resolves that."

'Fraid it *doesn't*, Jerry!

Dave's new one does though.
--

Regards,

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

"Jerry W. Lewis" wrote in message
...
I responded to your post, not the OP's. Your posted concern was that
Dave Patterson's formula treated empty cells as zeros. My post resolves
that.

Jerry

Ragdyer wrote:

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

--

Dave Peterson

You said "Those formulas don't seem to work right if there are blank
(MT) cells in the range!" What I posted corrects the behavior with blank
cells. I agree that the OP's body (inconsistent with his subject) calls
for a different treatment of negative numbers, but it was not his post
that I responded to.

Jerry

RagDyeR wrote:

<"My post resolves that."

'Fraid it *doesn't*, Jerry!

Dave's new one does though.

Jerry,

You're responding to my post, ... BUT ... quoting statements made by DAVE,
*not me*!

I *misunderstood* your statement:
<"My post resolves that."
To mean that your formula resolved the mt cell issue *in the context* of the
OP.

I now understand you to mean that it *only* resolves averaging across a
range, and not allowing mt cells to be included in the average calculation.

You must admit, that this does *exactly* the same thing:
=AVERAGE(A1:A10)
And is much more simpler and concise.

However, you may say that you were addressing my statement about DAVE'S
formulas not working when mt cells were in the range.
BUT ... DAVE'S formulas were *aimed* at answering the OP's request, as was
my originally posted suggestion, 4 minutes after DAVE'S original post, and 2
minutes before his addendum post.

So, everything revolved around the concept of averaging positive and
negative numbers as if all numbers were positive.
I might have introduced the mt cell variable, but still *within* the
"positive - negative" hypothesis.

So when you posted a "resolving" formula in response to my comment about
DAVE'S formula, I'm sure you can understand my conviction that your intent
was to suggest something that was pertinent to the thread (OP).

I'm sorry for the misunderstanding, but I'm sure you can easily appreciate
the rationale behind it's occurrence.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jerry W. Lewis" wrote in message
...
You said "Those formulas don't seem to work right if there are blank
(MT) cells in the range!" What I posted corrects the behavior with blank
cells. I agree that the OP's body (inconsistent with his subject) calls
for a different treatment of negative numbers, but it was not his post
that I responded to.

Jerry

RagDyeR wrote:

<"My post resolves that."

'Fraid it *doesn't*, Jerry!

Dave's new one does though.

Ragdyer wrote:

Jerry,

You're responding to my post, ... BUT ... quoting statements made by DAVE,
*not me*!


I am confused. "Those formulas don't seem to work right if there are
blank (MT) cells in the range!" is a direct quote from a post that
claims to be from you on 29 Dec (Google lists the post time as 6:42 pm)

I *misunderstood* your statement:
<"My post resolves that."
To mean that your formula resolved the mt cell issue *in the context* of the
OP.

I now understand you to mean that it *only* resolves averaging across a
range, and not allowing mt cells to be included in the average calculation.

You must admit, that this does *exactly* the same thing:
=AVERAGE(A1:A10)
And is much more simpler and concise.


No, that would average positive and negative numbers. The formula I
posted would average only positive numbers (as Dave Peterson's formula
did), but without coercing empty cells to zero (which seemed to be your
only problem with Dave Peterson's formula).

....
I'm sorry for the misunderstanding, but I'm sure you can easily appreciate
the rationale behind it's occurrence.

Agreed, this back an forth appears to have been miscommunication all the
way around.

Happy New Year!
Jerry