Knocking-off of opposite balances within a number set

**a703** · 01-10-2012, 10:53 AM

W.r.t. my earlier thread posted earlier today, I feel that I have solved the problem at least partially. It primarily involves checking if the given value is the minimum OR maximum absolute value among the data set, checking the sign convention and then some basic arithmetic operations.

Assuming 3 random values in cells A1 to C1 , I am able to select the min and max numbers with the following formulae :

MIN =INDEX(A1:C1,MATCH(MIN(ABS(A1:C1-"0")),ABS(A1:C1-"0"),0))
MAX =INDEX(A1:C1,MATCH(MAX(ABS(A1:C1-"0")),ABS(A1:C1-"0"),0))

How this works is that this essentially reduces the number from which the closest number is to be ascertained, in our case "0", takes the absolute value and then uses the match and index functions to bring back the value of that min/max number.

Now, I intend on developing the following chain of logic but somehow I cannot get the syntax to work.

If(sign(MAX value)<>sign(MIN value) AND A1=(MAX value), A+(MIN value) ,if(sign(MAX value)<>sign(MIN value) AND A1=(MIN value),0,A1)

The above means:
If the sign of the max value and min value are opposite and A1 is the MAX value, then set off the MAX value with the MIN value.
If the sign of the max value and min value are opposite and A1 is the MIN value, then reduce the MIN value to 0.
If the value is neither of the two, then hold on to the same original value.

Some help with the final working syntax of the above logic would be greatly appreciated. Also, any shorter and smarter logic would be very welcome.

Thanks

A

**zbor** · 01-10-2012, 10:54 AM

Upload example workbook please

**a703** · 01-10-2012, 11:08 AM

minmaxupload10012012.xls

for your reference.

**Pepe Le Mokko** · 01-10-2012, 11:26 AM

I suggest you change your title according to forum rules before the mods get at you

**micope21** · 01-10-2012, 12:02 PM

Remove wrong place

**zbor** · 01-11-2012, 03:21 AM

Try this.

Check logic for 3rd and 13th row

**a703** · 01-11-2012, 04:54 AM

I completely appreciate the efforts you have taken to set up the logic and it works fine too.

But I am afraid there is already an algo for the same results i.e. finding the min and max value. Please check the columns J and K in the uploaded excel sheet. The results match, cross-check column L. Honestly, this logic is getting quite cumbersome and I was thinking of a work-around for this.

1. creating a ranking matrix of the numbers according to their absolute values.
2. For zeros, the rank assigned would be 0.
3. The minimum value, Rank = 1 and maximum value, Rank = n (for a set of n numbers),
4. Check the sign of the above two and if the signs are opposite i.e. one is positive and another is negative, retrive their original values using array functions and knock them off. Maximum value = Maximum value + Minimum value, Minimum value = 0.
4. Repeat this process n - 1 times for n numbers until 1. all but one of them are zero or 2. all have the same sign convention.

Will send a working file as soon as one is ready. Once again, seriously appreciate and thankful for the interest and efforts shown.

**zbor** · 01-11-2012, 05:30 AM

Why is in range: {200 -50 300} between 2 positive and 1 negative, minimum closest to zero (negative)
and between {10 -300 -50} 1 positive and 2 negative, minimum negative?

**a703** · 01-11-2012, 06:10 AM

To give a perspective on the purpose behind the exercise:

1. Negative balances represent Credits and positive balances, Debits. The Credits need to be knocked-off against the debits and in that the sequence is that the smallest credit balance (negative balance) should be knocked off against the largest debit balance (positive balance) and vice versa. in {200 -50 300} -50 knocked off against 300 gives {200 0 250}.
2. the process stops here for {200 -50 300} as 200 and 250 have the same sign convention, essentially meaning they are both debits. had it been {-100 -50 300}, there would have been 2 steps {-100 0 250} and then {0 0 150}.
3. please check cells J3 and K3 in the sheet. the minimum value is 10 for {10 -300 -50}, the minimum value is not negative.