If I understand, you want the reference to column A to be static?

Use absolute references:

=AVERAGE(IF(($A1:$A10="P01")+($A1:$A10="P02"),B1:B 10))


--
Biff
Microsoft Excel MVP


"--Viewpoint" wrote in message
...
So far so, good. But I think there might be an easier way to copy
formulas
with arrays. Currently, I'm and using "fill right" but then I must adjust
the "if" part of the array (that data is static for my purposes). Is
there a
method of copying formulas retaining the "if" part of the array so that I
only have to change the remainer of the formula?

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"--Viewpoint" wrote in message
...
This is the first chance I had to try the formula and it seems I typed
it
correctly and it appears OK. Thanks for the help.

"T. Valko" wrote:

Try one of these array formulas** :

=AVERAGE(IF((A1:A10="P01")+(A1:A10="P02"),B1:B10))

Or, use cells to hold the criteria:

D1 = P01
D2 = P02

=AVERAGE(IF((A1:A10=D1)+(A1:A10=D2),B1:B10))

=AVERAGE(IF(ISNUMBER(MATCH(A1:A10,D1:D2,0)),B1:B10 ))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"--Viewpoint" wrote in message
...
Thanks. I did have that formula and it didn't work but once you
wrote
it,
I
realized that the data was linked and that I had to add the sheet
name.

Once more question: Using the same formula, rather than using the
equal
sign, I want to select a group that contains PO1 or P02. How can I
do
that?

"T. Valko" wrote:

See if this works:

Array entered** :

=AVERAGE(IF(A1:A110="01",B1:B110))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

The problem is more than likely the 01. Excel doesn't recognize
leading
zeros as part of a numeric string. In order to get Excel to display
leading
zeros you either have to apply a custom number format or the entry
has
to
be
a TEXT string. In this case I'm "guessing" that 01 is a TEXT
string.

--
Biff
Microsoft Excel MVP


"--Viewpoint" wrote in
message
...
I am trying to find the formula for averaging cells in column B if
the
data
in Ais (a specific criteria). I've tried some formulas in the
threads
related to average, but nothing seems to work.

Example:
If A1:A110 equals 01
Then Average B1:B110 (cells related to the criteria in A1:A110)

When using formulas, should cells have a specific format?
Because
there
is
text involved, I set the cell format to general. Do you suggest
differently?
Also, I'm working with data that I exported from a Microsoft
Access
2007
database (SQL) but I'm using Excel 2003. Will this cause a
problem?