Embedded zero function in data table

[Cordeslucas]

All,

first, thank you in advance for any and all advice. I have searched all over the internet and on this site as well.

I am having issues with a two variable data table:

background. I am solving for an IRR, based off of a build cost, rate, and performance ratio for a given set of assumptions.

I am wanting to create a two variable data table, which would return the rate I would need, with given performance ratios and build costs, in order to retain a 10% IRR.

my first step is to run a goal seek, solving for a 10% IRR, by changing the rate.

I then copy and hard code a few values at the bottom of the sheet, allowing me to back into this rate (necessary to complete the formula I need for the data table to work).

I then put this formula into my data table, with the row inputs being the build cost and the column inputs being the performance ratio.


here is where lies the problem. the data table is only giving me the same rate that I have solved for in the top left of my data table.

if I run a one variable table, I am able to solve for the rate with various performance ratios.


I cannot figure out where I am going wrong.

want to avoid macros, as this will be shared with customers.

many thanks

[Kaper]

Sounds it could be a problem with relative addressing, but to be sure:
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic