Excel: calculate averages based on other column's value

Question

I have a datasheet containing two huge columns, both with integer values. The values in one column range from 1-11, the other from 0-22. The values aren't sorted in any way. In a separate sheet (same file) I want to show averages per step.

Example:

A   B
-------
2   3
4   0
6   2
2   9
4   12
1   9
1   18

In the other tab, I would like to see:

Values A   Average B   Values B   Average A
--------------------------------------------
 1          14          0          4
 2          6           2          6
 4          6           3          2
 6          2           9          2
                        12         4
                        18         1

I'd like to solve this with a function that looks loops through the values of each column, and during that loop takes an average of the rows in the column that correspond to that value. Manually, this would be too much of a hassle.

I tried working with AVERAGEIF but nothing fruitful came out of it.

Answer 1

Try:

=AVERAGEIF('analyse-logs'!A:A;A1;'analyse-logs'!B:B)

Regards

Answer 2

You could try using AVERAGEIFS instead. This worked for me.

=IFERROR(AVERAGEIFS('analyse-logs'!B:B, 'analyse-logs'!A:A, A1), 0)

The code above is for A values. A1 refers to the cell where your value is referenced (or you could use A=1 like you did in your original function). I used IFERROR in this case so that references with no matches will return 0 as the average.

Answer 3

If you are comfortable building a pivot table, you can build a pivot table on your data. And use average of B in values when A is the rows field. Should yield you the result. Vice-versa should yield the right half of the result.

If you want to see both of them at the same time, one tab can have pivot with A in rows field and average of B in values field. You can build another pivot in another sheet with B in rows field and average of A in values field.

If you want the results side-by-side (2 columns of first part and the next two columns of the 2nd part), you can get pivot tables to be positioned next to each other (Move a Pivot Table) Since the source data is going to be identical, if there are any changes in the base data, a simple Refresh Pivot (Alt+F5) should update the values.

(If you haven't done this before) How to get average in Values field: Change the summary function for a field in a PivotTable report