Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date range table array formula
OK I am still trying to figure out an old problem OK if make the sheet simple A1 A2 A3 EMP # Date EMP Profile code TAble array is set up like L1 M1 N1 O1 Agent # Start Date End Date EMP profile code 177721 1-1-2009 5-31-2009 1234 177721 6-1-2009 12-31-2009 5678 Ok so the big issue is the emp will chage profile peroidically thoughout a calander year. I am trying to get were in A1 is you enter the EMP # and then in B1 you enter a random date the correct profile code will come up in C1 based on the date range in the of table array the emp was profiled to the code =SUMPRODUCT(--(A1=L1:L9),--(B1=M1:M9),--(B1<=N1:N9),O1:O9) if not woeking me only, it sticks a 0 regardless of the emp and date entered. completely open to re-structering the array is needed. As matter of fact the table array is a work in progress |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date range table array formula
Hi,
Try this. Please adjust the range as desired. SUMPRODUCT((C6:C7=C9)*(D6:D7<=D9)*(E6:E7=D9),F6:F 7) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com " wrote in message ... OK I am still trying to figure out an old problem OK if make the sheet simple A1 A2 A3 EMP # Date EMP Profile code TAble array is set up like L1 M1 N1 O1 Agent # Start Date End Date EMP profile code 177721 1-1-2009 5-31-2009 1234 177721 6-1-2009 12-31-2009 5678 Ok so the big issue is the emp will chage profile peroidically thoughout a calander year. I am trying to get were in A1 is you enter the EMP # and then in B1 you enter a random date the correct profile code will come up in C1 based on the date range in the of table array the emp was profiled to the code =SUMPRODUCT(--(A1=L1:L9),--(B1=M1:M9),--(B1<=N1:N9),O1:O9) if not woeking me only, it sticks a 0 regardless of the emp and date entered. completely open to re-structering the array is needed. As matter of fact the table array is a work in progress |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date range table array formula
i guess I am cunfused as to the range one would =C9
and range 2 would = D9 same for the third. If my data wher typed in A1 B1 and the formula to produce in C1 "Ashish Mathur" wrote: Hi, Try this. Please adjust the range as desired. SUMPRODUCT((C6:C7=C9)*(D6:D7<=D9)*(E6:E7=D9),F6:F 7) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com " wrote in message ... OK I am still trying to figure out an old problem OK if make the sheet simple A1 A2 A3 EMP # Date EMP Profile code TAble array is set up like L1 M1 N1 O1 Agent # Start Date End Date EMP profile code 177721 1-1-2009 5-31-2009 1234 177721 6-1-2009 12-31-2009 5678 Ok so the big issue is the emp will chage profile peroidically thoughout a calander year. I am trying to get were in A1 is you enter the EMP # and then in B1 you enter a random date the correct profile code will come up in C1 based on the date range in the of table array the emp was profiled to the code =SUMPRODUCT(--(A1=L1:L9),--(B1=M1:M9),--(B1<=N1:N9),O1:O9) if not woeking me only, it sticks a 0 regardless of the emp and date entered. completely open to re-structering the array is needed. As matter of fact the table array is a work in progress |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date range table array formula
=SUMPRODUCT(--(A1=L1:L9),--(B1=M1:M9),--(B1<=N1:N9),O1:O9)
.. it sticks a 0 regardless of the emp and date entered. Your expression is ok. But probably the return col O contains text nums, which sumproduct cannot evaluate. You could try this more generic index/match equivalent (normal ENTER to confirm will do, like the sumproduct) which will work regardless of whether its nums, text or mixed data in the return col: =INDEX(O2:O9,MATCH(1,INDEX((A1=L2:L9)*(B1=M2:M9)* (B1<=N2:N9),),0)) Do high-five this response by clicking the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date range table array formula
question??? In the table you have emp #/ start / end date/ code what will happen if the end date it left blank. Will this mess up the function "Max" wrote: =SUMPRODUCT(--(A1=L1:L9),--(B1=M1:M9),--(B1<=N1:N9),O1:O9) .. it sticks a 0 regardless of the emp and date entered. Your expression is ok. But probably the return col O contains text nums, which sumproduct cannot evaluate. You could try this more generic index/match equivalent (normal ENTER to confirm will do, like the sumproduct) which will work regardless of whether its nums, text or mixed data in the return col: =INDEX(O2:O9,MATCH(1,INDEX((A1=L2:L9)*(B1=M2:M9)* (B1<=N2:N9),),0)) Do high-five this response by clicking the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date range table array formula
what will happen if the end date left blank
Blanks are evaluated as zeros in comparisons, these would mess up correct comparisons -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- " wrote: question??? In the table you have emp #/ start / end date/ code what will happen if the end date it left blank. Will this mess up the function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
different formula for ROW, array / range? | Excel Discussion (Misc queries) | |||
array formula with a dynamic range. | Excel Worksheet Functions | |||
how to use * wildcard in a sum(if((cond),range)) array formula | Excel Worksheet Functions | |||
#VALUE! On An Array Formula Referencing a Range Outside The Workbo | Excel Discussion (Misc queries) | |||
Array Formula, noncontigous range | Excel Worksheet Functions |