I have a complex calculation that is generated across multiple cells. It is not practical to create a single formula that generates the output.

The calculation has a single input number.

I would like to create a table with one column that has multiple values for that input number, and then auto-generates a second column that has the output of the complex, multi-cell calculation into a second column.

For example. A company is being purchased. There are a number of factors that influence the payouts to each individual shareholder. That is the complex, multi-cell calculation. But the key input is the value being paid for the business. I'd like to create a table that lists a range of values for purchase (e.g. $1,000,000-10,000,000), and then have a column that shows the payout to a specific individual across this range of values.

What is the right technique in Excel 2007 to do this?

Any help is greatly appreciated,

FG