Finding the value of a cell for another cell to equal a certain number

[LavaDude]

I'd like to find the value for a cell that would make another cell equal an
exact number.

Example:

A1 = Find Formula
A2 = (A1*6)
A3 = SUM(A1:A2)

I'd like to say for example, what value in A1 would have A3 equal to 35.
This is a very simplified example and I hope someone can help.

Thanks! ...

LavaDude

[Gary Brown]

Active the Add-in called 'Solver'.

Also check out 'Goal Seek' under the TOOLS option on the top menu.
HTH,
Gary Brown


"LavaDude" <[email protected]> wrote in message
news:[email protected]...
> I'd like to find the value for a cell that would make another cell equal
> an
> exact number.
>
> Example:
>
> A1 = Find Formula
> A2 = (A1*6)
> A3 = SUM(A1:A2)
>
> I'd like to say for example, what value in A1 would have A3 equal to 35.
> This is a very simplified example and I hope someone can help.
>
> Thanks! ...
>
> LavaDude

[Max]

Perhaps you could try Goal Seek ..

Select A3

Click Tools > Goal Seek

In the Goal Seek dialog,
input the settings:

Set cell: A3
To value: 35 (< this is your target value in A3)
By changing cell: A1

Click OK

There'll be a Status dialog
Click OK

The value in A1 will show the result: 5

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"LavaDude" <[email protected]> wrote in message
news:[email protected]...
> I'd like to find the value for a cell that would make another cell equal
an
> exact number.
>
> Example:
>
> A1 = Find Formula
> A2 = (A1*6)
> A3 = SUM(A1:A2)
>
> I'd like to say for example, what value in A1 would have A3 equal to 35.
> This is a very simplified example and I hope someone can help.
>
> Thanks! ...
>
> LavaDude

[Peo Sjoblom]

Look at tools>goal seek, leave A1 blank, put =A1*6 in A2 and =SUM(A1:A2) in
A3, select A3
open goal seek, set cell A3 to value 35 by changing cell A1, click OK and
you get first a box saying
Goal seek with cell A3 found a solution, click OK again and notice now
you'll have 5 in A1

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"LavaDude" <[email protected]> wrote in message
news:[email protected]...
> I'd like to find the value for a cell that would make another cell equal
> an
> exact number.
>
> Example:
>
> A1 = Find Formula
> A2 = (A1*6)
> A3 = SUM(A1:A2)
>
> I'd like to say for example, what value in A1 would have A3 equal to 35.
> This is a very simplified example and I hope someone can help.
>
> Thanks! ...
>
> LavaDude

[LavaDude]

I found a solution!! ... I used "Goal Seek..." from the tools menu... but,
is there another way?

LavaDude

LavaDude <[email protected]> wrote in
news:[email protected]:

> I'd like to find the value for a cell that would make another cell
> equal an exact number.
>
> Example:
>
> A1 = Find Formula
> A2 = (A1*6)
> A3 = SUM(A1:A2)
>
> I'd like to say for example, what value in A1 would have A3 equal to
> 35. This is a very simplified example and I hope someone can help.
>
> Thanks! ...
>
> LavaDude
>

[LavaDude]

Thanks guys! ... I guess I should have refreshed my articles before
responding ... LOL ...

Aloha!

LavaDude <[email protected]> wrote in
news:[email protected]:

> I found a solution!! ... I used "Goal Seek..." from the tools menu...
> but, is there another way?
>
> LavaDude
>

[Max]

Another way to tinker with

Let's quickly set up a one variable data table

Try this in the *same* sheet:

Put in say, D1: =A3
(just a simple link to the output cell of interest)

List a set of possible values for A1, say: 1,2,3 ... 10
down in C2:C11

Select C1:D11

Click Data > Table

In the Table dialog:
Leave the "Row input cell" empty
Input for :Column input cell" : A1

Click OK

In C1:D11 will appear:

7**
1 7
2 14
3 21
4 28
5 35 < read-off the value in col C (for 35 in col D)
6 42
7 49
8 56
9 63
10 70

**It's usual to mask the link cell D1 by
formatting the font in say, white
to blend-in with the fill color

The output range D2:D11 will compute
the various results (for A3)
depending on the possible inputs (for A1) listed in C2:C11

So we could just scroll down and read-off the value in col C,
i.e. "5" which computes to the target value of "35" in col D

And if needed (supposing there isn't any value resembling our target value
of 35 in the output range in D2:D11) we could just change the listed values
in C2:C11 to yet other values (e.g.: try putting in C2:C11 : 11,12,13 ...
20) to experiment further. The table will recalc accordingly to give us the
new results in D2:D11.

Extend to suit ..

Note: Albeit the TABLE formula, {=TABLE(,A1)} which will appear in every
cell in the output range D2:D11, looks very much like an array formula, it
cannot just be entered as such. The construct must be done / invoked via the
Data > Table menu.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"LavaDude" <[email protected]> wrote in message
news:[email protected]...
> I'd like to find the value for a cell that would make another cell equal
an
> exact number.
>
> Example:
>
> A1 = Find Formula
> A2 = (A1*6)
> A3 = SUM(A1:A2)
>
> I'd like to say for example, what value in A1 would have A3 equal to 35.
> This is a very simplified example and I hope someone can help.
>
> Thanks! ...
>
> LavaDude

[Max]

> > but, is there another way?

You could also try a one variable data table approach
I've just posted an example set up for you to tinker with

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"LavaDude" <[email protected]> wrote in message
news:[email protected]...
> Thanks guys! ... I guess I should have refreshed my articles before
> responding ... LOL ...
>
> Aloha!
>
> LavaDude <[email protected]> wrote in
> news:[email protected]:
>
> > I found a solution!! ... I used "Goal Seek..." from the tools menu...
> > but, is there another way?
> >
> > LavaDude
> >