Extracting data from two columns have dependencies between them

[bines53]

Hello Friends ,

Very complex question.
Columns A and C, data are numbers, each column has negative and positive numbers.
I want to extract data criteria are: two new columns will have similar data in each row.
The difference between them, one negative and the other positive., And the summary of all the new columns will be zero.
In the example I attached, new columns are H and J.

Thank you

 

[Xen]

Why are some rows ignored? Like, 2, 3, 10, 18, 20, 24 and 26. And while all the other rows have one number from column A or C, row 21 has nothing to do with number from A or C (A+C gives 3, but it is still the only exception).

What is the logic here?

Edit: Ok, I got part about rows that are ignored: =OR(AND(A1>0;C1<0);AND(A1<0;C1>0)), this ignores rows that are ignored in your example.

 

[bines53]

Hi Xen ,

Column B represents the strike price ,
I want summarized synthetic contracts, purchase and sale.

david.

 

[NARAYANK991]

Hi David ,

There is some inconsistency in the results for rows 19 and 21.

Try this ;

=IF(OR(ISBLANK($A1),ISBLANK($C1),SIGN($A1)=SIGN($C1)),"",SIGN(A1)*MIN(ABS($A1),ABS($C1)))

=IF($H1="","",SIGN(C1)*MIN(ABS($A1),ABS($C1)))

Narayan

 

[bines53]

Hi Narayan ,

A good idea,

The solution should be, each cell refers to any cell in column.

david.

 

[bines53]

Hi Narayan ,
After further thought, I think your answer is perfect !

Now I have to subtract a certain row, two units of each column, so that total two columns will
Zero. How do I find the line that could be subtracted from it?

Thank you !

 

[NARAYANK991]

Hi David ,

Something like this ?

Narayan

 

[bines53]

Hi Narayan ,

When summarizing the nonzero column, I want to open another column where to get the data to be equal
Zero. Formulation will recognize the first line you can reduce it to the totaling will be zero.

David.

 

[NARAYANK991]

Hi David ,

See if this works.

Narayan

 

[bines53]

Hi Narayan ,

I appreciate your help !

I changed the value in cell C4 to minus 4, needs a small correction formula .

Thank you !

David