# Summary Calculation for Responses in Excel

I have a workbook with two tabs.  A list of questions and a calculation table. In the sample, I've only included one of 10 different sections in order to keep it simple..... I can add to the other sections with the formula provided.   I want to have a formula in the calculation table that looks at the cell value in the questions tab ("True", "False", "Partial") and looks at the stage (1, 2, 3, 4 or 5) and based on the fact that the question falls within 1-5, have it calculate (additive) the value of all answers (True, False, Partial) / by the number of occurances (as to fomulate "an average"), placed in the correct column (1 - 5).  The value of True, False and Partial may be able to be changed on the Calculation Tab and presently has the value True = 2, Partial = 1 and False = 0.  What I'm trying to avoid is building out an entire table of answers; I think there is an elegant formula for calculating it with "if statements".

Bright01
Questionaire-v1.xls
###### Who is Participating?

Commented:
Bright01,

> How do I distinguish the "sections" so as to get the right total in the right cell?

Quite simply by using a formula like this:

=SUMPRODUCT((Questions!\$A\$4:\$A\$20=\$D10)*(Questions!\$D\$4:\$D\$20=Calculations!F\$4)*Questions!\$E\$4:\$E\$20)

and using a Section number column.

It's in the attached file.

Patrick
Questionaire-v1-02.xls
0

Would it be possible for you to also post a sample of what you are looking to do? I feel like I am "" this close to understanding what you are looking for. If I am nearly correct, I think there may be a way to calculate the answers.

Perhaps a series of answers and the desired outcome where you want to have the calculation show up? Perhaps I am misunderstanding what you are looking for?
0

Author Commented:
n1wgk,
I can post an example but can't do the calculation.  That's what I'm looking for.  So if you take the questionaire and answer True, False Partial to any of the questions, what I'm looking for is on the calculation.  So in the case of the first question (B4:C6), there are three answers, say; TRUE 1 FALSE 1 PARTIAL 1; they are all in Stage 1.  That would yield a total of 2+0+1 or 3 in the cell, F6, on the calcuations tab.  That cell represents the sum of the answers (T, F & P) on the questionaire in Stage 1.  The hard part is figuring how it identifies which stage (1-5) and what answer with what value and then adding it up and putting it in the right cell in the calculations tab.  In this case only in the "Information Security & Privacy" row and in cells (totals) in 1 - 5.
Does that help?

Questionaire-v2.xls
0

Commented:
Bright01,

Have a check the attached file to check if this what you want.

Sincerely,

Ed
Questionaire-v2.xls
0

Bright01

I guess I am being obtuse here...okay, let me walk through this and please correct my assumptions and answer the questions. I think we can find an answer here (or maybe even design something better).

We have several "knowns" but I need to understand how they interact (in a manual way) so that we can figure out how to automate it.

It appears that "Question 1" is in fact 3 parts, under Information Security and Privacy, the sum of which makes up a "number". This number is then applied under the Calculations tab in the corresponding "Section" named Information Security & Privacy, under the correct "Number" column corresponding to Question 1, which you call "Level".

If that is the case, and I will be going with actual cell positions, not your labels, this is what I envision:
If B4 = True, the value for B4 should = the corresponding number under Calculations cell C2 (in this case, 2)
If B5 = False, the value for B5 should = the corresponding number under Calculations cell C3 (in this case, 0)
If B6 = Partial, the value for B6 should = the corresponding number under Calculations cell C4 (in this case, 1)
Because all three questions correspond with "Level" or "Number" 1 under Information Security and Privacy, they should be added together (2+0+1=3) and that number should be placed in cell F10

Take a look at my version of what you have and see if it makes sense.

Questionaire-v3.xls
0

Author Commented:
Mindsuperb,
Thanks for the response.........A few of the answers are correct but the means by which you got the answer won't work or scale  (I have over 100 questions in the complete spreadsheet).  The formula  has to look at the column (Questions Column C), determine the appropriate stage (i.e. 1-5) and then determine if the answer is True, False or Partial or blank, then add the appropriate value (in Cells C2 - C4 in Calculations - 2 if True, 1 if Partial, 0 if False or blank) to all other values meeting the stage criteria and put the result in the appropriate stage cell (Calculations F4 - J4).  I think you're on the right track with either an if statement set or a lookup table.
B.
0

Author Commented:
n1wgk,
Almost got it!!!!!  The math is correct and the VTable look up is, I think, the right approach.  I also realize that you may need more of the spreadsheet to see how it must scale so I've sent you a new one with more cells and your calculation included.  Do we have to display "the values" (col. D in Questionaire?) or can that be worked into the math/formula in "calculations"?  The added complication with additional categories, is that it must recognize the category (e.g. stewardship, value creation, etc.) or better yet, make it = to the category that is being summed so as not to have to spell out each category....that way it knows to sum the stage within category.
Bright01

Questionaire-v4.xls
0

The easiest way is to hide the D column under Questions...I am playing with the formula now to see about getting this incorporated.
0

Commented:
Bright01,

You could use a helper column in the Questions sheet and then use:

=SUMIF(Questions!\$C\$4:\$C\$20,Calculations!F\$4,Questions!\$D\$4:\$D\$20)

to summarize the results. It's in the attached file.

Patrick
Questionaire-v1-01.xls
0

Author Commented:
Patrickab,
Brilliant!  You and n1wgk are getting me to my destination here.  I sent n1wgk an updated file with two additional "sections".  How do I distinguish the "sections" so as to get the right total in the right cell?  Your formula works but I have to be able to scale it beyond C4:C20 and distinguish categories.  In otherwords how do I keep from hardcoding the cell references for sections?  Should I use range names instead or can I use the entire column and have it also look up the section reference?
0

Range Names would certainly work and would be less confusing to the eye. The only other option (as I see it) would be to put category numbers (6 for Information Security & Privacy) next to each sub-question in another helper column and somehow have the criteria work on 2 different checks...let me play a minute with that thought.
0

This appears to work, though it may still be a little bit manual. You need to name the ranges.
Questionaire-v5.xls
0

Author Commented:
Great job you guys!  I had to tinker with it to make it scale but I got a good understanding of how to make it work.  May ask a related question but much thanks to both of you for your help on this.
0

Commented:
Bright01,

If you use the following formula you do not need a 'helper column' for the values to the responses:

I have increased the rows in the formula to 2000 - adjust as needed.

It's in the attached file.

Patrick

Questionaire-v1-03.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.