This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

Thank you in advance.

Bright01

Questionaire-v1.xls

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?

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

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

Sincerely,

Ed

Questionaire-v2.xls

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

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.

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

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

=SUMIF(Questions!$C$4:$C$2

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

Patrick

Questionaire-v1-01.xls

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?

Questionaire-v5.xls

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

=SUMPRODUCT((Questions!$A$

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

It's in the attached file.

Patrick

Questionaire-v1-03.xls

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.

All Courses

From novice to tech pro — start learning today.

> 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$

and using a Section number column.

It's in the attached file.

Patrick

Questionaire-v1-02.xls