# Complicated Formula Using Multiple Inputs

Some really good threads here but couldn't find what I am looking for.

My formula is very complex on 1 spreadsheet (sheet). There are 6 variables to input that eventually calculates a single answer. I want to change the variables to compare the outputs (answers) for investing decisions. Now the problem is that since the formula is so long and complex, it is impractical to keep copying and pasting the formula then syncing each variable. Is there a way to use the 1 formula like some kind of "Master" formula and run through many different variables?

Here's a short and clear example.

Spreadsheet 1 or sheet 1- containing complex formula

``````input a
input b
input c
input d
input e
input f

answer = input a + input b + input c + input d + input e + input f
``````

``````input a =  1    input a  = 1   input a = 1
input b =  1    input b  = 1   input b = 1
input c =  0    input c  = 1   input c = 1
input d =  0    input d  = 0   input d = 1
input e =  0    input e  = 0   input e = 0
input f =  0    input f  = 0   input f = 0
``````

This is the input/variables page. By using spreadsheet 1 and plugging the inputs from spreadsheet 2 to spreadsheet 1, the `answer = 1 + 1 + 0 + 0 + 0 + 0 = 2`. Easy. But now, the challenge is, how can I input the next set of inputs into spreadsheet 1 without affecting the answer I just got in spreadsheet 1? If we continue, the next inputs from spreadsheet 2 are `1 + 1 + 1 + 0 + 0 + 0` after which spreadsheet 1 calculates to 3.

Is there someway to reference the formula for every calculation so that it's not fixed? Maybe I'm asking the wrong questions. I'm just trying to use this single complex formula by using many inputs to calculate multiple answers that can be analyzed. Any ideas?

Thanks so much!

-
does this help?: office.microsoft.com/en-au/excel-help/… – mcalex Mar 2 '13 at 23:56
Sounds like a good use of data tables. Check out Hui's tutorial at Chandoo's website chandoo.org/wp/2010/05/06/… – dav Mar 3 '13 at 1:14
Thanks David! That def helped. – Stefan Mar 3 '13 at 16:59