Line Balancing in Excel

[Mayank]

hi guys,

i am trying to create an excel based line balancing sheet and I am stuck at the following point for 2 days now ...

Please help

1) I have all elements written down in B31:B382 and all element times written down in G31:G382

2) I want excel to pick up the takt time (calculated by a formula in B12) and sum up the element times till reaching the takt time but not exceeding it. Once this is done, repeat the above till all elements are covered.

3) I used the formula =IF(AND(SUM($G$31:G31)>$B$12,MIN(SUM($G$31:G31),$B$12)),"",SUM($G$31:G31)) in H31 and copied it down. This gave me the first iteration but i dont know how to repeat the above from the next row.


I am stuck and to top it all I havent worked in Macros or VBA in the past so.....

Please help....

 

[Hui]

Mayank

Can you post a sample of data somewhere ?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[kchiba]

Hi Mayank,


I am not to sure what you are after, but try the following:


assuming you data is in column A, your takt is in cell F20


in Column B place the formula =SUM($A$1:A1) (note the absolute)


in Column C place the formula:


=IF(MOD(B1,$F$1)>A1,B1-INT(B1/$F$1)*$F$1,A1)

 

[Mayank]

link to file:


http://cid-4b0d2dad313affd9.skydrive.live.com/self.aspx/.Public/Line%20Balancing%20Query.xls

 

[Hui]

Mayank,

Try the following:

E6: =C6

E7: =IF(INT((E6+C7)/$I$1)=INT(E6/$I$1),E6+C7,C7)

and copy E7 down


ps: Be aware that your Cell G29 has a value not a formula

 

[Mayank]

Thanks a lot Hui....u really are an excel ninja!!!!

im aware of the value, i will put a formula there...

thanks again

 

[Hui]

or you could try this

which is shorter and quicker

E7: =C7+(INT((E6+C7)/$I$1)=INT(E6/$I$1))*E6