Goal Seek

Is there a way to "copy" Goal Seek acroos multiple rows?

Hourly Current New New
Rate Comp Rate Compensation
16.00 727.27 15.55675 727.27
16.00 727.27 15.55 727.00
17.00 772.73 105.00
15.26 693.75 105.00
16.00 727.27 105.00
16.50 750.00 105.00

The cells under the column labled New Compensation has a different formula
than the cells under the column labled Current Comp. I want to calculate a
new rate so that the New Compensation would be equal to the Current Comp by
changing the cells under the column labled New Rate.

I have used Goal Seek in the first row. However this spreadsheet is several
hundred lines long and useing Goal Seek is not practical. Is there a way to
copy the goal seek funtion across multiple rows or is these a formula which
could be written?

Thanks.

Hi Mike,

In general, you should be able to get your formula to work "the other way
around".
Second option is to record a macro while you goal seek the first line and
then change that to do the same for each line.
If you can write your formula in VBA, I could post a Function which iterates
that formula so you can enter a "goal seek" on each row.

But first of all, what are your formulas and how do they relate to each
other (IOW how did you Goal seek?)

--
Kind regards,

Niek Otten


"Mike" <[email protected]> wrote in message
news:[email protected]...
> Is there a way to "copy" Goal Seek acroos multiple rows?
>
> Hourly Current New New
> Rate Comp Rate Compensation
> 16.00 727.27 15.55675 727.27
> 16.00 727.27 15.55 727.00
> 17.00 772.73 105.00
> 15.26 693.75 105.00
> 16.00 727.27 105.00
> 16.50 750.00 105.00
>
> The cells under the column labled New Compensation has a different formula
> than the cells under the column labled Current Comp. I want to calculate a
> new rate so that the New Compensation would be equal to the Current Comp
> by
> changing the cells under the column labled New Rate.
>
> I have used Goal Seek in the first row. However this spreadsheet is
> several
> hundred lines long and useing Goal Seek is not practical. Is there a way
> to
> copy the goal seek funtion across multiple rows or is these a formula
> which
> could be written?
>
> Thanks.
>

Could I send you the worksheet? I think it wold be easier that way.

"Niek Otten" wrote:

> Hi Mike,
>
> In general, you should be able to get your formula to work "the other way
> around".
> Second option is to record a macro while you goal seek the first line and
> then change that to do the same for each line.
> If you can write your formula in VBA, I could post a Function which iterates
> that formula so you can enter a "goal seek" on each row.
>
> But first of all, what are your formulas and how do they relate to each
> other (IOW how did you Goal seek?)
>
> --
> Kind regards,
>
> Niek Otten
>
>
> "Mike" <[email protected]> wrote in message
> news:[email protected]...
> > Is there a way to "copy" Goal Seek acroos multiple rows?
> >
> > Hourly Current New New
> > Rate Comp Rate Compensation
> > 16.00 727.27 15.55675 727.27
> > 16.00 727.27 15.55 727.00
> > 17.00 772.73 105.00
> > 15.26 693.75 105.00
> > 16.00 727.27 105.00
> > 16.50 750.00 105.00
> >
> > The cells under the column labled New Compensation has a different formula
> > than the cells under the column labled Current Comp. I want to calculate a
> > new rate so that the New Compensation would be equal to the Current Comp
> > by
> > changing the cells under the column labled New Rate.
> >
> > I have used Goal Seek in the first row. However this spreadsheet is
> > several
> > hundred lines long and useing Goal Seek is not practical. Is there a way
> > to
> > copy the goal seek funtion across multiple rows or is these a formula
> > which
> > could be written?
> >
> > Thanks.
> >
>
>
>

Hi,

May i suggest a quick and dirty way of reducing your work - use the solver
add in available in Excel.

How it would work is that you add 5 rows of the cells in both the "Current
comp" and "New Comp" column and take the difference between them. Now one
condition in the Solver will be that this difference will be 0. The other 5
conditions will be that each cell of "Current comp" will be equal to the
corresponding cell under "New comp".

As stated, this is a crude way of doing it but it can save you the trouble
of goal seeking cell by cell.

Please go through the help available on the Help menu to understand more
about this.

Also, could you mail me the file please.

"Mike" wrote:

> Is there a way to "copy" Goal Seek acroos multiple rows?
>
> Hourly Current New New
> Rate Comp Rate Compensation
> 16.00 727.27 15.55675 727.27
> 16.00 727.27 15.55 727.00
> 17.00 772.73 105.00
> 15.26 693.75 105.00
> 16.00 727.27 105.00
> 16.50 750.00 105.00
>
> The cells under the column labled New Compensation has a different formula
> than the cells under the column labled Current Comp. I want to calculate a
> new rate so that the New Compensation would be equal to the Current Comp by
> changing the cells under the column labled New Rate.
>
> I have used Goal Seek in the first row. However this spreadsheet is several
> hundred lines long and useing Goal Seek is not practical. Is there a way to
> copy the goal seek funtion across multiple rows or is these a formula which
> could be written?
>
> Thanks.
>