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

  • Thread starter Thread starter LavaDude
  • Start date Start date
L

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
 
Active the Add-in called 'Solver'.

Also check out 'Goal Seek' under the TOOLS option on the top menu.
HTH,
Gary Brown
 
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
 
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)
 
I found a solution!! ... I used "Goal Seek..." from the tools menu... but,
is there another way?

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

Aloha!
 
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.
 
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
 
Back
Top