Can I use solver for this?

I am not sure the best way to solve my problem and am hoping someone can help.

I want to be able to determine approx how long each item takes to produce.
I have types of work new, renewal and mod - and then a complexity level.
I know that it took 6980 total hrs to produce all of these items.

Assumptions that i wanted to build in:
New items take twice as long as renewals and 4 times as long as mods.
Hard items take longer then medium items and medium take longer then easy.

Totals
NEW RENEWAL MOD
EASY 53 71 87
MEDIUM 18 19 24
HARD 29 24 29


Total Hrs 6980

Is this the type of problem solver can help with, or is there aneasier way?

I want to be able to determine approx how long each item takes to produce.

I know that it took 6980 total hrs to produce all of these items.
New items take twice as long as renewals and 4 times as long as mods.
Hard items take longer then medium items and medium take longer then easy.
Is this the type of problem solver can help with, or is there an easier way?


Hi. Here's my take on Solver.
When you say...

Hard items take longer then medium items and medium take longer then easy.


I believe at this point there are many solutions.
Either Medium take just a little longer than Easy, and hard takes an extra long time...
or hard is just a little longer then medium, ect.

Here's an attempt via equations.
Let's separate the matrix for now, and multiply them together item by item later.
Here are the number of items each.

{{53, 71, 87}, {18, 19, 24}, {29, 24, 29}}

For time, let's set "x" as the time for easy Mod's.
Then the array for easy is {4x, 2x, x}
The Medium row "Is just a little longer in time." Therefore, we add a t1 to x in the above.
{4 (t1 + x), 2 (t1 + x), t1 + x}
The last row is longer still, t2, and we have to remember that xt1t2
{4 (t2 + x), 2 (t2 + x), t2 + x}

If we "Multiply" the two matrix's together, and add them up, we are left with this equation:
134 t1 + 193 t2 + 768 x = 6980

Hence, we see that there are multiple solutions with the constraint that xt1t2.

I may be wrong, but I show only 1 "Integer" solution, so maybe this is what you want?
{x - 4, t1 - 9, t2 - 14}

Hence, here is the one "Integer" table for the appox time to produce each item.

{16, 8, 4}, {52, 26, 13}, {72, 36, 18}

An "Easy, Mod" takes 4 hours.
An "Easy, New" takes 16 hours.
Notice that all Renew's are twice as long as the Mod's, and the New's are twice as long as the Renew's, as per you stated constraint.
The Medium and Hard's are in an increasing manner as stated.
--
HTH :)
Dana DeLouis


"Belinda7237" wrote in message ...

I am not sure the best way to solve my problem and am hoping someone can help.

I want to be able to determine approx how long each item takes to produce.
I have types of work new, renewal and mod - and then a complexity level.
I know that it took 6980 total hrs to produce all of these items.

Assumptions that i wanted to build in:
New items take twice as long as renewals and 4 times as long as mods.
Hard items take longer then medium items and medium take longer then easy.

Totals
NEW RENEWAL MOD
EASY 53 71 87
MEDIUM 18 19 24
HARD 29 24 29


Total Hrs 6980

Is this the type of problem solver can help with, or is there aneasier way?

Dana -

Thanks for the explanation I validated it with a couple months of my data
sets and works perfectly - I have a few others of these that i can use the
same logic t odevelop an equation for so i wanted t obe able to understand
everything here... I have written it out for myself as you describe here and
completely understand it...with one exception - what numbers were multiplied
to get the 134 t1?

Thank you for steering me right with your brillant mind!!!

"Belinda7237" wrote:

I am not sure the best way to solve my problem and am hoping someone can help.

I want to be able to determine approx how long each item takes to produce.
I have types of work new, renewal and mod - and then a complexity level.
I know that it took 6980 total hrs to produce all of these items.

Assumptions that i wanted to build in:
New items take twice as long as renewals and 4 times as long as mods.
Hard items take longer then medium items and medium take longer then easy.

Totals
NEW RENEWAL MOD
EASY 53 71 87
MEDIUM 18 19 24
HARD 29 24 29


Total Hrs 6980

Is this the type of problem solver can help with, or is there aneasier way?

what numbers were multiplied to get the 134 t1?

Hi. If you expand the second row, the t1 coefficients add to the following:

=SUMPRODUCT({18,19,24},{4,2,1})
134

Likewise for t2:
=SUMPRODUCT({29,24,29},{4,2,1})
193

For the x variable, multiply each row's {4,2,1} array with your data, and then add them up.

{4*53, 2*71, 87},
{4*18, 2*19, 24},
{4*29, 2*24, 29}

The sum of all the numbers is 768.

The hard part is figuring out which solution is acceptable. There just happened to be 1 integer solution.
There are many more non-integer solutions.

Your new integer solution array is:
{{16, 8, 4}, {52, 26, 13}, {72, 36, 18}}

If we multiply each element in your array
{{53, 71, 87}, {18, 19, 24}, {29, 24, 29}};

We get (ie 16*53, etc)
{{848, 568, 348}, {936, 494, 312}, {2088, 864, 522}}

Whose Total is
6980
--
HTH :)
Dana DeLouis


"Belinda7237" wrote in message ...

Dana -

Thanks for the explanation I validated it with a couple months of my data
sets and works perfectly - I have a few others of these that i can use the
same logic t odevelop an equation for so i wanted t obe able to understand
everything here... I have written it out for myself as you describe here and
completely understand it...with one exception - what numbers were multiplied
to get the 134 t1?

