omit negitive #s

[mechpm]

I have a work sheet that uses columns B thru H and rows 2 thru 11 to calculate data.

When any number in column E returns a negative # I need the entire row to return 0 or blank so that rest of the columns will not calculate these #s. I’ve tried some what if formulas but can’t get it to work....

I’m sure this is doable maybe an array type formula. However, I’ve just learned of this capability and they are quite the mystery.


Thx,

Rw
,

[Mark W.]

On 2002-05-17 15:29, mechpm wrote:
I have a work sheet that uses columns B thru H and rows 2 thru 11 to calculate data.

When any number in column E returns a negative # I need the entire row to return 0 or blank so that rest of the columns will not calculate these #s. I’ve tried some what if formulas but can’t get it to work....

I’m sure this is doable maybe an array type formula. However, I’ve just learned of this capability and they are quite the mystery.


Thx,

Rw
,

Instead of zeroing or blanking the rows why not use...

=SUMIF(E2:E11,">=0",sum_range)

[ This Message was edited by: Mark W. on 2002-05-17 15:36 ]

[mechpm]

On 2002-05-17 15:35, Mark W. wrote:

Instead of zeroing or blanking the rows why not use...

=SUMIF(E2:E11,">=0",sum_range)

[ This Message was edited by: Mark W. on 2002-05-17 15:36 ]

This will exclude the negative # s in column E. However, I have other columns were the #s(cells) are not retuned negative, or are input cells. These #s(cells) need to be omitted per each negative condition in column E.

Let’s say E 5 and E 7 are negative then I need all cells in rows 5 & 7 to return blank or 0
So that they are not used in other calculations in columns B, C & H.

I have =SUMIF(E2:E11,">=0") at the bottom of column E now and I have conditional formatting that changes the colors of the rows when any #s (cells) in column E are Negative. However, I have to delete the cells and then rewrite the formulas when I enter new data.

I am not familiar with sum_range. How can I apply this to get the above result? You may have to be more deliberate as I am a meager apprentice in the excel environment.

I am looking in walkingbachs book 2002 formulas for sum-range however, any help with this
would be greatly appreciated.

This complex and unfathomable program has become I bit of a fixation. Do you suppose there is a 12 step process that combats the effects?

Thx

R W

[kinkyparamour]

If you find that 12 step program I believe a lot of us will be forced to join......

Denny

[mechpm]

On 2002-05-17 18:00, kinkyparamour wrote:
If you find that 12 step program I believe a lot of us will be forced to join......

Denny

I believe it was yogi anand (no let me rephrase that THE Yogi Anand the man with the patients of steel) who used the phrase Excel enthusiast one evening. Hey, shroud it if you like.

Can you say monomaniac..

[Mark W.]

For a definition of "sum_range" take a look at the Excel Help topic for "SUMIF worksheet function".