0

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.

Bram Vanroy
  • 27,032
  • 24
  • 137
  • 239
  • Can you clarify why "nothing fruitful" came of your attempts with AVERAGEIF (the English name of that function does not contain a dot)? What precisely did you try? – XOR LX Feb 11 '16 at 09:08
  • @XORLX Probably because I'm not as good with Excel as I should be. I tried `=AVERAGEIF('analyse-logs'!B:B; A=1)` to get values from tab `analyse-logs`, all the values in column B for which it is true that `A=1`, but I got an error that I can't divide by zero, even though there are many values for which it is true that a cell in the first column `A=1`. – Bram Vanroy Feb 11 '16 at 09:16
  • The syntax would be: =AVERAGEIF('analyse-logs'!A:A;A1,analyse-logs'!B:B) where A1 contains your value of interest, e.g. 1. This value can also be directly entered into the formula as the criteria in place of a cell reference, viz =AVERAGEIF('analyse-logs'!A:A;1,analyse-logs'!B:B), though obviously this is less flexible (the first version could be copied down to further cells to give, successively, averages for the same range but for values in A2, A3, etc.). – XOR LX Feb 11 '16 at 09:34
  • @XORLX - I'm reasonably sure it will get picked up but your formulas combine both semi-colons and commas as list separators. –  Feb 11 '16 at 10:12
  • @Jeeped Thanks. WIll correct. Edit: Unable to now. – XOR LX Feb 11 '16 at 10:21
  • Should be (semicolon version): AVERAGEIF('analyse-logs'!A:A;A1;analyse-logs'!B:B) – XOR LX Feb 11 '16 at 10:22
  • @XORLX A numeric value works, e.g. `=AVERAGEIF('analyse-logs'!A:A;A1;'analyse-logs'!B:B)`, however using a cell value doesn't. (Do note that you forgot a quote character.) – Bram Vanroy Feb 11 '16 at 10:45
  • My bad, I did something silly. Could you post it as an answer and I'll accept! – Bram Vanroy Feb 11 '16 at 10:59

3 Answers3

2

Try:

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

Regards

XOR LX
  • 7,632
  • 1
  • 16
  • 15
0

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

M.L
  • 328
  • 2
  • 12
0

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.

lenwe
  • 100
  • 4
  • By that are you inferring that AVERAGEIF is inappropriate here? – XOR LX Feb 11 '16 at 10:38
  • I didn't say `AVERAGEIF` is inappropriate, I merely suggested a solution that worked for me. – lenwe Feb 11 '16 at 10:44
  • Then to what does the "instead" refer? I presumed that you meant "instead of your current attempt at using AVERAGEIF". – XOR LX Feb 11 '16 at 11:14