How to use goal seek on column

[daniel.bash]

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

 

[Shane Devenshire]

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.

 

[Billy Liddel]

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 + F8) 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

 

[daniel.bash]

Hi,

Goal seek is designed to work on one cell at a time only.  Why not showus
the formula you are using and tell us what you are trying to do and maybewe
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

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

 

[Billy Liddel]

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

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

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