Negative Average

**Kypsis** · 12-13-2010, 08:51 AM

Greetings all, little probleem I ran into:
Lets say I have a list with values, these values are totally random, but 2 things are certain- they are either positive or negative. My problem would be, how can I take both postive and negative arithmetic averages from them ? The negative average is what I have more in my mind, some sort of negative average command ? Thank you for taking your time reading this

**Andy Pope** · 12-13-2010, 08:54 AM

Do mean calculate an average based on only positive or negative values?

**Kypsis** · 12-13-2010, 08:56 AM

yes, basically, i mean make 2 cells for the answers: one for postive average, other for negative average, what I brainstormed was: IF less then zero, I didnt manage

**Andy Pope** · 12-13-2010, 09:01 AM

You need to use an array formula, comit with CTRL+SHIFT+ENTER

data in A1:A16

positives
=AVERAGE(IF(A1:A16>=0,A1:A16))

negatives
=AVERAGE(IF(A1:A16<0,A1:A16))

**squiggler47** · 12-13-2010, 09:05 AM

wouldnt :-

=AVERAGEIF(A1:A16,">=0")

negatives
=AVERAGEIF(A1:A16,"<0")

be simpler?

**Kypsis** · 12-13-2010, 09:06 AM

seems to work thank you, but, one issue, the negative averages are presented with +(pluss), i would like them to be with - (minus), tried format cells, but didnt get a minus in front of the average

**Andy Pope** · 12-13-2010, 09:06 AM

If you have xl2007 or above then yes.

**squiggler47** · 12-13-2010, 09:07 AM

just add - before the Average

=-AVERAGE(IF(A1:A16<0,A1:A16))

or
=-AVERAGEIF(A1:A16,"<0")

**Andy Pope** · 12-13-2010, 09:08 AM

I don't get pluses for the negative average in my example. Are you sure you are doing it correctly?

**Kypsis** · 12-13-2010, 09:12 AM

no no, everything is working fine now, thanks for help guys, just wanted to present my negative average results with a negative sign, cheers for the help