averageif for a range of values in another column
 

I need to take the average of colum A for the cells that have either a 1 or a
2 in the corresponding rows of column B. How do I do this?

averageif for a range of values in another column
 

On Apr 26, 9:21*am, -ken wrote:
I need to take the average of colum A for the cells that have either a 1 or a
2 in the corresponding rows of column B. How do I do this?

One way, for first 100 rows...

=SUMPRODUCT(A1:A100,--((B1:B100=2)+(B1:B100=1)))/SUMPRODUCT(--
((B1:B100=2)+(B1:B100=1)))

Ken Johnson

averageif for a range of values in another column
 

Try this array formula** :

=AVERAGE(IF(B1:B20={1,2},A1:A20))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"-ken" wrote in message
...
I need to take the average of colum A for the cells that have either a 1 or
a
2 in the corresponding rows of column B. How do I do this?