Re-Normalize factors to 100 after changing factors

[mirology]

My data are like This [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]TotalFeed[/TD]
[TD]Col1[/TD]
[TD]Col2[/TD]
[TD]Col3[/TD]
[TD]Factor1[/TD]
[TD]Factor2[/TD]
[TD]Factor3[/TD]
[/TR]
[TR]
[TD]100 (sc-1)[/TD]
[TD]50[/TD]
[TD]25[/TD]
[TD]25[/TD]
[TD]=50/100[/TD]
[TD]=25/100[/TD]
[TD]=25/100[/TD]
[/TR]
[TR]
[TD]100 (sc 2)[/TD]
[TD]70[/TD]
[TD][/TD]
[TD][/TD]
[TD]=70/100[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[/TR]
[TR]
[TD]100 (sc-3)[/TD]
[TD]70[/TD]
[TD]15[/TD]
[TD][/TD]
[TD]=70/100[/TD]
[TD]=15/100[/TD]
[TD="align: center"]?[/TD]
[/TR]
</tbody>[/TABLE]

As a matter of math its easy the 30 % remaining will be allocated as 15% and 15 % ...

What i need is when i change Col1 value ,factor2 and factor3 auto-renormalize eg.(15-15) so that sum=100 for all 3 columns
and the same happens when i change Col2 value , factor1 and factor3 changes accordingly
same for col 3. factor 1,2 change

for all of the above scenarios ( Scenario 1 = direct manual input so total = 100 )
Scenario 2 ( 1 manual and 2 auto calculated )
Scenarios 3 ( 2 manual and 1 auto calculated)

I need a unified Column Formula or a VB code to do the above Scenarios depending if the offset cell (Col1,2,3) empty or not

 

[Rick Rothstein]

As far as I know, you will not be able to do what you want using Excel formulas... an Excel formula calculates on the values it sees in the cells it references and cannot tell which cell physically changed. You can do what you want with VBA event code, but you will need to clarify your requirements a little bit before such code can be written. Is it always the case that whatever number you put in one cell is subtracted from 100 and the remainder is divided in two and each of those halves is assigned to the remaining cells across? In other words, at least two of the cells will always have the same amount in them?

 

[mirology]

Thanks for your reply, the idea is if i made a manual over-ride in columns from 1 to 3 the factor should be forced to take it ... and the reaming will be divided over the other cells unless they are manually over-rided too ... i was messing around with the sumif function and it seems i have found a way to do without VB and here is the forumla written in any of the(FACTOR Columns) :

for example in scenario number 2

=IF(ColumnCell="",(OlderColumnCell/((SUM(OlderColumnCellsss)-SUMIF(ColumnCells,"<>",OlderColumnCellsss)))*(100-SUMIF(ColumnCellsss,"<>",ColumnCellsss))),ColumnCell)

IF function used to check if cell empty or not : if not empty then factor = cell value directly
if empty : the sumif function used to sum the adjacent previous rows for non empty cells and substract it from the 100 and dist. it

I used the above formula and it works well with all scenarios... except scenario 1 there is no total =100 check so data validation may be done for this ... Thanks