AVERAGESIFS Function - average cells that fall within a date range and meet additional criteria
=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" wrote in message
...
is there anyway to do it without an array? The averageifs function for
2007 should work...
On Mar 12, 2:03 pm, "Bob Phillips" 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" wrote in message
...
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 -
|