Table using two indirect variables

[gundersen]

I have used the table function under data.
However I would like to use two variables (horisontal and vertical) which only affect the output formular indirectly.

Is it possible to create a table where the inputs (horisontal and vertical) only impact the content of the table indirectly?

Kind Regards
Poul Gundersen

 

[tusharm]

Can you provide an example of what you want?

I have used the table function under data.
However I would like to use two variables (horisontal and vertical) which only affect the output formular indirectly.

Is it possible to create a table where the inputs (horisontal and vertical) only impact the content of the table indirectly?

Kind Regards
Poul Gundersen

 

[gundersen]

I want to show my net wealth at the end of a period. My net wealth is defined as the value of my invested capital minus the value of my debt. If I earn 5 pct. on my invested capital but pays 10 pct. on my debt the two ammounts will be possible to calculate after eg. 5 years. My static input to my table outcome will be the present capital and debt, and the variable input will be the two interest rates on debt and capital, respectively.

The net wealth in five years will depend on the two interest rates and could be calculated in one formula, but my real example is a bit more complex and it is not possible to create a formular that relates directly to my inpunt.

Poul

 

[tusharm]

I want to show my net wealth at the end of a period. My net wealth is defined as the value of my invested capital minus the value of my debt. If I earn 5 pct. on my invested capital but pays 10 pct. on my debt the two ammounts will be possible to calculate after eg. 5 years. My static input to my table outcome will be the present capital and debt, and the variable input will be the two interest rates on debt and capital, respectively.

The net wealth in five years will depend on the two interest rates and could be calculated in one formula, but my real example is a bit more complex and it is not possible to create a formular that relates directly to my inpunt.

Poul

Let's stay with the real problem. Does it mean that if you were modeling this in Excel and were to enter the inputs in different Excel cells, there is no way to see the desired output in an Excel cell? If so, then I don't see how Excel can work for you. So, maybe, you can explain how you are using Excel for your problem.

 

[gundersen]

My example is


A B C D E
1
2
3 Initial capital 1000
4 Initial debt 700
5 Interest on capital 6%
6 Interest on debt 10%
7 Capital after 5 years 1338 =C3*(1+C5)^5
8 Debt after 5 years 1127 =C4*(1+C&)^5
9 Net wealth after 5 Y 211 =C7-C8

In this example I can calculate net wealth after 5 years to 211 for the values 6% in cell C5 and 10% in cell C6.

I would like to make a table that shows this "net wealth" with varying values of C5 and C6 - just like the Table function under data.

 

[gundersen]

Sorry, I tried to show a copy of a spreadsheet, but my spaces disappeared. I did not use column A. Hope you understand. Is there a way I can attach a small spreadsheet

 

[tusharm]

See Excel help (you have to be connected to the Internet and have selected 'show content from office.com') for 'Calculate multiple results by using a data table' particularly the section 'Create a two-variable data table'

What you want to do is just what that section explains.

My example is


A B C D E
1
2
3 Initial capital 1000
4 Initial debt 700
5 Interest on capital 6%
6 Interest on debt 10%
7 Capital after 5 years 1338 =C3*(1+C5)^5
8 Debt after 5 years 1127 =C4*(1+C&)^5
9 Net wealth after 5 Y 211 =C7-C8

In this example I can calculate net wealth after 5 years to 211 for the values 6% in cell C5 and 10% in cell C6.

I would like to make a table that shows this "net wealth" with varying values of C5 and C6 - just like the Table function under data.