Knock Off Working - Formula / Macro Help

[Kshitij_Pune]

The Following file for Better Presentation is also available with Google Docs at following Link:

https://docs.google.com/spreadsheet/ccc?key=0AvHakw8hr1sjdGdWeV9qanoxWTdpRmNPSTRWMW5IX3c


How Should I Knock Off the Following

A B C D E F Total

I (100) (200) (300) (400) (100) 200 (900)

II (500) (400) 400 (500) 300 (700)

III (700) (500) 100 200 100 (500) (1,300)

IV (200) (600) (50) (850)

V (500) 8,000 (600) 1,000 (100) (400) 7,400

Total (1,800) 6,700 (400) 200 (600) (450)

The data in above table can be in any order or even there can be blanks.


What I need….?

A B C D E F Total

I (100) (200) (300) =-400+100 =-100+100 =200-200 (900)

II (500) =-400+200 =400-400 =-500+500 =300-300 (700)

III (700) (500) =100-100 =200-200 =100-100 =-500+100+200+100 (1,300)

IV (200) (600) (50) (850)

V =-500+500 =8000-500-100 =-600+500+100 =1000-400-100-500 =-100+100 =-400+400 7,400

(1,800) 6,700 (400) 200 (600) (450)

1. In the first row, I check for a positive number and set it off with the previous column, i.e. I will knock 100 out of 200 with column E and remaining 100 with column D and make them both reduce by that same amount and zero for Column F


2. I will do the same above procedure for Cloumn F in this row also, however I also knock off Column C with Column E and reduce it by that much amount and remaing amount of column C of 200 will be knocked off with that of Column B


How It should Appear

A B C D E F Total

I (100) (200) (300) (300) - - (900)

II (500) (200) - - - (700)

III (700) (500) - - - (100) (1,300)

IV (200) (600) (50) (850)

V - 7,400 - - - - 7,400

(1,300) 6,300 (300) (900) - (150)


Can this be done with combination of some formulas….? Or I would require a MACRO….?

The data that I want to apply this runs into some 5000 line items..

Thank you in advance

 

[Luke M]

For this to work, you need to insert a blank column after col F (formula needs a blank anchor point. Then, assuming the first (100) appears in A1, the formula to change is:

=IF(SUM($A1:$F1)>0,MAX(0,IF(A1<0,0,A1+MIN(SUMIF($A1:$F1,"<0")+SUMIF(B1:$G1,">0")))),MIN(0,IF(A1>0,0,A1+MAX(0,SUMIF($A1:$F1,">0")+SUMIF(B1:$G1,"<0")))))

 

[Kshitij_Pune]

Thanks a lot Luke....


It works really the Way it is required to....


Although I was not able to decipher the actual function of Column F... I would really appreciate your efforts even more, if you could spare some time to explain it to me...


Thank you once again..

 

[Luke M]

The formula works by taking the initial value of the cell, and then comparing that to the total sum of cells with equal sign and "cancelling" it against the sum of cells to the right of the current cell. It is that latter part of that statement that requires the:

B1:$G1

section. Since we'll be performing an operation on the cell in col F, I needed someway to refer to a blank space to the right, aka col G.


Trying to explain total formula:

First, determine whether we are going to be cancelling all of the positives or all of the negatives by determining the SUM of the row.

If positive, we're going to first make sure that no cell ever drop's below 0, and then take the individual cell's value and compare it with the total sum of cells in the row with same sign and "cancel" by adding it to the total sum of cells to the right of the active cell with opposite sign.

If Row SUM was negative, we do similar to above, we just reverse the signs and MIN/MAX operations.

 

[Kshitij_Pune]

Well... That was great Luke... Simple Logic that did some great Work for... Thanks... I will apply it in some other work also...