Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average a continuous group of numbers and negative numbers are 0
I'm trying to get an average of a group of numbers. There are some negative
numbers and I want them to be considered 0. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average a continuous group of numbers and negative numbers are 0
=SUMIF(A1:A6,"0")/COUNT(A1:A6)
Will only add up numbers that are greater than 0, then divide by the total number of numbers :) -- John C "Dumbfounded" wrote: I'm trying to get an average of a group of numbers. There are some negative numbers and I want them to be considered 0. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average a continuous group of numbers and negative numbers are 0
Just for the fun of it:
array-enter =AVERAGE((A1:A60)*A1:A6) Regards, Bernd |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
average a continuous group of numbers and negative numbers are 0
If you want the negatives to count as 0 (as opposed to being excluded
from the average altogether) use =AVERAGE(IF(A1:A5=0,0,A1:A5)) If you want to exclude negatives from the average, use =AVERAGE(IF(A1:A5<0,FALSE,A1:A5)) Note that averaging a zero is very different that excluding the value from the average. These are both array formulas, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel wil display the formula enclosed in curly braces. See www.cpearson.com/Excel/ArrayFormuals.aspx for more info about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 15 Oct 2008 13:14:02 -0700, Dumbfounded wrote: I'm trying to get an average of a group of numbers. There are some negative numbers and I want them to be considered 0. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel, change column of negative numbers to positive numbers? | New Users to Excel | |||
change 2000 cells (negative numbers) into positive numbers | Excel Worksheet Functions | |||
Excel Formula - Add column of numbers but ignore negative numbers | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions |