Return an average of multiple values from specific week

[mikkisbee]

Hi,

I have this situation, that I have process lead time results from pieces going trough process.

A B
Value Week
5 44
10 45
6 48
5 49
9 46
1 48
3 50
9 49
2 49
6 49
4 50

I want to return an average from column A values from specific week. For example for week 49, it should return 5,5. ((6+2+9+5)/4)

From what I understand, The VLOOKUP function cant handle multiple values. I think I should combine INDEX, MATCH and AVERAGE functions but how?


I'd be really thankful if someone has a solution for this.

[Sam Capricci]

I would think some combination of sumif and countif would work for this like... =SUMIF(B:B,49,A:A)/COUNTIF(B:B,49)

[Phuocam]

Use AVERAGEIF function:

=AVERAGEIF(B2:B20,49,A2:A20)

[mikkisbee]

Thank you Sambo kid and Phuocam! Both of your suggestions worked perfectly in my case.

[mikkisbee]

Bonus question: Is it possible to give range for criteria? For example weeks 46-48.

[Tony Valko]

Try this...

D1 = 46
E1 = 48

=AVERAGEIFS(A1:A11,B1:B11,">="&D1,B1:B11,"<="&E1)

[mikkisbee]

AVERAGEIFS -function works! Thanks Tony!

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Tony Valko]

You're welcome. Thanks for the feedback!