Average

[ApolloID]

Hi, i need an average formula with criteria.

Ex:

sheet1
555 1
882 3
555 8
555 5
321 1
555 20


in sheet2 i have:
555 and average formula (8.5)

I need an average formula like an Vlookup or sumif formula but to display the average for all "555".

Can this be done?
Thanks!

 

[jim may]

In Sheet2, Cell A1:

=AVERAGEIF(Sheet1!A1:A6,555,Sheet1!B1:B6)

 

[VoG]

Or

=AVERAGE(IF(Sheet1!A1:A6=555,Sheet1!B1:B6))

confirmed with CTRL + Shift + Enter.

 

[BGY23]

You can use a sumif to get the toal for 555 and a countif to get the number of instances of 555. one divided by the other will give you the average.

Say your data is in a1:b50. A contains the reference and b the total then:

=sumif(a1:a50,"555",b1:b50) divided by =countif(a1:a50,"555")

You can anchor the cells as required using $.

 

[ApolloID]

Thanks allot!!!!
All formulas work great!!!
Thanks!

 

[BGY23]

You're welcome. But something you should consider is if you have 555 with a value of "0" (zero) you will effect the average becuase the countif will include the instance but there will be not value.