How to average individual scores when various works done have different work difficulty

[jarrgonn]

Hello.

I'm trying to get the average of each person's work difficulty done for for different types of work. It's making it difficult for me because each person could randomly get whatever type of work and be graded on the difficulty level depending on how the work was done.

For example, Person 1 & 2 may be doing the same type of work but when it came to the point on how they basically did it & how difficult it was done, they could be graded differently. The only way i could simplify the raw data is to utilize a pivot, but due to the "sectioning-subsectioning", I'm unable to simply use the SUMPRODUCT formula to get the answer.

Hope the explanation makes sense.

Please see sample file attached.

[davsth]

You have not given us what you expect as an answer

Averageifs(a1:a31,c1:c31,"Person 1") person 1 could be a cell containing it

[jarrgonn]

Right, I missed out on indicating the answer.

Based on the sample file, and looking at the pivot table, Person 1 should have an average of 4.33 since he had 2 tasks done under WT 1 where one was graded as a 4 while the other one is a 5; plus 1 task under WT 6 that was graded as a 4. Therefore, (4+5+4)/3 = 4.33

[davsth]

=AVERAGEIF($C$2:$C$31,"Person 1",$A$2:$A$31)

[jarrgonn]

No wonder I'm so lost... I was looking at their whole department's numbers when I created my response above regarding the "desired output." It shouldn't be averaging all their work, per se. It should only average their Work Difficulty PER Work Type.

I'm really sorry about this to-&fro thing.

***

I'm trying to average individual scores/grades depending on the Work Difficulty level graded to them for specific Work Types. One Person [or others] may be assigned with the same Work Type but be graded differently for Work Difficulty; alternatively, one Person may be graded with Work Difficulty differently with the same Work Type.

I have provided [manually computed] the desired score for Persons 1, 5 & 6. Notice Persons 5 where he did the same Work Type [WT5] twice and was coincidentally grade the same Work Difficulty level; while with Person 6, he did the same Work Type [WT7] but was graded differently for both. Ultimately, the Count [if Work Type is done more than once] should FIRST be multiplied with the Work Difficulty level THEN averaged with the other Work Difficulty grades done.

The attached sample file has a minor edit under Person 6.

[JeteMc]

Looking at the way the pivot table is highlighted it appears that the last two arguments for the AVERAGE function in cell Q4 should be: E38 and E43 instead of E37 and E42.
If correct that cell should display 2.86
Let us know if you have any questions.

[jarrgonn]

Yes, you're right. Thank you for clarifying & responding. This is still an active problem & I'm still trying to figure it out. I just got lucky that the task's deadline was moved.

I edited the file. Please see attached.

[JeteMc]

This proposal bypasses the pivot table and uses the source data for the calculations.
The formula that breaks the average down by person and work type is: =IFERROR(AVERAGEIFS($C$2:$C$50,$A$2:$A$50,$P4,$B$2:$B$50,Q$3),"")
The formula that breaks the average down by person is: =IFERROR(AVERAGEIFS($C$2:$C$50,$A$2:$A$50,$P4),"")
Let us know if you have any questions.

[jarrgonn]

Thanks Jete! I just need to tweek the formula abit but this should help me big time.

'preciate this.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.