Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional w/ arrays
I am trying to get the average of several numbers using arrays. I want
average only if the date with the values are associated with a date range. {=AVERAGE(IF(AND($E$2:$E$6<I6,$E$2:$E$6<I5),$D$2:$ D$6,""))} |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional w/ arrays
I've never seen AND and OR work in an array formula. You can get around this
by using multiplication for AND and addition for OR: =AVERAGE(IF(($E$2:$E$6<I6)*($E$2:$E$6<I5),$D$2:$D$ 6,"")) although I think you could write the formula as =AVERAGE(IF($E$2:$E$6<MIN(I6,I5),$D$2:$D$6,"")) "PAL" wrote: I am trying to get the average of several numbers using arrays. I want average only if the date with the values are associated with a date range. {=AVERAGE(IF(AND($E$2:$E$6<I6,$E$2:$E$6<I5),$D$2:$ D$6,""))} |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional w/ arrays
Try this. (array entered)
I assume the dates to average are inclusive of the dates in I5:I6. =AVERAGE(IF((E2:E6=I5)*(E2:E6<=I6),D2:D6)) -- Biff Microsoft Excel MVP "PAL" wrote in message ... I am trying to get the average of several numbers using arrays. I want average only if the date with the values are associated with a date range. {=AVERAGE(IF(AND($E$2:$E$6<I6,$E$2:$E$6<I5),$D$2:$ D$6,""))} |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use of arrays | Excel Worksheet Functions | |||
Use of arrays | Excel Worksheet Functions | |||
Fantasy Football Conditional Arrays | Excel Worksheet Functions | |||
Need help with arrays (I think) | Excel Worksheet Functions | |||
Arrays | Excel Worksheet Functions |