Thank you for steering me right with your brillant mind!!!

"Belinda7237" wrote:


I am not sure the best way to solve my problem and am hoping someone can help.

I want to be able to determine approx how long each item takes to produce.
I have types of work new, renewal and mod - and then a complexity level.
I know that it took 6980 total hrs to produce all of these items.

Assumptions that i wanted to build in:
New items take twice as long as renewals and 4 times as long as mods.
Hard items take longer then medium items and medium take longer then easy.

Totals
NEW RENEWAL MOD
EASY 53 71 87
MEDIUM 18 19 24
HARD 29 24 29


Total Hrs 6980

Is this the type of problem solver can help with, or is there aneasier way?

thank you! that helped a great deal - i appeciate all your help...i actually
get it now!

"Dana DeLouis" wrote:

what numbers were multiplied to get the 134 t1?

Hi. If you expand the second row, the t1 coefficients add to the
following:

=SUMPRODUCT({18,19,24},{4,2,1})
134

Likewise for t2:
=SUMPRODUCT({29,24,29},{4,2,1})
193

For the x variable, multiply each row's {4,2,1} array with your data, and
then add them up.

{4*53, 2*71, 87},
{4*18, 2*19, 24},
{4*29, 2*24, 29}

The sum of all the numbers is 768.

The hard part is figuring out which solution is acceptable. There just
happened to be 1 integer solution.
There are many more non-integer solutions.

Your new integer solution array is:
{{16, 8, 4}, {52, 26, 13}, {72, 36, 18}}

If we multiply each element in your array
{{53, 71, 87}, {18, 19, 24}, {29, 24, 29}};

We get (ie 16*53, etc)
{{848, 568, 348}, {936, 494, 312}, {2088, 864, 522}}

Whose Total is
6980
--
HTH :)
Dana DeLouis


"Belinda7237" wrote in message
...
Dana -

Thanks for the explanation I validated it with a couple months of my
data
sets and works perfectly - I have a few others of these that i can use
the
same logic t odevelop an equation for so i wanted t obe able to
understand
everything here... I have written it out for myself as you describe here
and
completely understand it...with one exception - what numbers were
multiplied
to get the 134 t1?

Thank you for steering me right with your brillant mind!!!

"Belinda7237" wrote:

I am not sure the best way to solve my problem and am hoping someone
can help.

I want to be able to determine approx how long each item takes to
produce.
I have types of work new, renewal and mod - and then a complexity
level.
I know that it took 6980 total hrs to produce all of these items.

Assumptions that i wanted to build in:
New items take twice as long as renewals and 4 times as long as mods.
Hard items take longer then medium items and medium take longer then
easy.

Totals
NEW RENEWAL MOD
EASY 53 71 87
MEDIUM 18 19 24
HARD 29 24 29


Total Hrs 6980

Is this the type of problem solver can help with, or is there aneasier
way?

Hi. Glad it helped.
I should have suggested the following for 768.
With your data in A1:C3

{53, 71, 87}
{18, 19, 24}
{29, 24, 29}

Then
=SUM(MMULT(A1:C3,{4;2;1}))
Returns 768

- -
HTH :)
Dana DeLouis


"Belinda7237" wrote in message ...

thank you! that helped a great deal - i appeciate all your help...i actually
get it now!

"Dana DeLouis" wrote:


what numbers were multiplied to get the 134 t1?


Hi. If you expand the second row, the t1 coefficients add to the
following:

=SUMPRODUCT({18,19,24},{4,2,1})
134

Likewise for t2:
=SUMPRODUCT({29,24,29},{4,2,1})
193

For the x variable, multiply each row's {4,2,1} array with your data, and
then add them up.

{4*53, 2*71, 87},
{4*18, 2*19, 24},
{4*29, 2*24, 29}

The sum of all the numbers is 768.

The hard part is figuring out which solution is acceptable. There just
happened to be 1 integer solution.
There are many more non-integer solutions.

Your new integer solution array is:
{{16, 8, 4}, {52, 26, 13}, {72, 36, 18}}

If we multiply each element in your array
{{53, 71, 87}, {18, 19, 24}, {29, 24, 29}};

We get (ie 16*53, etc)
{{848, 568, 348}, {936, 494, 312}, {2088, 864, 522}}

Whose Total is
6980
--
HTH :)
Dana DeLouis


"Belinda7237" wrote in message
...

Dana -

Thanks for the explanation I validated it with a couple months of my

data

sets and works perfectly - I have a few others of these that i can use

the

same logic t odevelop an equation for so i wanted t obe able to

understand

everything here... I have written it out for myself as you describe here

and

completely understand it...with one exception - what numbers were

multiplied

to get the 134 t1?

Thank you for steering me right with your brillant mind!!!

"Belinda7237" wrote:

I am not sure the best way to solve my problem and am hoping someone

can help.


I want to be able to determine approx how long each item takes to

produce.

I have types of work new, renewal and mod - and then a complexity

level.

I know that it took 6980 total hrs to produce all of these items.

Assumptions that i wanted to build in:
New items take twice as long as renewals and 4 times as long as mods.
Hard items take longer then medium items and medium take longer then

easy.


Totals
NEW RENEWAL MOD
EASY 53 71 87
MEDIUM 18 19 24
HARD 29 24 29


Total Hrs 6980

Is this the type of problem solver can help with, or is there aneasier

way?