| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average data with date and if constraints
=SUMPRODUCT(--(A2:A213=C224),--(A2:A213<=C225),--(W2:W213=1),H2:H213)/SUMPRODUCT(--(A2:A213=C224),--(A2:A213<=C225),--(W2:W215=1),H2:H213)
This is the suggestion one of you gave me, however it doesn't seem to work. col A are dates I've been working on this for hours and can't get it?? Please help |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average data with date and if constraints
Did NOT test. Try this arrary formula which must be entered using
ctrl+shift+enter =average(if(A2:A213=C224,A2:A213<=C225,W2:W213=1, H2:H213)) -- Don Guillett SalesAid Software "jrheinschm" wrote in message ... =SUMPRODUCT(--(A2:A213=C224),--(A2:A213<=C225),--(W2:W213=1),H2:H213)/SUMPRODUCT(--(A2:A213=C224),--(A2:A213<=C225),--(W2:W215=1),H2:H213) This is the suggestion one of you gave me, however it doesn't seem to work. col A are dates I've been working on this for hours and can't get it?? Please help |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average data with date and if constraints
when you say enter as array using cntrl, shift ,enter I don't
understand? remember I am A rookie at excel. thanks so much for the help. "Don Guillett" wrote: Did NOT test. Try this arrary formula which must be entered using ctrl+shift+enter =average(if(A2:A213=C224,A2:A213<=C225,W2:W213=1, H2:H213)) -- Don Guillett SalesAid Software "jrheinschm" wrote in message ... =SUMPRODUCT(--(A2:A213=C224),--(A2:A213<=C225),--(W2:W213=1),H2:H213)/SUMPRODUCT(--(A2:A213=C224),--(A2:A213<=C225),--(W2:W215=1),H2:H213) This is the suggestion one of you gave me, however it doesn't seem to work. col A are dates I've been working on this for hours and can't get it?? Please help |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average data with date and if constraints
That is because both parts of the formula are the same, so it will always
come to 1, or it would if the ranges were the same size. You have a W2:W215 which should be W2:W213 =SUMPRODUCT(--(A2:A213=C224),--(A2:A213<=C225),--(W2:W213=1),H2:H213)/SUMPR ODUCT(--(A2:A213=C224),--(A2:A213<=C225),--(W2:W213=1)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jrheinschm" wrote in message ... =SUMPRODUCT(--(A2:A213=C224),--(A2:A213<=C225),--(W2:W213=1),H2:H213)/SUMPR ODUCT(--(A2:A213=C224),--(A2:A213<=C225),--(W2:W215=1),H2:H213) This is the suggestion one of you gave me, however it doesn't seem to work. col A are dates I've been working on this for hours and can't get it?? Please help |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average data with date and if constraints
That formula will not work, you need
=AVERAGE(IF((A2:A213=C224)*(A2:A213<=C225)*(W2:W2 13=1),H2:H213)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Don Guillett" wrote in message ... when you create or edit a formula you then touch the enter key to leave the cell. Instead hold the ctrl & shift keys down while you touch the enter key. -- Don Guillett SalesAid Software "jrheinschm" wrote in message ... when you say enter as array using cntrl, shift ,enter I don't understand? remember I am A rookie at excel. thanks so much for the help. "Don Guillett" wrote: Did NOT test. Try this arrary formula which must be entered using ctrl+shift+enter =average(if(A2:A213=C224,A2:A213<=C225,W2:W213=1, H2:H213)) -- Don Guillett SalesAid Software "jrheinschm" wrote in message ... =SUMPRODUCT(--(A2:A213=C224),--(A2:A213<=C225),--(W2:W213=1),H2:H213)/SUMPR ODUCT(--(A2:A213=C224),--(A2:A213<=C225),--(W2:W215=1),H2:H213) This is the suggestion one of you gave me, however it doesn't seem to work. col A are dates I've been working on this for hours and can't get it?? Please help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| if data in one cell, then date in adjacent cell | Excel Worksheet Functions | |||
| compare date to column data | Excel Worksheet Functions | |||
| Limiting the range of a lookup function | Excel Worksheet Functions | |||
| Count multiple data by date | Excel Worksheet Functions | |||
| search for latest date | Excel Worksheet Functions | |||
Linear Mode
