If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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! |
Thread Tools | |
Display Modes | |
|
|