| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGESIFS Function - average cells that fall within a date rangeand meet additional criteria
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AVERAGESIFS Function - average cells that fall within a date range and meet additional criteria
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
AVERAGESIFS Function - average cells that fall within a daterange and meet additional criteria
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 - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| countif two corresponding cells meet the criteria | Excel Worksheet Functions | |||
| Excel-only average cells if two cells in same row, meet two condit | Excel Worksheet Functions | |||
| Count Cells that meet Criteria | Excel Worksheet Functions | |||
| Can I sum #s in a cell if other cells meet a certain criteria? | Excel Discussion (Misc queries) | |||
| Sum range of values that meet date criteria | Excel Discussion (Misc queries) | |||
Linear Mode
