Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminate negative numbers from stats
Hello,
I am tracking days for project completions. I do this my subtracting the start date from the completion date. The days of the uncompleted projects are negative numbers. Among other stats, I'd like to do averages on completed tasks. What's the best way to eliminate the negative numbers from the averages? Could this be done with a pivot table? I know a pivot table would do a lot of what I'd like done. Thanks for your help, Ellen |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminate negative numbers from stats
Ellen,
If the days (some positive, some negative) are in C2:C10), you could use: =AVERAGE(IF(C2:C10=0,C2:C10,"")) This is an array formula, and must be committed with Ctrl-Shift-Enter, not just Enter, any time it's been edited. It includes zero days. Change = to to exclude zero days. -- Earl Kiosterud www.smokeylake.com Note: Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "EllenM" wrote in message ... Hello, I am tracking days for project completions. I do this my subtracting the start date from the completion date. The days of the uncompleted projects are negative numbers. Among other stats, I'd like to do averages on completed tasks. What's the best way to eliminate the negative numbers from the averages? Could this be done with a pivot table? I know a pivot table would do a lot of what I'd like done. Thanks for your help, Ellen |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminate negative numbers from stats
Thanks, Earl. Your formula was quite helpful.
"Earl Kiosterud" wrote: Ellen, If the days (some positive, some negative) are in C2:C10), you could use: =AVERAGE(IF(C2:C10=0,C2:C10,"")) This is an array formula, and must be committed with Ctrl-Shift-Enter, not just Enter, any time it's been edited. It includes zero days. Change = to to exclude zero days. -- Earl Kiosterud www.smokeylake.com Note: Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thread gets messy. When in Rome... ----------------------------------------------------------------------- "EllenM" wrote in message ... Hello, I am tracking days for project completions. I do this my subtracting the start date from the completion date. The days of the uncompleted projects are negative numbers. Among other stats, I'd like to do averages on completed tasks. What's the best way to eliminate the negative numbers from the averages? Could this be done with a pivot table? I know a pivot table would do a lot of what I'd like done. Thanks for your help, Ellen |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminate negative numbers from stats
Sat, 2 Jun 2007 20:58:39 -0400 from Earl Kiosterud
: "EllenM" wrote in message ... I am tracking days for project completions. I do this my subtracting the start date from the completion date. The days of the uncompleted projects are negative numbers. Among other stats, I'd like to do averages on completed tasks. What's the best way to eliminate the negative numbers from the averages? If the days (some positive, some negative) are in C2:C10), you could use: =AVERAGE(IF(C2:C10=0,C2:C10,"")) This is an array formula, and must be committed with Ctrl-Shift-Enter, not just Enter, any time it's been edited. It includes zero days. Change = to to exclude zero days. Do you *want* the days of the uncompleted projects to be negative numbers? If you'd prefer for them to be blanks, then replace your subtraction: =B2-A2 with =if(B2=A2,B2-A2,"") Then your average will work just fine because =AVERAGE ignores non- numeric cells. No array formulas or other special stuff needed. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Eliminate negative numbers from stats
Thanks, Stan. Yes I want the uncompleted project days to be blank.
"Stan Brown" wrote: Sat, 2 Jun 2007 20:58:39 -0400 from Earl Kiosterud : "EllenM" wrote in message ... I am tracking days for project completions. I do this my subtracting the start date from the completion date. The days of the uncompleted projects are negative numbers. Among other stats, I'd like to do averages on completed tasks. What's the best way to eliminate the negative numbers from the averages? If the days (some positive, some negative) are in C2:C10), you could use: =AVERAGE(IF(C2:C10=0,C2:C10,"")) This is an array formula, and must be committed with Ctrl-Shift-Enter, not just Enter, any time it's been edited. It includes zero days. Change = to to exclude zero days. Do you *want* the days of the uncompleted projects to be negative numbers? If you'd prefer for them to be blanks, then replace your subtraction: =B2-A2 with =if(B2=A2,B2-A2,"") Then your average will work just fine because =AVERAGE ignores non- numeric cells. No array formulas or other special stuff needed. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I eliminate negative values in a spreadsheet ? | Excel Discussion (Misc queries) | |||
Set negative numbers to zero. Do not calculate with negative valu | Excel Discussion (Misc queries) | |||
change 2000 cells (negative numbers) into positive numbers | Excel Worksheet Functions | |||
hOW TO ELIMINATE COL. ABC ROW AND ROW NUMBERS IN EXCEL SHEET | Setting up and Configuration of Excel | |||
macro to eliminate repeating account numbers | New Users to Excel |