varying a cell and seeing the outcome

M

mikebo

I am a fairly regular user of Excel and I can find my way around, but now I
have question that I need help on. I have set up a fairly complex sheet that
calculates some numbers based on some input numbers. Imagine a sheet that,
for example, has a value "10%" in cell A1, and "50" in cell A2, then a whole
bunch of calculations based on these and other numbers, and finally a couple
of result numbers in cells X20 and X21.
Now I want to know what the results are when I change the input values. I
can of course simply type in a value of 20% in A1 and immediately see what
the result is, but if I want to do that for amny combinations of input
values, it becomes tedious. I would have to type in a new value in A1, then
write down the values of X20 and X21, change A1 again, and so on.
Is there a way to do that automatically? For example, define a range of
values for A1, and get a table that only lists, say, a column of the A1
values and 2 columns for the X20 nd X21 values?
I have checked out the "goal seek", "solve" and "scenario" options, but I
don't think thtey do what I want, and I don't think Pivot tables do that
either.
How can I do this in Excel (2003 or 2007, I have both).
 
E

Eduardo

Hi,
Do it simple , insert two rows at the top then let's say in cell B1 call it
X20 and B2 X21, then in C1 enter

=X20

and in C2

=X21

Each time you change the value in A3 it will give the results from X20 and 21
 
M

mikebo

Thanks, but that is not what I want. The problem is that I now have to change
the value and write down the result, then change the value again, write down
the result, etc. For 2 variables, that is probably doable (althouhg, even if
I wanted to change each variable to only 10 values, I would already have to
do that 100 times).

Here, I will give you an example: Let's ssay you want to calculate something
about real estate investments. There are many input variables: Principal,
mortgage rate, morgage terms, downpayment, points, appreciation,
depreciation, sales prices, capital gains tax rates, marginal tax rates,
etc., which cold all play into a decision to buy real estate. So, the
question may be: how does the mortgage rate influence the decision? If you
have the spreadhsheet with all the numbers calculated, you would have to go
in and change the mortgage rate a few times to see the result. Then you find
out that you can actually afford more house than you thought, so you change
the principal and have to run the mortgage rates again. Next thing might be
that you have to balance downpayment and monthly payments based on mortgage
rates.
You certainly don't want to write down (by hand) 200 or so numbers each time
you want to change some parameters. Can Excel do this automatically, or is
that too much to ask from it?
Really, what I would like is something where I can tell Excel:
Vary the value in A1 from 0 to 100 in steps of 10, and then give me a table
that lists those values and the values X20 and X21 (to stick with the
example) in a table.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top