Average of Numbers

[Zach]

Hello,

I would like to use this function:

=AVERAGE(O8:O263)

The problem is in column "O" I have both Positive and Negative numbers.

I would like to have the Average of the Positive numbers only in say cell
O265. How do I set up an IF statement to do this or any other way.

Thanks,

Zach

 

[David Hilberg]

Zach,

=SUMIF(O8:O263,">=0")/COUNTIF(O8:O263,">=0")

I'm assuming you want to include zeroes. If not, delete the equals
signs.

- David

 

[Dave Thomas]

You can use an array formula like:

=AVERAGE(IF(O8:O263>=0,O8:O263)). Press CTRL+SHIFT+ENTER after you type the
formula in. CTRL+SHIFT+ENTER, instead of simply ENTER, tells Excel the
formula is an array formula. You will see {=AVERAGE(IF(H1:H15>=0,H1:H15))}}
in the formula bar. The {} around the formula tells you the formula is an
array formula. If you change it say by pressing F2, the {} will disappear.
You never type in the {}'s.You must again enter it with CTRL+SHIFT+ENTER.
This formula averages 0's and positive numbers.

To average only the positive numbers (negatives and 0's excluded):
=AVERAGE(IF(H1:H15>0,H1:H15)) Press CTRL+SHIFT+ENTER

To average only the negative numbers (positives and 0's excluded):
=AVERAGE(IF(H1:H15<0,H1:H15)) Press CTRL+SHIFT+ENTER


You can do a lot of things with array formulas that otherwise would require
the use of many formulas, functions, cells or even VBA code. I suggest you
get a book like John Walkenbach's Excel 2003 Bible (ISBN: 0-7645-3967-1) or
his Excel 2007 Bible (ISBN: 0-4700-4403-9). Both books are $39.99 at Barnes
& Noble. Walkenbach will take you through Excel. This book is "must reading"
if you are going to work with Excel.

 

[Dave Thomas]

You can use an array formula like:

=AVERAGE(IF(O8:O263>=0,O8:O263)). Press CTRL+SHIFT+ENTER after you type the
formula in. CTRL+SHIFT+ENTER, instead of simply ENTER, tells Excel the
formula is an array formula. You will see {=AVERAGE(IF(O8:O263>=0,O8:O263))}
in the formula bar. The {} around the formula tells you the formula is an
array formula. If you change it say by pressing F2, the {} will disappear.
You never type in the {}'s.You must again enter it with CTRL+SHIFT+ENTER.
This formula averages 0's and positive numbers.

To average only the positive numbers (negatives and 0's excluded):
=AVERAGE(IF(O8:O263>=0,O8:O263)) Press CTRL+SHIFT+ENTER

To average only the negative numbers (positives and 0's excluded):
=AVERAGE(IF(O8:O263>=0,O8:O263)) Press CTRL+SHIFT+ENTER


You can do a lot of things with array formulas that otherwise would require
the use of many formulas, functions, cells or even VBA code. I suggest you
get a book like John Walkenbach's Excel 2003 Bible (ISBN: 0-7645-3967-1) or
his Excel 2007 Bible (ISBN: 0-4700-4403-9). Both books are $39.99 at Barnes
& Noble. Walkenbach will take you through Excel. This book is "must reading"
if you are going to work with Excel.

 

[Dave Thomas]

You can use an array formula like:

=AVERAGE(IF(O8:O263>=0,O8:O263)). Press CTRL+SHIFT+ENTER after you type the
formula in. CTRL+SHIFT+ENTER, instead of simply ENTER, tells Excel the
formula is an array formula. You will see {=AVERAGE(IF(O8:O263>=0,O8:O263))}
in the formula bar. The {} around the formula tells you the formula is an
array formula. If you change it say by pressing F2, the {} will disappear.
You never type in the {}'s.You must again enter it with CTRL+SHIFT+ENTER.
This formula averages 0's and positive numbers.

To average only the positive numbers (negatives and 0's excluded):
=AVERAGE(IF(O8:O263>0,O8:O263)) Press CTRL+SHIFT+ENTER

To average only the negative numbers (positives and 0's excluded):
=AVERAGE(IF(O8:O263<0,O8:O263)) Press CTRL+SHIFT+ENTER


You can do a lot of things with array formulas that otherwise would require
the use of many formulas, functions, cells or even VBA code. I suggest you
get a book like John Walkenbach's Excel 2003 Bible (ISBN: 0-7645-3967-1) or
his Excel 2007 Bible (ISBN: 0-4700-4403-9). Both books are $39.99 at Barnes
& Noble. Walkenbach will take you through Excel. This book is "must reading"
if you are going to work with Excel.