how to solving line balancing problem by using excel solver? help me

Originally Posted by fialuthfia

is it possible to calculate cycle time, idle time, and find bottleneck by using excel solver? if it doesn't, what software would you recommend? Thankyou

Short but useless answer -- yes, I expect it is possible to do these things in Excel -- just like in any other programming language. I am not at all familiar with line balance problems (I don't know if any of us here will be), but it sounds like an "old" problem that people have been solving for a long time, and I expect some of them have been using spreadsheets for the solution.

I find that I have to understand the math and algorithms behind a problem before I can really start to program it. Since it would be rather fortuitous to find someone on a random Excel forum who understands line balance problems, you may need to start by giving us a crash course in what line balance problems are and the algorithms used to solve them. Then we can be more effective in helping you program a solution into Excel. To start -- is this what you mean by "line balancing problem"? http://www.transtutors.com/homework-...g-problem.aspx

If it is, you might find this paper particularly interesting, since he focuses on solving such a problem using spreadsheets (found by putting "line balancing problem Excel" into my favorite internet search engine). http://archive.ite.journal.informs.o...RagsdaleBrown/

Actually, i've been searched about line balancing problem but mostly the problem is about job sequencing and i didn't find any previous spreadsheets that i'm looking for.
and sadly i really bad on finding and understanding algorithms formulation. Maybe you could understand from this link: https://www.dropbox.com/s/rloe1fpqbb...solver.xlsx?m= about my line balancing problem.
I really appreciate your help. Thankyou.

It appears that this is the same kind of line balancing problem described in the two links.

In my ignorance, I think the only help I could give would be to regurgitate what Ragsdale and Brown have published in their paper. Rather than having me rewrite their paper, perhaps it would be best to approach it this way: Did you read/study Ragsdale and Brown's paper (link again: http://archive.ite.journal.informs.o...RagsdaleBrown/)? Did you download and study their sample spreadsheet? What parts of their approach did you understand? What parts did you not understand?

A quick help file on the constraints available in Solver: http://www.solver.com/content/basic-...ete-constraint In addition to >=, <=, and =, there are also constraints for "binary (0/1)", "integer", and "all different".

I do not understand which constraint you are having trouble formulating. If it is the complex "between" constraint (Ragsdale and Browns "3rd" constraint), that would actually be entered as three different constraints:
E6:E12>=1
E6:E12<=7
E6:E12 integer
Does that help, or were you having a different problem entering the constraints?

E13 =MAX(E6:E12)
F6 =MAX(IF(ISERR(FIND($B$6:$B$12,D6)),0,$E$6:$E$12))(Press [Ctrl]+[Shift]+[Enter] to enter.)
I6 =SUMIF($E$6:$E$12,H6,$C$6:$C$12)

that formula above, is it should compute manually? if that so, so i should compute all the formulation manually first, then using solver to find out what we want to optimize?

I am not sure I understand this question. The general procedure whenever we want to use Solver is:

1) Enter the desired formulas and values that describe the system to minimize.
2) Call Solver and setup the desired constraints, decision cells, and objective function.
3) Execute Solver.

So, you would enter those formulas as explained, then call Solver from the Data group. Is that what you are asking?