Google Spreadsheet Creating sums based on adjacent cell criteria

Question:

( by MikeHardt )

Hello,

I am unable to solve a spreadsheet issue.  My goal is to write a formula that will average cells that meet a specific coded criteria.  I have coded a list of questions by an alphanumeric scheme.  There are 49 possible combinations (1-7; a-g)  e.g.,  Q#1; why is the sky blue?; code = 5e questions, Q#2 why is water wet?; code = 5e, Q#3 who let the dogs out?; code = 1a.  Each question has an attached percent correct value (p-value) that I want to add up by its coded criteria and find the average. e.g., 5e Q#1(.80) + Q#2(.90) = avg. .85.  I tried using a =LOOKUP formula, but it would only look up the first value that met the coding criteria.  Any assistance would be greatly appreciated.

A                     B                                  C                                         D

Coding             Percent Correct              Question Number                  Question

5e                    .80                                1                                 Why is the sky blue?             

5e                    .90                                2                                 Why is water wet?

1a                    .98                                3                                 Who let the dogs out?

3a                    .72                                4                                          

How is toothpaste made?

=

Code           Average

5e                .85

1a                .98

3a                .72

Additionally, if anyone knows a way in which I could aggregate coding from separate columns that could then be searched and averaged, that would be fantastic.  Thank you in advance for taking a look at this questions and providing some direction, or better yet a solution set-up.

A                B                  C                    D

criteria A     criteria 1        % Correct        Question

5                 e                  .80                   Q#1

5                 e                  .90                   Q#2

1                 a                  .98                   Q#3

3                 a                  .72                   Q#4

=

Code           Average

5e                .85

1a                .98

3a                .72

Solution:



Here is the screenshot for the spreadsheet containing the provided data (first part of question) :

Try this for your first part of question:

=QUERY(A:B;"select A,avg(B) group by A order by A desc label A 'Code', avg(B) 'Average'")

Put the above formulas in Cell F1 as shown in the above screenshot.

/////////// AND NOW THE SECOND PART OF THE QUESTION /////////////

Here is the screenshot for the spreadsheet containing the provided data (second part of question) :

Here is the formula for the solution for part two of the question:
=QUERY(arrayformula(IF({1,0};arrayformula(A:A&B:B);IF({0,1};C:C)));"select Col1,avg(Col2) where Col1 <>'' group by Col1 order by Col1 desc label Col1 'Code', avg(Col2) 'Average' ";1)


Put the above formulas in Cell F1 as shown in the above screenshot.


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive

A Single Arrayformula to Sum a Filter of All Rows

Question:

This is a link to a simplified version of the spreadsheet I'm trying to build.  

Following is the screenshot of the sheet "Data": 

Following is the screenshot of the sheet "Summary":

On the Summary sheet, I have filter formulas in columns B and C to sum the quantities from the Data sheet that meet two criteria.

The real spreadsheet is more complicated, and I would like to reduce the number of formulas to speed it up and make it more efficient.  Can I replace the filter formulas with one arrayformula per column (or is there a better option)?

Solution:

Keep the first two rows of sheet "Summary" same as it is and put the following formula in the cell A3 of the sheet "Summary":

=ArrayFormula(QUERY(IF({1,0,0};'Data'!B2:B;IF({0,1,0};if(year('Data'!A2:A)=B2;if(month('Data'!A2:A)=B1;ROUND('Data'!H2:H;1);"");"");IF({0,0,1};if(year('Data'!A2:A)=C2;if(month('Data'!A2:A)=C1;ROUND('Data'!H2:H;1);"");""))));"select Col1, sum(Col2), sum(Col3) where Col1 <> '' group by Col1 order by Col1 label sum(Col2) '',sum(Col3) '' ";0))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive