Formula to ignore negative numbers in a column?
I have a lengthy spreadsheet with several columns of numerical data. Some
cells have positive numbers and others have negative numbers. My goal is to get both a column sum and average--but only of only those cells with a positive number--i would like to exclude the negative numbers from my calculations. Are there formulas that will so this for me? -- Tia, Education and Documentation Specialist |
Formula to ignore negative numbers in a column?
Tia,
Sum: =SUMIF(D:D,"0") Average: =SUMIF(D:D,"0")/COUNTIF(D:D,"0") HTH, Bernie MS Excel MVP "Tia" wrote in message ... I have a lengthy spreadsheet with several columns of numerical data. Some cells have positive numbers and others have negative numbers. My goal is to get both a column sum and average--but only of only those cells with a positive number--i would like to exclude the negative numbers from my calculations. Are there formulas that will so this for me? -- Tia, Education and Documentation Specialist |
Formula to ignore negative numbers in a column?
Try something like this:
With values in cells A1:A10 Greater than zero: SUM B1: =SUMIF(A1:A10,"0") AVERAGE B2: =SUMIF(A1:A10,"0")/COUNTIF(A1:A10,"0") Greater than or equal to zero: SUM B1: =SUMIF(A1:A10,"=0") AVERAGE B2: =SUMIF(A1:A10,"=0")/COUNTIF(A1:A10,"=0") Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Tia" wrote: I have a lengthy spreadsheet with several columns of numerical data. Some cells have positive numbers and others have negative numbers. My goal is to get both a column sum and average--but only of only those cells with a positive number--i would like to exclude the negative numbers from my calculations. Are there formulas that will so this for me? -- Tia, Education and Documentation Specialist |
Formula to ignore negative numbers in a column?
Another option for Average using an Array formula:
=AVERAGE(IF(Rng0,Rng)) Rng being a range name of say A1:A10 -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Tia" wrote in message ... I have a lengthy spreadsheet with several columns of numerical data. Some cells have positive numbers and others have negative numbers. My goal is to get both a column sum and average--but only of only those cells with a positive number--i would like to exclude the negative numbers from my calculations. Are there formulas that will so this for me? -- Tia, Education and Documentation Specialist |
All times are GMT +1. The time now is 12:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com