How to make Excel look at table 1 and pick a value from table 2 based on condition.

Hi all,

I have three lines producing three different products, the prodcut can be produced on two or more lines, but the problem is that, each line has different performance per product.

Example: line 1 can produce X1 at a rate of 35 ton/hr and X2 @ 40 Ton/hr and x3 @ 36 t/h. so the capacity of this line is calculated based on the best demonstarted performance when producing combined prodcuts in one year. But look to the capacity if the line is producing only the highest two grades (X2 & X3 ) , then the line will have higher yearly production (higher capacity). Solver we you run it it will asume that all products are produced at the same rate
In the attached file, you will see a simple example using solver, so the problem is that I need excel or solver when it starts to allocate the yearly production per line; first to look at the highest rate for each product and to start the production of that product first untill I the grade reached/exceed the planned amount or the line capacity, then it should look to the second highest and so on. Please put in mind that the best demonstarted rates are per hour.

Thanks.

Revised Example..xlsx

Hi nlpman,

After reading your description in the attached workbook, I got confused about the approach using which you are filling data from Table 1 & 2 to Table 3. Please explain little more just on the approach using which the table 3 is getting filled. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi nlpman,

See the attached file where I have done the solver using Excel functions plus little VB Macros
look into the column P and T where T1 has a little smiley powered by a little macro. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Wooow, Thank you sir.

Very smart way.

What about the remaining transitions T2 T3 and T4.

Please note that table #1 the location of the 0 and 1 is changing always but table 3 should read the corresponding value from table # 2 always.

So table 1 is changing every year and accordingly table 3 will be changing as well, BUT table 2 will remain as is.

Also, please note that (and to make it easy) i'm interested to get the total losses per period only and I don't care about the location. So you can tell me that P1= Sum(T1.T2,T3,T4) as one value.

Thx

Nlpman

You are welcome nlpman...

Regarding remaining transitions like T2, T3 and T4...or for any number of tables, you can use the solution provided, as a solver where you can replace the existing table with the new tables and get the output.


Regarding total losses, you can put a sum formula in Table 3 itself and there you can get the required sum. Thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>