Get average based on value in another row

Question

I have values in an Excel file like this:

QR  |  QR AVG  |  val1  |
q1                5
q1                3
q1                4
q2                7
q2                9
q3                10
q3                11
q3                12
q3                11
q4                5
q5                5
q5                7

And I would like the QR AVG field to represent the average value partitioned by different QR values. In other words, I'd like to have the following values after my calculation:

QR  |  QR AVG  |  val1  |
q1     4          5
q1     4          3
q1     4          4
q2     8          7
q2     8          9
q3     11         10
q3     11         11
q3     11         12
q3     11         11
q4     5          5
q5     6          5
q5     6          7

Where I don't know the exact number of rows that I will have, and I will be intermittently adding rows randomly into the table.

I would prefer not to write a macro to do this if possible. Any idea how I might go about this?

Answer 1

Create a pivot table at the same spreadsheet.

Select columns A, B and C (Sheet1!$A:$C). Click "Insert", and then "Pivot Table".

Place QR at "Row Labels" field and Val1 (as average) at the "Values" field.

Then, use in B2:

=VLOOKUP(A2,$E$2:$F$6,2,1)  

Drag the formula to all cells in column B.

It will look like this:

:

Answer 2

Try this: =AVERAGEIF($A$2:$A$13,A2,$C$2:$C$13)

That should go in column B (QR Average) in each cell. The middle param will automatically change, of course.

Answer 3

Try this function adjusting ranges inside:

=AVERAGEIF($A$2:$A$17,A2,$C$2:$C$17)