Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Spreadsheet 2 or sheet 2 - spreadsheet with variables

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!

share|improve this question
    
does this help?: office.microsoft.com/en-au/excel-help/… –  mcalex Mar 2 '13 at 23:56
1  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.