Turn Excel spreadsheet into a formula

Question

I have an Excel spreadsheet that has a complex computation that is not trivial to turn into a macro or a single-cell formula. The spreadsheet has about 10 different inputs (values a human enters in different cells of the spreadsheet) and then it outputs 5 independent calculations (in 5 different cells) based on that input. Their calculation is using some pre-entered data in the spreadsheet (about 100 different constants) and doing some look-ups on them.

I would like to use this whole spreadsheet as a formula on a different spreadsheet to calculate a set of input values and produce the corresponding set of output values. Imagine this as creating different table with 10 columns for the input variables and 5 columns for the outputs, then copying each input into the other spreadsheet and copying back the output in the results table.

For instance:

• A1, A2, A3,... A10 are cells where someone enters values
• through a series of calculations B1, B2, B3, B4 and B5 are updated with some formulas

Can I use the whole series of calculations from A1...A10 into B1...B5 without creating one massive huge formula or a VBA macro?

I want to have a set of input values in 100 rows from A100, B100, C100,... J100 onward then do some Excel magic that will:

1. Copy the values from A100...J100 into A1 to A10
2. Wait for the result to appear in B1 to B5
3. Copy the values from B1 to B5 into K100 to O100
4. Repeat steps 1 to 3 for all rows from 100 to 150

Solution

You might be able to do that with Excel's What-If Analysis. I'm not familiar enough with it to say. You can do it with a relatively simple macro.

At base, the macro would work like this:

Open the formula workbook
Set calculation to manual
Loop thru the rows you want to apply the formula to
   Take inputs on current row of result sheet and enter them in the formula workbook
   Force Excel to recalculate
   Take results of formula workbook and enter them in the result sheet
End loop
Turn calculation back on to automatic.

There are two ways to transfer the data in rows in your results workbook to the formula workbook in columns. One way is to copy & paste using Transpose to convert the row to a column, the other is to use offsets where for one range you are offsetting the column and the other range you are offsetting the row.

An example of the latter is as follows.

    ' set ranges to top left cell
    Set rngFrom = Workbooks("Result.xls").Worksheets("Sheet1").Cells(iCurrentRow, 1)
    Set rngTo = Workbooks("Formula.xls").Worksheets("Sheet1").Range("A1")

    ' transfer values
    For i = 0 To 9
        rngTo.Offset(RowOffset:=i).Value = rngFrom.Offset(ColumnOffset:=i).Value
    Next i

If you need specific help with writing a VBA macro, let us know.

Comments

[JMax]

your question is really unclear. We can't understand what you mean, what you intend to do. Show us what the inputs, your expected outputs and so on

[Георги Кременлиев]

I have updated some of the question. What I really need is a way to use a series of calculations as a single formula. Hope that this makes the question a bit more clear.

[Георги Кременлиев]

Not really what I was looking for. I tried to update the question with more details.

[Avi]

Hi Doug, I would like to be more specific: The question isn't about how to handle multiple inputs, but how to handle multiple values for the same input (e.g. calculate once of an interest value of 5% , once for an interest value of 6% and so on. Can you explain this?

[dougp01]

Sounds like you are doing the classical "what-if" study only it involves an external data set. You will want to find a way to do all calculations in the working spreadsheet. What-if was one of the original purposes of the spreadsheet as conceived by Dan Bricklin. Correct me if I'm wrong but you want to see several experimental values calculated from a single data set. If you can express the math for a cell in a formula, you should be able to use a combination of relative and absolute addressing within the various types of lookup functions. If I'm off base here can you provide a sample?

[dougp01]

Specifically I like to use the column and row headers as the actual values. For example, you would have column headings as numeric values of 5%, 6%, etc. Your formulas would need lookup functions or Index() and Match()