Formula to average ignoring negatives?

[Steve]

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]

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:A10>0,A1:A10))
This is also an array formula.

 

[Ragdyer]

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.

 

[Dave Peterson]

And this array formula:
=AVERAGE(IF(A1:A10>0,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.

 

[Ragdyer]

Dave,

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

 

[Jerry W. Lewis]

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

Jerry

 

[Ragdyer]

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

 

[Steve]

=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.

 

[Dave Peterson]

I didn't consider empty cells.

Another way that should work, though:

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

(still array entered)

Dave,

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

 

[Jerry W. Lewis]

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

 

[Dave Peterson]

Peterson. Dave Peterson.

<vbg>

(in a 007 voice)

 

[RagDyeR]

<"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 !
--------------------------------------------------------------------

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]

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 !
--------------------------------------------------------------------

Peterson. Dave Peterson.

<vbg>

(in a 007 voice)

 

[Dave Peterson]

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

 

[Jerry W. Lewis]

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]

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.

 

[Jerry W. Lewis]

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

 

[xyz123]

Hello

Looking at the same problem that is how to average a group of numbers (which includes both positive and negative numbers) in their absolute values only (So the average of +8 and -6 makes seven).
I have tried what was recommneded above, however, I have a rather complex spreadsheet where 197 cells for the each average is picked from different places in the spread sheet. (Each of the 197 fields contain a formula for two cells subtracted from each other). Normally I would happily go through and apply ABS to these fields but my spreadsheet goes to 785 and EX so it is huge and I need a lot of averages. Tried the following with just a few of the 197 values =AVERAGE(ABS(AE3,AE7,AE11,AE15,AE19,AE23)) but it doens't work. Anyone got some good ideas, please? Perhaps an alternative solution e.g. how to add ABS to everycell without changing existing formulas in the spreadsheet (all cells have different formulas so cannot simply use replace)