Excel Array Formula?

  • Thread starter Thread starter Tzy
  • Start date Start date
T

Tzy

Hello, hope someone out there can help me out a bit.

This is the problem. I have 2 sheets. One with a summary and calculations
and one sheet with data.
In the summary sheet (Sheet1), I have names which also appear in the data
sheet (Sheet2). However the problem is that in the data sheet the name will
appear several times. I want a function that does the following:
For example:
Calculate the average of column C in Sheet2 for all the rows that contain
the name Charlie.
Anyone have any solution?

Help appreciated. Thanks
 
=AVERAGE(IF(Sheet2!A2:A50="Charlie",Sheet2!B2:B50))

array entered with ctrl + shift & enter

replace "Charlie" with a cell reference and put the name there instead, that
way you don't need to edit the formula when you change the name

You can also use a non array formula

=SUMIF(Sheet2!A2:A50,"Charlie",Sheet2!B2:B50)/COUNTIF(Sheet2!A2:A50,"Charlie")

Finally if you use Excel 2007 there is a new function called AVERAGEIF

--


Regards,


Peo Sjoblom
 
Back
Top