Ifs / if(and

[greenfortyguy]

Hello,

I am trying to write a formula that will perform the following function, and drag down a column:

• IF the value in column K is <0, the value in the same row of column I is the equivalent positive.
• The values in column I remain as a maximum.

This is an example of the result I'm looking for:


[TABLE="width: 365"]
<colgroup><col span="2" style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]25,034[/TD]
[TD="align: center"]4,966[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]24,991[/TD]
[TD="align: center"]5,009[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]24,946[/TD]
[TD="align: center"]5,054[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]24,873[/TD]
[TD="align: center"]5,127[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]24,820[/TD]
[TD="align: center"]5,180[/TD]
[/TR]
[TR]
[TD="align: center"]3682[/TD]
[TD="align: center"]30,000[/TD]
[TD="align: center"]-3,682[/TD]
[/TR]
[TR]
[TD="align: center"]3682[/TD]
[TD="align: center"]33,628[/TD]
[TD="align: center"]-3,628[/TD]
[/TR]
[TR]
[TD="align: center"]3682[/TD]
[TD="align: center"]33,561[/TD]
[TD="align: center"]67[/TD]
[/TR]
[TR]
[TD="align: center"]3682[/TD]
[TD="align: center"]33,495[/TD]
[TD="align: center"]133[/TD]
[/TR]
[TR]
[TD="align: center"]3682[/TD]
[TD="align: center"]29,759[/TD]
[TD="align: center"]187[/TD]
[/TR]
[TR]
[TD="align: center"]3682[/TD]
[TD="align: center"]29,700[/TD]
[TD="align: center"]246[/TD]
[/TR]
</tbody>[/TABLE]

Thanks,

Guy

 

[Joe4]

Not sure what this means:

• The values in column I remain as a maximum.

Where are the zeroes in the other rows coming from?

If you just want it to either be the opposite of column K if column K is negative, and 0 otherwise, then try this in cell I2:

=IF(K2<0,0-K2,0)

 

[greenfortyguy]

Hi Joe,

What I meant was, if a value appears in column I as a result of the value in column K being negative, that value remains in all the cells below even if the values in column K are positive.

i.e. in my example,

• the first negative value in column K is -3,682. As a result, +3,682 appears in column I.
• the next value in column K is -3,628. However, -3,682 remains the value in column I.
• the next value in column K is +67. -3,682 remains in column I.

This would continue, unless a more negative value than -3,682 appeared in column K. If this were to happen, that value would then be repeated.

Thanks,

Guy

EDIT: The zeros are coming as the result of a potential use of the formula =IF(K2<0, -K2, 0), which is essentially the formula you stated.

 

[Joe4]

Assuming that your data starts on row 2, enter this in I2 and copy down for all rows:

=0-MIN(0,$K$2:K2)