Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula question for arrays
I have a group of cells with dates in them set up as so
4/8/2004 4/26/2004 Cycle 1 4/27/2004 5/4/2004 Cycle 2 5/5/2004 5/11/2004 Cycle 3 etc etc etc FIND PLOGS CLOSED DURING A SPECIFIC TIME PERIOD Then I have a query to sum the number of "All Products" with the criteria of "Closed Plogs" and "Show Stopper" and the date value in column "H" if ="a date" and then <= "a date" as shown below {=SUM(IF(Closed_Plogs!$A$2:$A$3000="All Products",1,0)*IF(Closed_Plogs!$L$2:$L$3000="Show Stopper",1,0)*IF(Closed_Plogs!$H$2:$H$3000=VALUE( "4/08/2004"),1,0)*IF(Close d_Plogs!$H$2:$H$3000<=VALUE("4/26/2004"),1,0))} FIND PLOGS OPENED DURING A SPECIFIC TIME PERIOD I have another query to the sum the number of "All Products" with the criteria of "show stopper" and date value criteria as shown below {=SUM(IF(All_Plogs!$A$2:$A$3000="All Products",1,0)*IF(All_Plogs!$L$2:$L$3000="Show Stopper",1,0)*IF(All_Plogs!$B$2:$B$3000=VALUE("4/08/2004"),1,0)*IF(All_Plog s!$B$2:$B$3000<=VALUE("4/26/2004"),1,0))} I say all of this to I now need to take the same data and now I need to know which cycle the plog was opened in and which cycle it was closed in, not the specific Plog, just a count of when they were opened and closed. The above formulas are set up in cycles in the spreadsheets and tell me how many were closed during a cycle and how many were opened during a cycle, however, I am having a real difficult time coming up with the correct formula I would have a column for Open C1 Closed C1 column for Open C1 Closed C2 column for Open C1 Closed C3 column for Open C2 Closed C2 column for Open C2 Closed C3 column for Open C3 Closed C3 Well I have rambled on and on trying to explain, I hope this makes sense and I appreciate any and all help. You guys are the greatest. -- Thanks, Cynthia G. "Information is just signs and numbers, while knowledge involves their meaning. What we want is knowledge, but what we get is information." --Heinz R. Pagels |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula question for arrays
Assume it says Cycle1 in C for when opened and Cycle2 in D for when closed
as an example. *IF(All_Plogs!$C$2:$C$3000="Cycle1",1,0)*IF(All_Pl ogs!$SD$2:$D$3000="Cycle2" ,1,0)* Add the above condition to the appropriate other conditions. If not that, then you would need a better explanation of how cycle open and cycle close are indicated for a single plot. -- Regards, Tom Ogilvy "Cynthia Gregory" wrote in message ... I have a group of cells with dates in them set up as so 4/8/2004 4/26/2004 Cycle 1 4/27/2004 5/4/2004 Cycle 2 5/5/2004 5/11/2004 Cycle 3 etc etc etc FIND PLOGS CLOSED DURING A SPECIFIC TIME PERIOD Then I have a query to sum the number of "All Products" with the criteria of "Closed Plogs" and "Show Stopper" and the date value in column "H" if ="a date" and then <= "a date" as shown below {=SUM(IF(Closed_Plogs!$A$2:$A$3000="All Products",1,0)*IF(Closed_Plogs!$L$2:$L$3000="Show Stopper",1,0)*IF(Closed_Plogs!$H$2:$H$3000=VALUE( "4/08/2004"),1,0)*IF(Close d_Plogs!$H$2:$H$3000<=VALUE("4/26/2004"),1,0))} FIND PLOGS OPENED DURING A SPECIFIC TIME PERIOD I have another query to the sum the number of "All Products" with the criteria of "show stopper" and date value criteria as shown below {=SUM(IF(All_Plogs!$A$2:$A$3000="All Products",1,0)*IF(All_Plogs!$L$2:$L$3000="Show Stopper",1,0)*IF(All_Plogs!$B$2:$B$3000=VALUE("4/08/2004"),1,0)*IF(All_Plog s!$B$2:$B$3000<=VALUE("4/26/2004"),1,0))} I say all of this to I now need to take the same data and now I need to know which cycle the plog was opened in and which cycle it was closed in, not the specific Plog, just a count of when they were opened and closed. The above formulas are set up in cycles in the spreadsheets and tell me how many were closed during a cycle and how many were opened during a cycle, however, I am having a real difficult time coming up with the correct formula I would have a column for Open C1 Closed C1 column for Open C1 Closed C2 column for Open C1 Closed C3 column for Open C2 Closed C2 column for Open C2 Closed C3 column for Open C3 Closed C3 Well I have rambled on and on trying to explain, I hope this makes sense and I appreciate any and all help. You guys are the greatest. -- Thanks, Cynthia G. "Information is just signs and numbers, while knowledge involves their meaning. What we want is knowledge, but what we get is information." --Heinz R. Pagels |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula question for arrays
Thanks Tom. I will try that!
-- Thanks, Cynthia G. "Information is just signs and numbers, while knowledge involves their meaning. What we want is knowledge, but what we get is information." --Heinz R. Pagels "Tom Ogilvy" wrote in message ... Assume it says Cycle1 in C for when opened and Cycle2 in D for when closed as an example. *IF(All_Plogs!$C$2:$C$3000="Cycle1",1,0)*IF(All_Pl ogs!$SD$2:$D$3000="Cycle2" ,1,0)* Add the above condition to the appropriate other conditions. If not that, then you would need a better explanation of how cycle open and cycle close are indicated for a single plot. -- Regards, Tom Ogilvy "Cynthia Gregory" wrote in message ... I have a group of cells with dates in them set up as so 4/8/2004 4/26/2004 Cycle 1 4/27/2004 5/4/2004 Cycle 2 5/5/2004 5/11/2004 Cycle 3 etc etc etc FIND PLOGS CLOSED DURING A SPECIFIC TIME PERIOD Then I have a query to sum the number of "All Products" with the criteria of "Closed Plogs" and "Show Stopper" and the date value in column "H" if ="a date" and then <= "a date" as shown below {=SUM(IF(Closed_Plogs!$A$2:$A$3000="All Products",1,0)*IF(Closed_Plogs!$L$2:$L$3000="Show Stopper",1,0)*IF(Closed_Plogs!$H$2:$H$3000=VALUE( "4/08/2004"),1,0)*IF(Close d_Plogs!$H$2:$H$3000<=VALUE("4/26/2004"),1,0))} FIND PLOGS OPENED DURING A SPECIFIC TIME PERIOD I have another query to the sum the number of "All Products" with the criteria of "show stopper" and date value criteria as shown below {=SUM(IF(All_Plogs!$A$2:$A$3000="All Products",1,0)*IF(All_Plogs!$L$2:$L$3000="Show Stopper",1,0)*IF(All_Plogs!$B$2:$B$3000=VALUE("4/08/2004"),1,0)*IF(All_Plog s!$B$2:$B$3000<=VALUE("4/26/2004"),1,0))} I say all of this to I now need to take the same data and now I need to know which cycle the plog was opened in and which cycle it was closed in, not the specific Plog, just a count of when they were opened and closed. The above formulas are set up in cycles in the spreadsheets and tell me how many were closed during a cycle and how many were opened during a cycle, however, I am having a real difficult time coming up with the correct formula I would have a column for Open C1 Closed C1 column for Open C1 Closed C2 column for Open C1 Closed C3 column for Open C2 Closed C2 column for Open C2 Closed C3 column for Open C3 Closed C3 Well I have rambled on and on trying to explain, I hope this makes sense and I appreciate any and all help. You guys are the greatest. -- Thanks, Cynthia G. "Information is just signs and numbers, while knowledge involves their meaning. What we want is knowledge, but what we get is information." --Heinz R. Pagels |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can you use multiple bin arrays in a frequency formula? | Excel Worksheet Functions | |||
Write a formula using two arrays, across two worksheets | Excel Worksheet Functions | |||
Arrays/Functions/Different datatypes in a formula? | Excel Worksheet Functions | |||
Arrays Take too long. VERY HARD QUESTION. my head hurts : / | Excel Worksheet Functions | |||
Using Vlookup in formula arrays | Excel Worksheet Functions |