Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I change all negative values in a column to = 0?
I'm trying to calculate an average of data, but need to ignore negative
numbers in the range from which I'm taking the average. I'd like to either format cells to calculate based on displayed values rather than actual values OR use something like an IF function to change negative values into zeros or NA. I would much appreciate any help on this. Thx! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I change all negative values in a column to = 0?
=AVERAGE(IF(A1:A200,A1:A20))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "dbsavoy" wrote in message ... I'm trying to calculate an average of data, but need to ignore negative numbers in the range from which I'm taking the average. I'd like to either format cells to calculate based on displayed values rather than actual values OR use something like an IF function to change negative values into zeros or NA. I would much appreciate any help on this. Thx! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I change all negative values in a column to = 0?
Hi!
To get the average that excludes negative values *AND* zero values: Both formulas entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(A1:A100,A1:A10)) To exclude negatives *BUT* include zero's: =AVERAGE(IF((ISNUMBER(A1:A10))*(A1:A10=0),A1:A10) ) Biff "dbsavoy" wrote in message ... I'm trying to calculate an average of data, but need to ignore negative numbers in the range from which I'm taking the average. I'd like to either format cells to calculate based on displayed values rather than actual values OR use something like an IF function to change negative values into zeros or NA. I would much appreciate any help on this. Thx! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I change all negative values in a column to = 0?
I would add a dummy column next to the column you want to be averaged.
Let us assume that your real data is in column A, then I would add a column B with following formula in cell B1 = ABS(A1). This formula of course is to be copied in the entire range of column B that you need to be averaged. Thereafter apply your average formula on column B. You can always Hide Column B to avoid display of dummy data. The average formula can be displayed in column A or any other visibile column. "dbsavoy" wrote: I'm trying to calculate an average of data, but need to ignore negative numbers in the range from which I'm taking the average. I'd like to either format cells to calculate based on displayed values rather than actual values OR use something like an IF function to change negative values into zeros or NA. I would much appreciate any help on this. Thx! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
how do i sum up a column of time values but ignore negative values | New Users to Excel | |||
Pbl calculating US$ values | Excel Discussion (Misc queries) |