# Excel: For row, if condition met, use to calculate average

I have an issue that I suspect has a relatively simple solution.

I want to calculate an average from a range of cells but only for those rows containing a cell meeting a condition.

Take the following data:

``````    A       B        C
1 | YEAR |  VARA  |  VARB  |
--------------------------
2 | 1990 |  1     |  6     |
3 | 1991 |  5     |  5     |
4 | 1990 |  2     |  6     |
5 | 1990 |  1     |  8     |
6 | 1991 |  2     |  2     |
7 | 1991 |  3     |  1     |
``````

For each YEAR, I want to return the average of VARA and VARB.

The results should be:

• 1990: VARA = 1.33; VARB = 6.66
• 1991: VARA = 3.33; VARB = 2.66

As I understand it, Excel doesn't have a FOR command allowing iteration and a subsequent IF statement... so does anyone know how this can be achieved?

Thanks!

• maybe `=AVERAGEIFS(B:B, A:A, 1990)` and `=AVERAGEIFS(C:C, A:A, 1990)`
– user385793
Sep 19, 2019 at 11:01
• It is common pivot table... Sep 19, 2019 at 11:23

For VARA try to use this formula: `=AVERAGEIF(\$A\$2:\$A\$7,E2,\$B\$2:\$B\$7)`

For VARB try to use this formula: `=AVERAGEIF(\$A\$2:\$A\$7,E2,\$C\$2:\$C\$7)`

This array(CSE) formula solves the issue:

• Formula in Cell `H217`:

`{=ROUND(AVERAGE(IF(COUNTIF(\$G217:\$G217,\$G\$209:\$G\$214),H\$209:H\$214,"")),2)}`

• Finish formula with Ctrl+Shift+Enter & fill across.

Adjust cell references in the formula as needed.