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:

I	J	K
0	25,034	4,966
0	24,991	5,009
0	24,946	5,054
0	24,873	5,127
0	24,820	5,180
3682	30,000	-3,682
3682	33,628	-3,628
3682	33,561	67
3682	33,495	133
3682	29,759	187
3682	29,700	246

<colgroup><col span="2" style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

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)