I am trying to find an average purchase price (listed in column H) for
purchase dates (column A) that occur between 11/01/1998 and 12/01/1998
and an additional criteria - that column G has "Pre-Period".

Right now I have this formula:

=AVERAGEIFS
(H2:H809,A2:A5121,">=11/1/1998",A2:A5121,"<12/01/1998",G2:G809,"Pre-
Period")


This is returning #VALUE

Any suggestions on how to make this work?

THANK YOU!

Try this array formula

=AVERAGE(IF((A2:A5121>=--"1998-11-01")*(A2:A5121<--"1998-12-01")*(G2:G5121="Pre-Period"),H2:H5121))

--
__________________________________
HTH

Bob

"SK08" <(E-Mail Removed)> wrote in message
news:49dffdc2-97bd-45c6-bf26-(E-Mail Removed)...
>I am trying to find an average purchase price (listed in column H) for
> purchase dates (column A) that occur between 11/01/1998 and 12/01/1998
> and an additional criteria - that column G has "Pre-Period".
>
> Right now I have this formula:
>
> =AVERAGEIFS
> (H2:H809,A2:A5121,">=11/1/1998",A2:A5121,"<12/01/1998",G2:G809,"Pre-
> Period")
>
>
> This is returning #VALUE
>
> Any suggestions on how to make this work?
>
> THANK YOU!

is there anyway to do it without an array? The averageifs function for
2007 should work...






On Mar 12, 2:03*pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
> Try this array formula
>
> =AVERAGE(IF((A2:A5121>=--"1998-11-01")*(A2:A5121<--"1998-12-01")*(G2:G5121=*"Pre-Period"),H2:H5121))
>
> --
> __________________________________
> HTH
>
> Bob
>
> "SK08" <sakilke...@gmail.com> wrote in message
>
> news:49dffdc2-97bd-45c6-bf26-(E-Mail Removed)...
>
>
>
> >I am trying to find an average purchase price (listed in column H) for
> > purchase dates (column A) that occur between 11/01/1998 and 12/01/1998
> > and an additional criteria - that column G has "Pre-Period".
>
> > Right now I have this formula:
>
> > =AVERAGEIFS
> > (H2:H809,A2:A5121,">=11/1/1998",A2:A5121,"<12/01/1998",G2:G809,"Pre-
> > Period")
>
> > This is returning #VALUE
>
> > Any suggestions on how to make this work?
>
> > THANK YOU!- Hide quoted text -
>
> - Show quoted text -

>=AVERAGEIFS(H2:H809,A2:A5121,">=11/1/1998",A2:A5121,"<12/01/1998",G2:G809,"Pre-Period")

Your ranges aren't the same size:

H2:H809
A2:A5121
G2:G809

Use cells to hold the criteria:

A1 = 11/1/1998
B1 = 12/1/1998
C1 = Pre-Period

=AVERAGEIFS(H2:H5121,A2:A5121,">="&A1,A2:A5121,"<"&B1,G2:G5121,C1)

Make sure the ranges are all the same size.


--
Biff
Microsoft Excel MVP


"SK08" <(E-Mail Removed)> wrote in message
news:56ce8e2c-695d-4aa8-bd26-(E-Mail Removed)...
is there anyway to do it without an array? The averageifs function for
2007 should work...






On Mar 12, 2:03 pm, "Bob Phillips" <Bob...@somewhere.com> wrote:
> Try this array formula
>
> =AVERAGE(IF((A2:A5121>=--"1998-11-01")*(A2:A5121<--"1998-12-01")*(G2:G5121=*"Pre-Period"),H2:H5121))
>
> --
> __________________________________
> HTH
>
> Bob
>
> "SK08" <sakilke...@gmail.com> wrote in message
>
> news:49dffdc2-97bd-45c6-bf26-(E-Mail Removed)...
>
>
>
> >I am trying to find an average purchase price (listed in column H) for
> > purchase dates (column A) that occur between 11/01/1998 and 12/01/1998
> > and an additional criteria - that column G has "Pre-Period".
>
> > Right now I have this formula:
>
> > =AVERAGEIFS
> > (H2:H809,A2:A5121,">=11/1/1998",A2:A5121,"<12/01/1998",G2:G809,"Pre-
> > Period")
>
> > This is returning #VALUE
>
> > Any suggestions on how to make this work?
>
> > THANK YOU!- Hide quoted text -
>
> - Show quoted text -