Solved

Summary Calculation for Responses in Excel

Posted on 2010-09-08
14
300 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
ID: 33629306
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
ID: 33629428
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
ID: 33630315
Bright01,

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

Sincerely,

Ed
Questionaire-v2.xls
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 5

Expert Comment

by:n1wgk
ID: 33630433
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
ID: 33630451
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
ID: 33630628
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
ID: 33630808
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
 
LVL 45

Expert Comment

by:patrickab
ID: 33630835
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
ID: 33630891
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
ID: 33630968
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
ID: 33631099
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
ID: 33631156
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
ID: 33631906
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
ID: 33638758
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

810 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