If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
varying a cell and seeing the outcome
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). |
#2
|
|||
|
|||
varying a cell and seeing the outcome
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 "mikebo" wrote: 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). |
#3
|
|||
|
|||
varying a cell and seeing the outcome
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. "Eduardo" wrote: 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 "mikebo" wrote: 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). |
Thread Tools | |
Display Modes | |
|
|