**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:

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,

## No comments:

## Post a Comment