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. |
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. |
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 |
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. |
All times are GMT +1. The time now is 12:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com