0

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!

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

2 Answers 2

0

For VARA try to use this formula: =AVERAGEIF($A$2:$A$7,E2,$B$2:$B$7) enter image description here

For VARB try to use this formula: =AVERAGEIF($A$2:$A$7,E2,$C$2:$C$7) enter image description here

0
0

This array(CSE) formula solves the issue:

enter image description here

  • 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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .