Goal-Seek Question

[Smita]

Hello,

I have about 100 projects over which I allocate worked hours (40) for
managers. These are allocated in proportion to the weight of the project
relative to other projects. I use goal-seek to do this. This process is done
on a weekly basis and new projects are added and old removed.

Here is my question. I have noticed that goal-seek has a bias in that it
allocates only to the projects which have the highest weight, leaving out
others. So the result is that some projects get hit hardly whereas, others do
not.

Is there anything else I can do to even out this? Or even more importantly
is my assumption of bias correct? Any thoughts? How does goal-seek internally
work?

Thanks much.

 

[Niek Otten]

Please supply your formulas, the values of the precedent cells, the result(s) you expected and what you got instead.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello,
|
| I have about 100 projects over which I allocate worked hours (40) for
| managers. These are allocated in proportion to the weight of the project
| relative to other projects. I use goal-seek to do this. This process is done
| on a weekly basis and new projects are added and old removed.
|
| Here is my question. I have noticed that goal-seek has a bias in that it
| allocates only to the projects which have the highest weight, leaving out
| others. So the result is that some projects get hit hardly whereas, others do
| not.
|
| Is there anything else I can do to even out this? Or even more importantly
| is my assumption of bias correct? Any thoughts? How does goal-seek internally
| work?
|
| Thanks much.

 

[Smita]

It is not that I get a wrong answer. But if I have 50 hours to spread across
100 projects where the projects are weighted by thier size, only the 50
largest projects keep getting hit every time, rather than a more fair
distribution.

 

[MyVeryOwnSelf]

I have about 100 projects over which I allocate worked hours (40) for

managers. These are allocated in proportion to the weight of the
project relative to other projects. I use goal-seek to do this. This
process is done on a weekly basis and new projects are added and old
removed.

Here is my question. I have noticed that goal-seek has a bias in that
it allocates only to the projects which have the highest weight,
leaving out others. So the result is that some projects get hit hardly
whereas, others do not.

Is there anything else I can do to even out this? Or even more
importantly is my assumption of bias correct? Any thoughts? How does
goal-seek internally work?

Without more information, it's hard to figure out what's happening. Maybe
rounding someplace is turning small numbers into zeros, or maybe not enough
decimal places are being displayed.

As an aside, are you sure you need goal-seek? For example, with weights in
column A and "worked hours" in C1, putting
=$C$1*A1/SUM(A:A)
in B1 and copying down seems to allocate the work hours among the projects
without resort to goal-seek.

 

[Smita]

That is exactly what I am trying now.

You are right though about the fractions. But my constraint is that I need
to round the hour to integer. So I can not spread 0.4 hours, it would be 0,
but 0.6 would be 1.

That is what caused me to pursue goal-seek, but it seems to have bias
towards larger projects so that those keep getting hit more frequently and
end up subsidizing smaller ones.

I wanted to understand goal-seek (under the hood) to see if my hunch is
correct and if there is any other workaround.

Thanks.

 

[Niek Otten]

We have still no idea at all how you try to solve this and how you apply GoalSeek.
If you need any further assistence you'll have to supply quite a lot more details.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| That is exactly what I am trying now.
|
| You are right though about the fractions. But my constraint is that I need
| to round the hour to integer. So I can not spread 0.4 hours, it would be 0,
| but 0.6 would be 1.
|
| That is what caused me to pursue goal-seek, but it seems to have bias
| towards larger projects so that those keep getting hit more frequently and
| end up subsidizing smaller ones.
|
| I wanted to understand goal-seek (under the hood) to see if my hunch is
| correct and if there is any other workaround.
|
| Thanks.
|
| "MyVeryOwnSelf" wrote:
|
| > > I have about 100 projects over which I allocate worked hours (40) for
| > > managers. These are allocated in proportion to the weight of the
| > > project relative to other projects. I use goal-seek to do this. This
| > > process is done on a weekly basis and new projects are added and old
| > > removed.
| > >
| > > Here is my question. I have noticed that goal-seek has a bias in that
| > > it allocates only to the projects which have the highest weight,
| > > leaving out others. So the result is that some projects get hit hardly
| > > whereas, others do not.
| > >
| > > Is there anything else I can do to even out this? Or even more
| > > importantly is my assumption of bias correct? Any thoughts? How does
| > > goal-seek internally work?
| >
| > Without more information, it's hard to figure out what's happening. Maybe
| > rounding someplace is turning small numbers into zeros, or maybe not enough
| > decimal places are being displayed.
| >
| > As an aside, are you sure you need goal-seek? For example, with weights in
| > column A and "worked hours" in C1, putting
| > =$C$1*A1/SUM(A:A)
| > in B1 and copying down seems to allocate the work hours among the projects
| > without resort to goal-seek.
| >

 

[MyVeryOwnSelf]

I wanted to understand goal-seek (under the hood) ...

In simple terms, goal-seek uses step-by-step iteration. Suppose you specify
Set cell: A1
To value: 5
By changing cell: B1

What happens behind the scenes for each step is something like:

Change B1, being clever about which way and how much.

Calculate A1 again using the new B1.

Q1: Is A1 equal to 5 now?

Q2: Has it moved closer to 5? How much did A1 change?

Q3: Have the step-to-step changes become tiny?

Q4: Have we done too many steps -- time to give up?

Use the answers to decide
(a) Whether to stop.
(b) If not, how to change B1 next.

Repeat the above.

Modern PCs are fast, and can do a lot of iterations in a second for typical
goal-seek problems.

The user can exert some control over the process using:
Tools > Options > Calculation > Iteration
but this doesn't sound useful in the problem you've described so far.

Of course, Excel might be even cleverer than I realize.

 

[Dana DeLouis]

You are right though about the fractions. But my constraint is that I need

to round the hour to integer. So I can not spread 0.4 hours, it would be
0,
but 0.6 would be 1.

Sounds like you may want to use Solver instead, and use "Integer"
constraints.