How can I change all negative values in a column to = 0?

[dbsavoy]

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!

 

[Bob Phillips]

=AVERAGE(IF(A1:A20>0,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)

 

[Biff]

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:A10>0,A1:A10))

To exclude negatives *BUT* include zero's:

=AVERAGE(IF((ISNUMBER(A1:A10))*(A1:A10>=0),A1:A10))

Biff

 

[DKS]

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.