Average scores when two questions asked together

[noviceformulafan]

Good Afternoon.

This may have been asked in the past, I've just never seen it.
This forum has helped my excel ability increase tenfold and as a consequence I've been tasked with finding the average score (total) of two questions when asked together.

I have been able to find the number of times questions have been asked together, and the pass rates when this happens too. I have also found the average scores per question.

I just can't seem to figure out how to find the average of the two questions when asked together or when its a pass or fail for the candidate.

I've included an image in smaller scale of what I'm working with.

Any help would be appreciated!

 

[Saba Sabaratnam]

My spreadsheet layout for your data is presented below.

Enter the following formulas

I4 =AVERAGEIFS(B:B,C:C,">=0")
J4 = AVERAGEIFS(C:C,B:B,">=0")
I5 =AVERAGEIFS(B:B,C:C,">=0",D:D,"Pass")
J5 =AVERAGEIFS(C:C,B:B,">=0",D:D,"Pass")

Kind regards

Saba

 

[noviceformulafan]

Hi Saba.
Thanks for this, it's really helpful!

Is it possible to make a formula for the average combined score when both questions are asked ((9+8+10+7+0+8+10+8+10+9)/5=15.8)
and when the result is a pass
((9+8+10+7+10+8+10+9)/4=17.75) based on the examples you used?

Regards,

Adam

 

[Saba Sabaratnam]

Hi Adam,

You will need a helper column to achieve these results.

Enter the following formula in E2 and copy it down.

=IF(COUNTIFS(B2:C2,">=0")=2,SUM(B2:C2),"")

Then enter the following formulas:

I7 = AVERAGE(E:E)
I8 =AVERAGEIFS(E:E,D:D,"Pass")

Kind regards

Saba

 

[noviceformulafan]

Hi Adam,

You will need a helper column to achieve these results.

Enter the following formula in E2 and copy it down.

=IF(COUNTIFS(B2:C2,">=0")=2,SUM(B2:C2),"")

Then enter the following formulas:

I7 = AVERAGE(E:E)
I8 =AVERAGEIFS(E:E,D:D,"Pass")

View attachment 42195


Kind regards

Saba

Hi Saba.

Thanks so much for this!

I have 20 columns and want to do the same for each (question a and question b, question a and question c etc)

Is it just a case of having a helper column for each then?

Thanks again!

Adam

 

[Saba Sabaratnam]

Hi Adam,

Is combination of tests always two (e.g A-B, B-C, A-C) and do you keep all test results in single worksheet in multiple columns?

Can you please a screen shot of your actual database / worksheet with made up data?

Kind regards

Saba

 

[noviceformulafan]

Hi Saba,

I've attached an example of the worksheet I use and how the data appears.

That's the 'live' number of questions we have (24) but this increases and decreases as questions are added and deleted.

I've been working up to now combining two questions at a time, as I have no idea how to increase that.
Every candidate has 6 questions in total and it would help massively if I could work it for all 6, but that's at a level well past my understanding (although it would help)

In AE34 and AD35 I have the formula =AVERAGEIFS(C:C,D:D,">=0")+AVERAGEIFS(D:D,C:C,">=0")
In AE40 and AD41 I have the formula =AVERAGEIFS(C:C,D:D,">=0",B:B,"Pass")+AVERAGEIFS(D:D,C:C,">=0",B:B,"Pass")

I think that formula works for the average pass rates of questions asked together, but hopefully you can help as you already have done

.

Thanks again!

Adam