How to use goal seek on column

Shane is right Goal Seek in used on one cell. However, if you want each ratio
to be the same you can use a macro to change the values in column A.

Copy this macro into a module (ALT + F11, Insert Module), return to the
sheet, select column C values

and run the macro (ALT + F select macro & click Run

Change the Target value to suit before running.

Sub T()
Dim c As Variant
Dim sAddr As String

' in case of #Div/0! errors
On Error Resume Next

For Each c In Selection
sAddr = c.Address
Range(sAddr).GoalSeek goal:=0.5, changingcell:=c.Offset(0, -2)
Next c

End Sub

Regards
Peter Atherton

" " wrote:

> Hi!
>
> I have a formula in cell C which is dependant on values in cell A and
> B. I use goal seek to adjust the value in cell A to meet the goal for
> C. This works perfectly. However I would like this to be performed on
> all rows in column C. Is this possible?
>
> Example:
>
> Column A Column B Column
> C
> 1
> 1 =A/B
> 5
> 3 =A/B
> .... ...
> =A/B
>
> An answer would be much appreciated!
>
> /Daniel
>
 >> Stay informed about: How to use goal seek on column 

On Dec 4, 10:25 am, Shane Devenshire
wrote:
> Hi,
>
> Goal seek is designed to work on one cell at a time only.  Why not show us
> the formula you are using and tell us what you are trying to do and maybe we
> can give you a better solution.  Also, its possibe that you could use VBA to
> run goal seek against each cell in a column, one at a time.  
>
> I have made the assumption that you are trying to use goal seek on each set
> of cells (A1, B1, C1 for example) one at a time.  If that is not the case if
> you are trying to have goal seek consider more than one condition at a time,
> then you should be looking into Solver.
>
> --
> If this helps, please click the Yes button
>
> Cheers,
> Shane Devenshire
>
> " " wrote:
> > Hi!
>
> > I have a formula in cell C which is dependant on values in cell A and
> > B. I use goal seek to adjust the value in cell A to meet the goal for
> > C. This works perfectly. However I would like this to be performed on
> > all rows in column C. Is this possible?
>
> > Example:
>
> > Column A                       Column B                        Column
> > C
> > 1
> > 1                                     =A/B
> > 5
> > 3                                     =A/B
> > ....                                   ...
> > =A/B
>
> > An answer would be much appreciated!
>
> > /Daniel

Thanks for your reply.

When I looked at my spreadsheet I realized that I use more than three
columns. I will try to describe more in detail below.

I calculate suggested order quantity for spare parts. In order to see
how many months I can last without more orders I use the SMOS formula
described below (in column C).

Column C: SMOS = (EOH + SOQ) / SM
where;
SMOS (available month of stock including suggested order quantity)
[months]
EOH (efficacy on hand = available on hand + due in - back order)
[pieces]
SOQ (suggested order quantity: suggested by me) [pieces]
SM (standard model = computer calculated monthly demand) [pieces]

The way I use the goal seek function is to adjust SOQ (column B) so
that SMOS = 8 months (column C).

My list looks like this:
EOH SOQ SMOS SM
1 2 3 4
3 2 1 4

My list consists of hundreds of rows. And that is why I would like the
goal seek function to be applied on all cells in column C.

Hope it is easier to understand now.

Thanks again!

/Daniel
 >> Stay informed about: How to use goal seek on column 

Daniel

I have just seen your second post sp I have revised the macro. You do not
have to select the range - Just click run in the macro dialog box ALT + F8

Sub BatchGoalSeek()
Dim c As Variant
Dim sAddr As String
Dim rng As Range
Dim NRows As Long

NRows = Range("A1").CurrentRegion.Rows.Count
Set rng = Range("C2:C" & NRows)
' in case of #Div/0! errors
On Error Resume Next

For Each c In rng
sAddr = c.Address
Range(sAddr).GoalSeek goal:=8, changingcell:=c.Offset(0, -1)
Next c

End Sub


Regards
Peter

"daniel.bash DEL " wrote:

> On Dec 4, 10:25 am, Shane Devenshire
> wrote:
> > Hi,
> >
> > Goal seek is designed to work on one cell at a time only. Why not show us
> > the formula you are using and tell us what you are trying to do and maybe we
> > can give you a better solution. Also, its possibe that you could use VBA to
> > run goal seek against each cell in a column, one at a time.
> >
> > I have made the assumption that you are trying to use goal seek on each set
> > of cells (A1, B1, C1 for example) one at a time. If that is not the case if
> > you are trying to have goal seek consider more than one condition at a time,
> > then you should be looking into Solver.
> >
> > --
> > If this helps, please click the Yes button
> >
> > Cheers,
> > Shane Devenshire
> >
> > "daniel.b... DEL " wrote:
> > > Hi!
> >
> > > I have a formula in cell C which is dependant on values in cell A and
> > > B. I use goal seek to adjust the value in cell A to meet the goal for
> > > C. This works perfectly. However I would like this to be performed on
> > > all rows in column C. Is this possible?
> >
> > > Example:
> >
> > > Column A Column B Column
> > > C
> > > 1
> > > 1 =A/B
> > > 5
> > > 3 =A/B
> > > .... ...
> > > =A/B
> >
> > > An answer would be much appreciated!
> >
> > > /Daniel
>
> Thanks for your reply.
>
> When I looked at my spreadsheet I realized that I use more than three
> columns. I will try to describe more in detail below.
>
> I calculate suggested order quantity for spare parts. In order to see
> how many months I can last without more orders I use the SMOS formula
> described below (in column C).
>
> Column C: SMOS = (EOH + SOQ) / SM
> where;
> SMOS (available month of stock including suggested order quantity)
> [months]
> EOH (efficacy on hand = available on hand + due in - back order)
> [pieces]
> SOQ (suggested order quantity: suggested by me) [pieces]
> SM (standard model = computer calculated monthly demand) [pieces]
>
> The way I use the goal seek function is to adjust SOQ (column B) so
> that SMOS = 8 months (column C).
>
> My list looks like this:
> EOH SOQ SMOS SM
> 1 2 3 4
> 3 2 1 4
>
> My list consists of hundreds of rows. And that is why I would like the
> goal seek function to be applied on all cells in column C.
>
> Hope it is easier to understand now.
>
> Thanks again!
>
> /Daniel
>
>
>
>
 >> Stay informed about: How to use goal seek on column