Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
formula for negative and positive numbers in different columns | Excel Worksheet Functions | |||
How do I change a column of 500 numbers from positive to negative | Excel Discussion (Misc queries) | |||
How do I change the plus to a negative in a column of numbers? | Excel Worksheet Functions | |||
FORMULA TO ADD POSITIVE AND NEGATIVE NUMBERS, REGARDLESS OF SIGN | Excel Discussion (Misc queries) |