Solved

Summary Calculation for Responses in Excel

Posted on 2010-09-08
14
297 Views
Last Modified: 2012-05-10
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".  

Thank you in advance.

Bright01
Questionaire-v1.xls
0
Comment
Question by:Bright01
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 5

Expert Comment

by:n1wgk
Comment Utility
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 Comment

by:Bright01
Comment Utility
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
 
LVL 19

Expert Comment

by:MINDSUPERB
Comment Utility
Bright01,

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

Sincerely,

Ed
Questionaire-v2.xls
0
 
LVL 5

Expert Comment

by:n1wgk
Comment Utility
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 Comment

by:Bright01
Comment Utility
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 Comment

by:Bright01
Comment Utility
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.
Thanks in advance.
Bright01
 

Questionaire-v4.xls
0
 
LVL 5

Expert Comment

by:n1wgk
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 45

Expert Comment

by:patrickab
Comment Utility
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 Comment

by:Bright01
Comment Utility
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
 
LVL 5

Expert Comment

by:n1wgk
Comment Utility
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
 
LVL 5

Assisted Solution

by:n1wgk
n1wgk earned 200 total points
Comment Utility
This appears to work, though it may still be a little bit manual. You need to name the ranges.
Questionaire-v5.xls
0
 
LVL 45

Accepted Solution

by:
patrickab earned 300 total points
Comment Utility
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
 

Author Closing Comment

by:Bright01
Comment Utility
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
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
Bright01,

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

=SUMPRODUCT((Questions!$A$4:$A$2000=$D10)*(Questions!$D$4:$D$2000=Calculations!F$4)*VLOOKUP(Questions!$C$4:$C$2000,answers,2,0))

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now