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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dbsavoy - 21 Aug 2006 19:52 GMT

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 to this Message

Bob Phillips - 21 Aug 2006 20:07 GMT

=AVERAGE(IF(A1:A20>0,A1:A20))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> 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
[quoted text clipped - 4 lines]
> I would much appreciate any help on this.
> Thx!

Reply to this Message

Biff - 21 Aug 2006 20:10 GMT

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

> 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
[quoted text clipped - 7 lines]
> I would much appreciate any help on this.
> Thx!

Reply to this Message

DKS - 21 Aug 2006 20:14 GMT

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.

Reply to this Message